Обсуждение: Dates in inserts

Hello

I have a table:

CREATE TABLE public.dates (
date timestamp
)

From psql I run following inserts:

insert into dates values('13.01.03');
insert into dates values('01.13.03');

and I run select:

select * from dates order by date;

result:

        date
---------------------
 2003-01-13 00:00:00
 2003-01-13 00:00:00
(2 rows)

Why postgresql inserts the same dates ?


Thanks a lot,
Michal

On Tue, Apr 01, 2003 at 02:56:36PM +0200, Michal Kalanski wrote:
> insert into dates values('13.01.03');
> insert into dates values('01.13.03');

>         date
> ---------------------
>  2003-01-13 00:00:00
>  2003-01-13 00:00:00
> (2 rows)

Looks like the server is confused about the date format you want to use.
Decide on European or US and do a SET DATESTYLE as appropriate.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington

----- Original Message -----
>From: "Martijn van Oosterhout" <kleptog@svana.org>
>To: "Michal Kalanski" <kalanskim@zetokatowice.pl>
>Cc: <pgsql-general@postgresql.org>
>Sent: Tuesday, April 01, 2003 3:00 PM
>Subject: Re: [GENERAL] Dates in inserts

>Looks like the server is confused about the date format you want to use.
>Decide on European or US and do a SET DATESTYLE as appropriate.


I want to use dd.mm.yy date format.
I want to validate dates in inserts. How to do it ?

On Tue, 1 Apr 2003, Michal Kalanski wrote:

> Hello
>
> I have a table:
>
> CREATE TABLE public.dates (
> date timestamp
> )
>
> From psql I run following inserts:
>
> insert into dates values('13.01.03');
> insert into dates values('01.13.03');
>
> and I run select:
>
> select * from dates order by date;
>
> result:
>
>         date
> ---------------------
>  2003-01-13 00:00:00
>  2003-01-13 00:00:00
> (2 rows)
>
> Why postgresql inserts the same dates ?

Probably becuase there is no 13th month so 01.13.03 can only be interpreted as
the 13th of January. Whereas your settings are probably such that 13.01.03 is
interpreted correctly as the 13th of January.

Well, it's that or the 01.13.03 is correctly interpreted and the 13.01.03 can
only be interpreted as the 13th as there is no 13th month.

Find out which it is by doing:

SELECT '02.01.03'::date

and checking your DATE STYLE setting. I'm not sure without looking what the
variable is that controls the interpretation of input but DATE STYLE is
probably a good point to start looking from.

Interestingly, doing the above select on my 7.3 doesn't show the time portion,
presumably you're using something older.


>
> Thanks a lot,
> Michal

--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> Probably becuase there is no 13th month so 01.13.03 can only be
> interpreted as the 13th of January.

Right, and the same goes for 13.01.03: even if your datestyle is mmddyy,
the date parser will take this as ddmmyy, because otherwise it couldn't
be valid.  AFAIK there is no way to force the date parser to reject the
input instead.  Datestyle is used to drive the interpretation when the
input is ambiguous, but not when there is only one interpretation that
will work.

If you prefer to be stiff-necked then I'd recommend putting some
validation on the client side.

            regards, tom lane

On Tue, 1 Apr 2003, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > Probably becuase there is no 13th month so 01.13.03 can only be
> > interpreted as the 13th of January.
>
> Right, and the same goes for 13.01.03: even if your datestyle is mmddyy,
> the date parser will take this as ddmmyy, because otherwise it couldn't
> be valid.  AFAIK there is no way to force the date parser to reject the
> input instead.  Datestyle is used to drive the interpretation when the
> input is ambiguous, but not when there is only one interpretation that
> will work.
>
> If you prefer to be stiff-necked then I'd recommend putting some
> validation on the client side.

I think the better answer is to only insert dates in an unambiguous
format.

>I want to use dd.mm.yy date format.
>I want to validate dates in inserts. How to do it ?

I was going to suggest that to force a format, try using
to_timestamp( '01.13.03', 'DD.MM.YY' ) but it does not
check the month field for validity.  Instead it returns
Jan 1 2004 (!).  I guess month 13 is January of the next year.

However, when you use any of the character Mon formats
for Mon, it does give an error message for a bad month
if you give it a bad month.


elein=# select to_timestamp( '13.01.03', 'DD.MM.YY' );
      to_timestamp
------------------------
 2003-01-13 00:00:00-08
(1 row)

elein=# select to_timestamp( '01.13.03', 'DD.MM.YY' );
      to_timestamp
------------------------
 2004-01-01 00:00:00-08
(1 row)

elein=# select to_date( '01 13 03', 'DD Mon YY' );
ERROR:  to_timestamp(): bad value for MON/Mon/mon

elein=# select to_date( '01-dEc-2003', 'DD-Mon-YYYY' );
  to_date
------------
 2003-12-01
(1 row)

elein=# select to_date( '01-dE-2003', 'DD-Mon-YYYY' );
ERROR:  to_timestamp(): bad value for MON/Mon/mon

elein@varlena.com


On Tuesday 01 April 2003 09:26, scott.marlowe wrote:
> On Tue, 1 Apr 2003, Tom Lane wrote:
> > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > > Probably becuase there is no 13th month so 01.13.03 can only be
> > > interpreted as the 13th of January.
> >
> > Right, and the same goes for 13.01.03: even if your datestyle is mmddyy,
> > the date parser will take this as ddmmyy, because otherwise it couldn't
> > be valid.  AFAIK there is no way to force the date parser to reject the
> > input instead.  Datestyle is used to drive the interpretation when the
> > input is ambiguous, but not when there is only one interpretation that
> > will work.
> >
> > If you prefer to be stiff-necked then I'd recommend putting some
> > validation on the client side.
>
> I think the better answer is to only insert dates in an unambiguous
> format.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
----------------------------------------------------------------------------------------
elein@varlena.com     Database Consulting     www.varlena.com
              I have always depended on the [QA] of strangers.