Thread: 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>
> 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
compliantway 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
formatstring. However, in the end that is an Oracle-compatibility function, and there is only one measure of what it
shoulddo: 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','yyyymmddhh24mi'); 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. +
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: