Lists: | pgsql-generalpgsql-patches |
---|
From: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | date comparisons |
Date: | 2006-12-12 16:32:47 |
Message-ID: | Pine.GSO.4.53.0612121125220.28737@galapagos.bx.psu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
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 YYYY');
select id from main_table where
(to_timestamp('January 2006', 'Month YYYY'), 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
From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 16:46:16 |
Message-ID: | 457EDCD8.1050102@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
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 YYYY');
>
> select id from main_table where
> (to_timestamp('January 2006', 'Month YYYY'), 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 <erik(at)myemma(dot)com>
software development
emma(r)
From: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> |
---|---|
To: | Erik Jones <erik(at)myemma(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 17:03:20 |
Message-ID: | Pine.GSO.4.53.0612121157310.28737@galapagos.bx.psu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
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 YYYY');
> >
> > select id from main_table where
> > (to_timestamp('January 2006', 'Month YYYY'), 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 <erik(at)myemma(dot)com>
> 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', 'YYYY Month');
works, but
select id, date_entered from main_table where
date_entered >= to_timestamp('January 2006', 'Month YYYY');
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 YYYY');
to_timestamp
------------------------
0006-01-01 00:00:00-05
(1 row)
hbvar=# select to_timestamp('2006 January', 'YYYY Month');
to_timestamp
------------------------
2006-01-01 00:00:00-05
(1 row)
Belinda
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> |
Cc: | Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 17:22:16 |
Message-ID: | 457EE548.6030705@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
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', 'YYYY Month');
>
> works, but
>
> select id, date_entered from main_table where
> date_entered >= to_timestamp('January 2006', 'Month YYYY');
>
> 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 YYYY');
> to_timestamp
> ------------------------
> 0006-01-01 00:00:00-05
> (1 row)
>
> hbvar=# select to_timestamp('2006 January', 'YYYY Month');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)
SELECT to_timestamp('January 22 2006','Month DD YYYY');
to_timestamp
------------------------
0005-06-28 00:00:00+00
(1 row)
SELECT to_timestamp('Jan 22 2006','Mon DD YYYY');
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> |
Cc: | Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 17:25:22 |
Message-ID: | 5052.1165944322@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
"Belinda M. Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> 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 YYYY');
to_timestamp
------------------------
0006-01-01 00:00:00-05
(1 row)
regression=# select to_timestamp('January 2006', 'Month YYYY');
to_timestamp
------------------------
2006-01-01 00:00:00-05
(1 row)
You probably want
regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
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
From: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 17:39:00 |
Message-ID: | Pine.GSO.4.53.0612121235540.28737@galapagos.bx.psu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
On Tue, 12 Dec 2006, Tom Lane wrote:
> "Belinda M. Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> 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 YYYY');
> to_timestamp
> ------------------------
> 0006-01-01 00:00:00-05
> (1 row)
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)
>
> You probably want
>
> regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu>, Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 17:55:40 |
Message-ID: | 5716.1165946140@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
Richard Huxton <dev(at)archonet(dot)com> 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
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu>, Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2006-12-12 17:58:17 |
Message-ID: | 457EEDB9.8090903@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
Tom Lane wrote:
> "Belinda M. Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> 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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu>, Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2007-02-03 23:46:56 |
Message-ID: | 200702032346.l13Nkut23682@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
Would someone please confirm that our behavior in the three queries
below matches Oracle's behavior?
---------------------------------------------------------------------------
Tom Lane wrote:
> "Belinda M. Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> 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 YYYY');
> to_timestamp
> ------------------------
> 0006-01-01 00:00:00-05
> (1 row)
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)
>
> You probably want
>
> regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
> 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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From: | "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com> |
---|---|
To: | "Bruce Momjian" <bruce(at)momjian(dot)us> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu>, "Erik Jones" <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2007-02-04 00:01:40 |
Message-ID: | 81961ff50702031601q22d54852o917c982eec94d4c4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
On 2/3/07, Bruce Momjian <bruce(at)momjian(dot)us> 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 = 'YYYY-MM-DD HH.MI.SSXFF AM';
Session altered.
SQL> select to_timestamp('January 2006', 'Month YYYY') from dual;
TO_TIMESTAMP('JANUARY2006','MONTHYYYY')
---------------------------------------------------------------------------
2006-01-01 12.00.00.000000000 AM
SQL> select to_timestamp('January 2006', 'Month YYYY') from dual;
TO_TIMESTAMP('JANUARY2006','MONTHYYYY')
---------------------------------------------------------------------------
2006-01-01 12.00.00.000000000 AM
SQL> select to_timestamp('January 2006', 'FMMonth YYYY') from dual;
TO_TIMESTAMP('JANUARY2006','FMMONTHYYYY')
---------------------------------------------------------------------------
2006-01-01 12.00.00.000000000 AM
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Chad Wagner <chad(dot)wagner(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu>, Erik Jones <erik(at)myemma(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date comparisons |
Date: | 2007-02-04 00:03:44 |
Message-ID: | 200702040003.l1403iV26746@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
OK, so we have a bug. Thanks.
---------------------------------------------------------------------------
Chad Wagner wrote:
> On 2/3/07, Bruce Momjian <bruce(at)momjian(dot)us> 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 = 'YYYY-MM-DD HH.MI.SSXFF AM';
>
> Session altered.
>
> SQL> select to_timestamp('January 2006', 'Month YYYY') from dual;
>
> TO_TIMESTAMP('JANUARY2006','MONTHYYYY')
> ---------------------------------------------------------------------------
> 2006-01-01 12.00.00.000000000 AM
>
> SQL> select to_timestamp('January 2006', 'Month YYYY') from dual;
>
> TO_TIMESTAMP('JANUARY2006','MONTHYYYY')
> ---------------------------------------------------------------------------
> 2006-01-01 12.00.00.000000000 AM
>
> SQL> select to_timestamp('January 2006', 'FMMonth YYYY') from dual;
>
> TO_TIMESTAMP('JANUARY2006','FMMONTHYYYY')
> ---------------------------------------------------------------------------
> 2006-01-01 12.00.00.000000000 AM
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Belinda M(dot) Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu>, Erik Jones <erik(at)myemma(dot)com>, Chad Wagner <chad(dot)wagner(at)gmail(dot)com>, PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] date comparisons |
Date: | 2007-02-08 03:22:30 |
Message-ID: | 200702080322.l183MU723660@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-patches |
OK, I researched this and have developed the attached patch, which I
have applied to CVS HEAD.
The problem with our code is that when using to_timestamp() or to_date()
without "TM", we assume a fixed maximum length, even if the input
string is variable length, like month or day names. Oracle assumes "TM"
(trim) for such input fields, and this patch does the same.
I think it is too risky to backpatch to 8.2.X.
If anyone sees anymore cases of this in the code, please let me know.
---------------------------------------------------------------------------
Tom Lane wrote:
> "Belinda M. Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> 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 YYYY');
> to_timestamp
> ------------------------
> 0006-01-01 00:00:00-05
> (1 row)
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)
>
> You probably want
>
> regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
> 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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachment | Content-Type | Size |
---|---|---|
/rtmp/diff | text/x-diff | 2.3 KB |