Forum OpenACS Q&A: Postgres to

Posted by Vinod Kurup on 03/15/01 08:04 AM

Hi all, I've noticed that to_date sometimes doesn't work the way I think it should. Is this a bug? I tried on 7.1b4 and 7.0.3


# select to_date('2001-3-6', 'YYYY-MM-DD');
  to_date
------------
 2001-03-01

# select to_date('2001-3-11', 'YYYY-MM-DD');
  to_date
------------
 2001-03-01

Should I expect these to work as is, or do I need to assure that there are leading zeroes in my input.

Thanks!

2: Response to Postgres to_date bug? (response to 1)

Posted by Bob OConnor on 03/15/01 01:51 PM

I don't see this as a bug. The template shows MM-DD so that
means that the leading zeros are needed -- (er required). It works
fine with the leading zeros '2001-03-06'.

-Bob

3: Response to Postgres to_date bug? (response to 1)

Posted by Don Baccus on 03/15/01 01:54 PM

If you tell it "MM" and "DD" you need to give it two digits in each
case. Oracle gives you an explicit error message (I just tried it);
PG should as well but apparently doesn't bother.

You might consider filing the fact that it doesn't give an error
message as a bug at the PG website.

4: Response to Postgres to_date bug? (response to 1)

Posted by Vinod Kurup on 03/15/01 04:41 PM

Hmm... I just tried it in Oracle and I got:


SQL> select to_date('2001-3-6','YYYY-MM-DD') from dual;

TO_DATE('2
----------
2001-03-06

That's where my initial confusion came from, cuz it seemed to work in oracle, but was not working in postgres.

In comparing the postgre & oracle docs, postgres says DD=(01-31) while oracle says DD=(1-31), so I guess each implementation is working properly - I just need to RTFM 😊

I agree that pg should at least throw an error, but it looking more closely, I can't get pg's to_date to complain about anything.


openacs=# select to_date('foo', 'YYYY-MM-DD');
    to_date
---------------
 0001-01-01 BC
(1 row)

So, perhaps this should be a feature request, not a bug? In any case, thanks for the help!

5: Response to Postgres to_date bug? (response to 1)

Posted by Don Baccus on 03/15/01 08:26 PM

Hmmm...what version of Oracle are you running? I really did get an error.

Maybe I typed the example in wrong.

6: Response to Postgres to_date bug? (response to 1)

Posted by Vinod Kurup on 03/15/01 09:25 PM

I'm running 8.1.6.

BTW, I did a little searching on the postgres archives and I found out that using the 'FM' format option will simulate what I describe above.


openacs=# select to_date('2001 3 6','YYYY FMMM FMDD');
  to_date
------------
 2001-03-06

openacs=# select to_date('2001 03 06', 'YYYY FMMM FMDD');
  to_date
------------
 2001-03-06

Unfortunately, using dashes confuses it:


openacs=# select to_date('2001-03-06', 'YYYY-FMMM-FMDD');
  to_date
------------
 2001-02-22

7: Response to Postgres to_date bug? (response to 1)

Posted by Leif Jensen on 03/15/01 10:56 PM

It is actually not the dashes, but the 0 (zeros):

testdb=# select to_date('2001-3-6 10:00', 'YYYY-FMMM-FMDD HH24:MI');
to_date
------------
2001-03-06
(1 row)

testdb=# select to_date('2001-12-16 10:00', 'YYYY-FMMM-FMDD HH24:MI');
to_date
------------
2001-12-16
(1 row)

8: Re: Postgres to_date bug? (response to 1)

Posted by Nima Mazloumi on 06/07/04 07:33 PM

I have a problem with to_date as well:

I have a field in the database date of the type timestampz.
The field returns a string like this:

"2004-06-04 12:50:00+02"

Now if I call to_date(date, ('YYYY-MM-DD HH24:MI') this results in

"2004-06-04" and the hours and minutes are missing. Why is that? Is this a bug?

I am using PostgreSQL 7.3.4.

Greetings,
Nima

9: Re: Postgres to_date bug? (response to 1)

Posted by Nima Mazloumi on 06/07/04 07:39 PM

Problem solution found here:
https://openacs.org/forums/message-view?message_id=111938

PG required to_timestamp if time is important while Oracle uses to_date in both cases.

10: Re: Postgres to_date bug? (response to 9)

Posted by Jeff Davis on 06/07/04 07:58 PM

you can also just not convert it at all eg:

select '2004-06-04 18:57:00+01' - now();
         ?column?
--------------------------
 -3 days -00:00:25.633516
(1 row)

Forum OpenACS Q&A: Postgres to_date bug?