Re: [GENERAL] date comparisons
OK, so we have a bug. Thanks. --- Chad Wagner wrote: > On 2/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > > > > Would someone please confirm that our behavior in the three queries > > below matches Oracle's behavior? > > > Here is output from Oracle: > > Connected to: > Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production > With the Partitioning, OLAP and Data Mining options > > SQL> alter session set nls_timestamp_format = '-MM-DD HH.MI.SSXFF AM'; > > Session altered. > > SQL> select to_timestamp('January 2006', 'Month ') from dual; > > TO_TIMESTAMP('JANUARY2006','MONTH') > --- > 2006-01-01 12.00.00.0 AM > > SQL> select to_timestamp('January 2006', 'Month ') from dual; > > TO_TIMESTAMP('JANUARY2006','MONTH') > --- > 2006-01-01 12.00.00.0 AM > > SQL> select to_timestamp('January 2006', 'FMMonth ') from dual; > > TO_TIMESTAMP('JANUARY2006','FMMONTH') > --- > 2006-01-01 12.00.00.0 AM -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] date comparisons
On 2/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: Would someone please confirm that our behavior in the three queries below matches Oracle's behavior? Here is output from Oracle: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> alter session set nls_timestamp_format = '-MM-DD HH.MI.SSXFF AM'; Session altered. SQL> select to_timestamp('January 2006', 'Month ') from dual; TO_TIMESTAMP('JANUARY2006','MONTH') --- 2006-01-01 12.00.00.0 AM SQL> select to_timestamp('January 2006', 'Month ') from dual; TO_TIMESTAMP('JANUARY2006','MONTH') --- 2006-01-01 12.00.00.0 AM SQL> select to_timestamp('January 2006', 'FMMonth ') from dual; TO_TIMESTAMP('JANUARY2006','FMMONTH') --- 2006-01-01 12.00.00.0 AM
Re: [GENERAL] date comparisons
Would someone please confirm that our behavior in the three queries below matches Oracle's behavior? --- Tom Lane wrote: > "Belinda M. Giardine" <[EMAIL PROTECTED]> writes: > > Should it be this way? > > Well, to_timestamp() is apparently designed not to complain when the > input doesn't match the format, which is not my idea of good behavior > ... but your example is in fact wrong. 'Month' means a 9-character > field, so you are short a couple of spaces. > > regression=# select to_timestamp('January 2006', 'Month '); > to_timestamp > > 0006-01-01 00:00:00-05 > (1 row) > > regression=# select to_timestamp('January 2006', 'Month '); > to_timestamp > > 2006-01-01 00:00:00-05 > (1 row) > > You probably want > > regression=# select to_timestamp('January 2006', 'FMMonth '); > to_timestamp > > 2006-01-01 00:00:00-05 > (1 row) > > Or, as suggested upthread, forget to_timestamp and just use the native > timestamp or date input conversion, which on the whole is a lot more > robust (it *will* throw an error if it can't make sense of the input, > unlike to_timestamp). > > regards, tom lane > > ---(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 -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] date comparisons
Richard Huxton writes: > The padding is on *input* too? Is this an Oracle compatibility "feature"? I assume so. If Oracle does not work like that, then it'd be a bug ... but the whole purpose of that function is to be Oracle-compatible, so we're sort of stuck doing what Oracle does. regards, tom lane ---(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: [GENERAL] date comparisons
Tom Lane wrote: "Belinda M. Giardine" <[EMAIL PROTECTED]> writes: Should it be this way? Well, to_timestamp() is apparently designed not to complain when the input doesn't match the format, which is not my idea of good behavior ... but your example is in fact wrong. 'Month' means a 9-character field, so you are short a couple of spaces. The padding is on *input* too? Is this an Oracle compatibility "feature"? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] date comparisons
On Tue, 12 Dec 2006, Tom Lane wrote: > "Belinda M. Giardine" <[EMAIL PROTECTED]> writes: > > Should it be this way? > > Well, to_timestamp() is apparently designed not to complain when the > input doesn't match the format, which is not my idea of good behavior > ... but your example is in fact wrong. 'Month' means a 9-character > field, so you are short a couple of spaces. > > regression=# select to_timestamp('January 2006', 'Month '); > to_timestamp > > 0006-01-01 00:00:00-05 > (1 row) > > regression=# select to_timestamp('January 2006', 'Month '); > to_timestamp > > 2006-01-01 00:00:00-05 > (1 row) > > You probably want > > regression=# select to_timestamp('January 2006', 'FMMonth '); > to_timestamp > > 2006-01-01 00:00:00-05 > (1 row) Thanks. I wanted to understand the reason for my attempt not working no matter which method I used in the end. Help to prevent future errors. > > Or, as suggested upthread, forget to_timestamp and just use the native > timestamp or date input conversion, which on the whole is a lot more > robust (it *will* throw an error if it can't make sense of the input, > unlike to_timestamp). > > regards, tom lane Good to know. Belinda ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] date comparisons
"Belinda M. Giardine" <[EMAIL PROTECTED]> writes: > Should it be this way? Well, to_timestamp() is apparently designed not to complain when the input doesn't match the format, which is not my idea of good behavior ... but your example is in fact wrong. 'Month' means a 9-character field, so you are short a couple of spaces. regression=# select to_timestamp('January 2006', 'Month '); to_timestamp 0006-01-01 00:00:00-05 (1 row) regression=# select to_timestamp('January 2006', 'Month '); to_timestamp 2006-01-01 00:00:00-05 (1 row) You probably want regression=# select to_timestamp('January 2006', 'FMMonth '); to_timestamp 2006-01-01 00:00:00-05 (1 row) Or, as suggested upthread, forget to_timestamp and just use the native timestamp or date input conversion, which on the whole is a lot more robust (it *will* throw an error if it can't make sense of the input, unlike to_timestamp). regards, tom lane ---(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: [GENERAL] date comparisons
Belinda M. Giardine wrote: Thanks that works. But I am trying to understand why the others did not, especially my first attempt. Further testing shows that select id, date_entered from main_table where date_entered >= to_timestamp('2006 January', ' Month'); works, but select id, date_entered from main_table where date_entered >= to_timestamp('January 2006', 'Month '); does not. The order of the fields in the to_timestamp function changes the timestamp produced. Should it be this way? No. Good testing, you've found a bug. Seems to be a problem with the "Month" formatting if there's more fields after it. hbvar=# select to_timestamp('January 2006', 'Month '); to_timestamp 0006-01-01 00:00:00-05 (1 row) hbvar=# select to_timestamp('2006 January', ' Month'); to_timestamp 2006-01-01 00:00:00-05 (1 row) SELECT to_timestamp('January 22 2006','Month DD '); to_timestamp 0005-06-28 00:00:00+00 (1 row) SELECT to_timestamp('Jan 22 2006','Mon DD '); to_timestamp 2006-01-22 00:00:00+00 (1 row) If you report this bug using the form below, I'm sure one of the developers will have a patch out shortly. http://www.postgresql.org/support/submitbug Good catch! -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] date comparisons
On Tue, 12 Dec 2006, Erik Jones wrote: > Belinda M. Giardine wrote: > > This should be simple but I am missing something. I am trying to extract > > all records entered after a given date. The table has a field > > date_entered which is a timestamp. In this particular case I am not > > worried about time. > > > > I have tried: > > select id from main_table where > > date_entered > to_timestamp('January 2006', 'Month '); > > > > select id from main_table where > > (to_timestamp('January 2006', 'Month '), now()) overlaps (date_entered, > > date_entered); > > > > Both of these return all the rows in the table. Half of the rows are > > dated 2000-06-22 12:00:00. > > > > PostgreSQL version 8.1.4 > > > I think people often make date comparisons too difficult in postgres. > > select id > from main_table > where date_entered >= '2006-01-01'; > > There are built in conversions for formatted date strings. > > -- > erik jones <[EMAIL PROTECTED]> > software development > emma(r) > Thanks that works. But I am trying to understand why the others did not, especially my first attempt. Further testing shows that select id, date_entered from main_table where date_entered >= to_timestamp('2006 January', ' Month'); works, but select id, date_entered from main_table where date_entered >= to_timestamp('January 2006', 'Month '); does not. The order of the fields in the to_timestamp function changes the timestamp produced. Should it be this way? hbvar=# select to_timestamp('January 2006', 'Month '); to_timestamp 0006-01-01 00:00:00-05 (1 row) hbvar=# select to_timestamp('2006 January', ' Month'); to_timestamp 2006-01-01 00:00:00-05 (1 row) Belinda ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] date comparisons
Belinda M. Giardine wrote: This should be simple but I am missing something. I am trying to extract all records entered after a given date. The table has a field date_entered which is a timestamp. In this particular case I am not worried about time. I have tried: select id from main_table where date_entered > to_timestamp('January 2006', 'Month '); select id from main_table where (to_timestamp('January 2006', 'Month '), now()) overlaps (date_entered, date_entered); Both of these return all the rows in the table. Half of the rows are dated 2000-06-22 12:00:00. PostgreSQL version 8.1.4 I think people often make date comparisons too difficult in postgres. select id from main_table where date_entered >= '2006-01-01'; There are built in conversions for formatted date strings. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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
[GENERAL] date comparisons
This should be simple but I am missing something. I am trying to extract all records entered after a given date. The table has a field date_entered which is a timestamp. In this particular case I am not worried about time. I have tried: select id from main_table where date_entered > to_timestamp('January 2006', 'Month '); select id from main_table where (to_timestamp('January 2006', 'Month '), now()) overlaps (date_entered, date_entered); Both of these return all the rows in the table. Half of the rows are dated 2000-06-22 12:00:00. PostgreSQL version 8.1.4 What am I missing? Belinda ---(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