Strange results from to_timestamp
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
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>
Show quoted text
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
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?
Import Notes
Resolved by subject fallback
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_timestampMario 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+01I 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+01I 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. +
Attachments:
/rtmp/difftext/plainDownload
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.109
diff -c -c -r1.109 formatting.c
*** src/backend/utils/adt/formatting.c 19 Apr 2006 14:48:06 -0000 1.109
--- src/backend/utils/adt/formatting.c 19 Apr 2006 16:07:35 -0000
***************
*** 917,922 ****
--- 917,923 ----
static char *get_th(char *num, int type);
static char *str_numth(char *dest, char *num, int type);
+ static int strspace_len(char *str);
static int strdigits_len(char *str);
static char *str_toupper(char *buff);
static char *str_tolower(char *buff);
***************
*** 1687,1697 ****
}
static int
strdigits_len(char *str)
{
char *p = str;
! int len = 0;
while (*p && isdigit((unsigned char) *p) && len <= DCH_MAX_ITEM_SIZ)
{
len++;
--- 1688,1714 ----
}
static int
+ strspace_len(char *str)
+ {
+ int len = 0;
+
+ while (*str && isspace((unsigned char) *str))
+ {
+ str++;
+ len++;
+ }
+ return len;
+ }
+
+ static int
strdigits_len(char *str)
{
char *p = str;
! int len;
+ len = strspace_len(str);
+ p += len;
+
while (*p && isdigit((unsigned char) *p) && len <= DCH_MAX_ITEM_SIZ)
{
len++;
***************
*** 1826,1832 ****
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1843,1849 ----
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 1848,1854 ****
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1865,1871 ----
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 1870,1876 ****
else
{
sscanf(inout, "%02d", &tmfc->mi);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1887,1893 ----
else
{
sscanf(inout, "%02d", &tmfc->mi);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 1892,1898 ****
else
{
sscanf(inout, "%02d", &tmfc->ss);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1909,1915 ----
else
{
sscanf(inout, "%02d", &tmfc->ss);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 1998,2004 ****
else
{
sscanf(inout, "%05d", &tmfc->ssss);
! return 5 + SKIP_THth(suf);
}
}
break;
--- 2015,2021 ----
else
{
sscanf(inout, "%05d", &tmfc->ssss);
! return strspace_len(inout) + 5 + SKIP_THth(suf);
}
}
break;
***************
*** 2249,2255 ****
else
{
sscanf(inout, "%02d", &tmfc->mm);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2266,2272 ----
else
{
sscanf(inout, "%02d", &tmfc->mm);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 2323,2329 ****
else
{
sscanf(inout, "%03d", &tmfc->ddd);
! return 3 + SKIP_THth(suf);
}
}
break;
--- 2340,2346 ----
else
{
sscanf(inout, "%03d", &tmfc->ddd);
! return strspace_len(inout) + 3 + SKIP_THth(suf);
}
}
break;
***************
*** 2345,2351 ****
else
{
sscanf(inout, "%02d", &tmfc->dd);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2362,2368 ----
else
{
sscanf(inout, "%02d", &tmfc->dd);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 2360,2366 ****
else
{
sscanf(inout, "%1d", &tmfc->d);
! return 1 + SKIP_THth(suf);
}
break;
case DCH_WW:
--- 2377,2383 ----
else
{
sscanf(inout, "%1d", &tmfc->d);
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_WW:
***************
*** 2382,2388 ****
else
{
sscanf(inout, "%02d", &tmfc->ww);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2399,2405 ----
else
{
sscanf(inout, "%02d", &tmfc->ww);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 2405,2411 ****
else
{
sscanf(inout, "%02d", &tmfc->iw);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2422,2428 ----
else
{
sscanf(inout, "%02d", &tmfc->iw);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 2422,2428 ****
else
{
sscanf(inout, "%1d", &tmfc->q);
! return 1 + SKIP_THth(suf);
}
break;
case DCH_CC:
--- 2439,2445 ----
else
{
sscanf(inout, "%1d", &tmfc->q);
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_CC:
***************
*** 2447,2453 ****
else
{
sscanf(inout, "%02d", &tmfc->cc);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2464,2470 ----
else
{
sscanf(inout, "%02d", &tmfc->cc);
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
}
break;
***************
*** 2507,2513 ****
{
sscanf(inout, "%04d", &tmfc->year);
tmfc->yysz = 4;
! return 4 + SKIP_THth(suf);
}
}
break;
--- 2524,2530 ----
{
sscanf(inout, "%04d", &tmfc->year);
tmfc->yysz = 4;
! return strspace_len(inout) + 4 + SKIP_THth(suf);
}
}
break;
***************
*** 2540,2546 ****
else
tmfc->year += 2000;
tmfc->yysz = 3;
! return 3 + SKIP_THth(suf);
}
break;
case DCH_YY:
--- 2557,2563 ----
else
tmfc->year += 2000;
tmfc->yysz = 3;
! return strspace_len(inout) + 3 + SKIP_THth(suf);
}
break;
case DCH_YY:
***************
*** 2572,2578 ****
else
tmfc->year += 1900;
tmfc->yysz = 2;
! return 2 + SKIP_THth(suf);
}
break;
case DCH_Y:
--- 2589,2595 ----
else
tmfc->year += 1900;
tmfc->yysz = 2;
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
break;
case DCH_Y:
***************
*** 2600,2606 ****
*/
tmfc->year += 2000;
tmfc->yysz = 1;
! return 1 + SKIP_THth(suf);
}
break;
case DCH_RM:
--- 2617,2623 ----
*/
tmfc->year += 2000;
tmfc->yysz = 1;
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_RM:
***************
*** 2652,2658 ****
else
{
sscanf(inout, "%1d", &tmfc->w);
! return 1 + SKIP_THth(suf);
}
break;
case DCH_J:
--- 2669,2675 ----
else
{
sscanf(inout, "%1d", &tmfc->w);
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_J: