Re: [HACKERS] Strange results from to_timestamp
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','mmdd hh24mi');
to_timestamp
0300-01-01 00:00:00-05
(1 row)
test= select to_timestamp(' 030004','mmdd hh24mi');
to_timestamp
0300-04-01 00:00:00-05
(1 row)
test= select to_timestamp(' 040004','mmdd 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(' 0300','mmdd hh24mi');
to_timestamp
---
0001-01-01 03:00:00+01 BC
(1 row)
Questionable, but probably valid.
mydb=# select to_timestamp(' 0300','mmdd 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','mmdd hh24mi');
to_timestamp
0382-04-23 03:00:00+01
(1 row)
Same as above.
mydb=# select to_timestamp(' 040004','mmdd 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
EnterpriseDBhttp://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
Re: [HACKERS] Strange results from to_timestamp
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','mmdd hh24mi');
to_timestamp
0300-01-01 00:00:00-05
(1 row)
test= select to_timestamp(' 030004','mmdd hh24mi');
to_timestamp
0300-04-01 00:00:00-05
(1 row)
test= select to_timestamp(' 040004','mmdd 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(' 0300','mmdd hh24mi');
to_timestamp
---
0001-01-01 03:00:00+01 BC
(1 row)
Questionable, but probably valid.
mydb=# select to_timestamp(' 0300','mmdd 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','mmdd hh24mi');
to_timestamp
0382-04-23 03:00:00+01
(1 row)
Same as above.
mydb=# select to_timestamp(' 040004','mmdd 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
EnterpriseDBhttp://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
EnterpriseDBhttp://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 - 1.109
--- src/backend/utils/adt/formatting.c 19 Apr 2006 16:07:35 -
***
*** 917,922
--- 917,923
static char *get_th(char *num, int type);
static char *str_numth(char *dest, char *num, int type);
+ static intstrspace_len(char *str);
static intstrdigits_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;
***
Re: [HACKERS] Strange results from to_timestamp
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Strange results from to_timestamp
On 4/7/06, Tom Lane [EMAIL PROTECTED] wrote:
Mario Weilguni [EMAIL PROTECTED] 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(' 0300','mmdd hh24mi') from dual;
select to_timestamp(' 0300','mmdd hh24mi') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL select to_timestamp(' 0300','mmdd hh24mi') from dual;
select to_timestamp(' 0300','mmdd hh24mi') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
Cheers,
Adrian Maier
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Strange results from to_timestamp
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
Re: [HACKERS] Strange results from to_timestamp
Am Donnerstag, 6. April 2006 14:57 schrieb Mario Weilguni:
mydb=# select to_timestamp(' 0300','mmdd hh24mi');
to_timestamp
---
0001-01-01 03:00:00+01 BC
(1 row)
Questionable, but probably valid.
mydb=# select to_timestamp(' 0300','mmdd 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
Re: [HACKERS] Strange results from to_timestamp
Mario Weilguni [EMAIL PROTECTED] 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
