Skip site navigation (1) Skip section navigation (2)

Date formats in 7.0b2

From: "Hernan J(dot) Gonzalez" <leonbloy(at)yahoo(dot)com>
To: pgsql-admin(at)postgreSQL(dot)org
Subject: Date formats in 7.0b2
Date: 2000-04-05 17:07:26
Message-ID: 20000405170726.1616.qmail@web204.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
I'm puzzled with the date formats in
the 7.0b2 version. Perhaps I'm missing something
trivial, (SQL standards?) but I have been using postgres
from years, and never had this kind of problem.

I use the -e switch, so that
> dates passed to and from the
> frontend processes will be
> assumed to be in "European" format (DD-MM-YYYY)
(from the postgres man page)

Now, it seems that 7.0b2  accepts dates in this
format  AS INPUT but the dates  in the OUTPUT
are always in the YYYY-MM-DD  format ...

Needless to say, this has devasting effects on
my scripts!

Further, postgres seems to accept BOTH
formats as input, and even with 2 digits
in the year... and with some strange results.
I attach some queries below.

Regards

Hernan Gonzalez
Buenos Aires, Argentina
hgonzal(at)sinectis(dot)com(dot)ar

#######################################################################

 testdb=# select fecha from eventos where fecha >= '31-01-2001';
    fecha
 ------------
  2001-02-01
  2001-03-01
  2002-01-01
  2002-02-01
 (4 rows)

It accepts european DD-MM-YYYY date (ok) but outputs YYYY-MM-DD  (?)
(Doesn't seem to be a psql issue)


 testdb=# select fecha from eventos where fecha >= '01-31-2001';
 ERROR:  Bad date external representation '01-31-2001'

Refuses MM-DD-YYYY date (ok, I'm usign the -e switch ) ]


 testdb=# select fecha from eventos where fecha >= '2001-01-31';
    fecha
 ------------
  2001-02-01                                                                   

  2001-03-01
  2002-01-01
  2002-02-01
 (4 rows)

Accepts YYYY-MM-DD also ...


 testdb=# select fecha from eventos where fecha >= '01-01-01';
    fecha
 ------------
  2001-01-01
  2001-02-01
  2001-03-01
  2002-01-01
  2002-02-01
 (5 rows)

Accepts DD-MM-YY  also  ....


 testdb=# select fecha from eventos where fecha >= '1-1-1';
    fecha
 ------------
  2000-02-01
  2000-04-01
  2000-05-01
  2000-06-01
  2000-07-01
  2000-08-01
  2000-09-01
  2000-10-01
  2000-11-01
  2000-11-17       
  2000-12-20
  2001-01-01
  2001-02-01
  2001-03-01
  2002-01-01
  2002-02-01
 (17 rows)

?? What does this mean ????


Now, let's see what if XX-XX-XX is understood as DD-MM-YY or YY-MM-DD ...

 testdb=# select fecha from eventos where fecha >= '03-02-01';
    fecha
 ------------
  2001-03-01
  2002-01-01
  2002-02-01
 (3 rows)


testdb=# select fecha from eventos where fecha >= '01-02-03';
 fecha
-------
(0 rows)

.... so, XX-XX-XX is understood as DD-MM-YY
                                                                             



__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com

Responses

pgsql-admin by date

Next:From: Tressens LionelDate: 2000-04-06 10:02:49
Subject: changing the owner of a db
Previous:From: Charles MartinDate: 2000-04-05 08:30:23
Subject: Re: backends die on pg_dump, and spurious files?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group