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 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@myemma.com>
software development
emma(r)

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@myemma.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

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

"Belinda M. Giardine" <giardine@bio.cse.psu.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

On Tue, 12 Dec 2006, Tom Lane wrote:

"Belinda M. Giardine" <giardine@bio.cse.psu.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

Richard Huxton <dev@archonet.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

Tom Lane wrote:

"Belinda M. Giardine" <giardine@bio.cse.psu.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

Would someone please confirm that our behavior in the three queries
below matches Oracle's behavior?

---------------------------------------------------------------------------

Tom Lane wrote:

"Belinda M. Giardine" <giardine@bio.cse.psu.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@postgresql.org so that your
message can get through to the mailing list cleanly

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On 2/3/07, Bruce Momjian <bruce@momjian.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

OK, so we have a bug. Thanks.

---------------------------------------------------------------------------

Chad Wagner wrote:

On 2/3/07, Bruce Momjian <bruce@momjian.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@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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@bio.cse.psu.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@postgresql.org so that your
message can get through to the mailing list cleanly

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +