Strange results from to_timestamp

Am Donnerstag, 6. April 2006 14:57 schrieb Mario Weilguni:

mydb=# select to_timestamp('00000000 0300','yyyymmdd hh24mi');
to_timestamp
---------------------------
0001-01-01 03:00:00+01 BC
(1 row)

Questionable, but probably valid.

mydb=# select to_timestamp(' 0300','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-12-25 03:00:00+01
(1 row)

This puzzles me. Where is the 25th of december coming from?

Sorry, forgot to mention, this is from PostgreSQL 8.1.3

ISTM, and mismatch between the date/time string and the format string will
lead to
strange results.
The source code of to_timestamp() is in src/backend/utils/adt/formatting.c:

Datum
to_timestamp(PG_FUNCTION_ARGS)

Regards,
William ZHANG

"Mario Weilguni" <mweilguni@sime.com>

Mario Weilguni <mweilguni@sime.com> writes:

I think all except the first one should raise a warning, isn't it?

to_timestamp (and friends) all seem to me to act pretty bizarre when
faced with input that doesn't match the given format string. However,
in the end that is an Oracle-compatibility function, and there is only
one measure of what it should do: what does Oracle do in the same case.
Can anyone try these examples on a recent Oracle version?

regards, tom lane

On 4/7/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Mario Weilguni <mweilguni@sime.com> writes:

I think all except the first one should raise a warning, isn't it?

to_timestamp (and friends) all seem to me to act pretty bizarre when
faced with input that doesn't match the given format string. However,
in the end that is an Oracle-compatibility function, and there is only
one measure of what it should do: what does Oracle do in the same case.
Can anyone try these examples on a recent Oracle version?

In Oracle10g Express those dates are rejected as invalid :

SQL> select to_timestamp('00000000 0300','yyyymmdd hh24mi') from dual;
select to_timestamp('00000000 0300','yyyymmdd hh24mi') from dual
*
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_timestamp(' 0300','yyyymmdd hh24mi') from dual;
select to_timestamp(' 0300','yyyymmdd hh24mi') from dual
*
ERROR at line 1:
ORA-01843: not a valid month

Cheers,
Adrian Maier

to_timestamp is only for Oracle compatibility? I always thought it's some sort of sql standard. What's the sql compliant way to do this?

Regards,
mario weilguni

-----Ursprüngliche Nachricht-----
Von: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] Im Auftrag von Tom Lane
Gesendet: Freitag, 07. April 2006 06:09
An: Mario Weilguni
Cc: PostgreSQL-development
Betreff: Re: [HACKERS] Strange results from to_timestamp

Mario Weilguni <mweilguni@sime.com> writes:

I think all except the first one should raise a warning, isn't it?

to_timestamp (and friends) all seem to me to act pretty bizarre when faced with input that doesn't match the given format string. However, in the end that is an Oracle-compatibility function, and there is only one measure of what it should do: what does Oracle do in the same case.
Can anyone try these examples on a recent Oracle version?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Mario Weilguni wrote:

to_timestamp is only for Oracle compatibility? I always thought it's
some sort of sql standard. What's the sql compliant way to do this?

There isn't a standard method, which is why we added Oracle functions.

---------------------------------------------------------------------------

Regards,
mario weilguni

-----Urspr?ngliche Nachricht----- Von:
pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] Im Auftrag von Tom Lane
Gesendet: Freitag, 07. April 2006 06:09 An: Mario Weilguni Cc:
PostgreSQL-development Betreff: Re: [HACKERS] Strange results from
to_timestamp

Mario Weilguni <mweilguni@sime.com> writes:

I think all except the first one should raise a warning, isn't it?

to_timestamp (and friends) all seem to me to act pretty bizarre when
faced with input that doesn't match the given format string. However,
in the end that is an Oracle-compatibility function, and there is only
one measure of what it should do: what does Oracle do in the same case.
Can anyone try these examples on a recent Oracle version?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Interesting bug report. The problem is that sscanf(buf, "%d", &val)
eats leading white space, but our functions were not handling that.

I have applied the attached patch that fixes this:

test=> select to_timestamp(' 0300','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-01-01 00:00:00-05
(1 row)

test=> select to_timestamp(' 030004','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-04-01 00:00:00-05
(1 row)

test=> select to_timestamp(' 040004','yyyymmdd hh24mi');
to_timestamp
------------------------
0400-04-01 00:00:00-05
(1 row)

It doesn't throw an error for too mamy format strings, but it does avoid
the incorrect values. The fix will appear in 8.2.

---------------------------------------------------------------------------

Mario Weilguni wrote:

mydb=# select to_timestamp('00000000 0300','yyyymmdd hh24mi');
to_timestamp
---------------------------
0001-01-01 03:00:00+01 BC
(1 row)

Questionable, but probably valid.

mydb=# select to_timestamp(' 0300','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-12-25 03:00:00+01
(1 row)

This puzzles me. Where is the 25th of december coming from?

mydb=# select to_timestamp(' 030004','yyyymmdd hh24mi');
to_timestamp
------------------------
0382-04-23 03:00:00+01
(1 row)

Same as above.

mydb=# select to_timestamp(' 040004','yyyymmdd hh24mi');
to_timestamp
------------------------
0509-10-10 04:00:00+01

I think all except the first one should raise a warning, isn't it? Where can I
find the source code of this function?

Best regards,
Mario Weilguni

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Attachment now attached. :-)

---------------------------------------------------------------------------

Bruce Momjian wrote:

Interesting bug report. The problem is that sscanf(buf, "%d", &val)
eats leading white space, but our functions were not handling that.

I have applied the attached patch that fixes this:

test=> select to_timestamp(' 0300','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-01-01 00:00:00-05
(1 row)

test=> select to_timestamp(' 030004','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-04-01 00:00:00-05
(1 row)

test=> select to_timestamp(' 040004','yyyymmdd hh24mi');
to_timestamp
------------------------
0400-04-01 00:00:00-05
(1 row)

It doesn't throw an error for too mamy format strings, but it does avoid
the incorrect values. The fix will appear in 8.2.

---------------------------------------------------------------------------

Mario Weilguni wrote:

mydb=# select to_timestamp('00000000 0300','yyyymmdd hh24mi');
to_timestamp
---------------------------
0001-01-01 03:00:00+01 BC
(1 row)

Questionable, but probably valid.

mydb=# select to_timestamp(' 0300','yyyymmdd hh24mi');
to_timestamp
------------------------
0300-12-25 03:00:00+01
(1 row)

This puzzles me. Where is the 25th of december coming from?

mydb=# select to_timestamp(' 030004','yyyymmdd hh24mi');
to_timestamp
------------------------
0382-04-23 03:00:00+01
(1 row)

Same as above.

mydb=# select to_timestamp(' 040004','yyyymmdd hh24mi');
to_timestamp
------------------------
0509-10-10 04:00:00+01

I think all except the first one should raise a warning, isn't it? Where can I
find the source code of this function?

Best regards,
Mario Weilguni

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +