[BUGS] is this my date problem

#1

I am not sure but i thought I read something of this
problem and maybe its not a problem but my
understanding.

There are only 30 days in september however I can not
seem to make a query that asks for october 1.

proofduedate is time stamp with zone
maybe this is the problem but I am a little lost.

SELECT jobnumseq, proofduedate FROM jobs WHERE
proofduedate BETWEEN to_timestamp('29 September 2003',
'DD Month YYYY') AND to_timestamp('31 September 2003
23:59', 'DD Month YYYY HH24:MI');
jobnumseq | proofduedate
-----------+------------------------
10080 | 2003-09-30 17:00:00-04
10081 | 2003-09-30 22:00:00-04
10082 | 2003-10-01 16:00:00-04
10074 | 2003-09-29 16:00:00-04
10077 | 2003-09-29 16:00:00-04
10078 | 2003-09-29 18:00:00-04
10079 | 2003-09-30 15:00:00-04
10083 | 2003-10-01 13:00:00-04
10084 | 2003-10-01 13:00:00-04
10085 | 2003-10-01 13:00:00-04
10086 | 2003-10-01 16:00:00-04
10087 | 2003-10-01 16:00:00-04
10088 | 2003-10-01 16:00:00-04
(13 rows)

Ted

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

On Wed, Oct 01, 2003 at 10:26:57 -0700,
Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> wrote:

Quote:

I am not sure but i thought I read something of this
problem and maybe its not a problem but my
understanding.

There are only 30 days in september however I can not
seem to make a query that asks for october 1.

You shouldn't be refering to September 31 as that isn't a valid date.
From your output it looks like it may have been treated as the same
as October 1, but I wouldn't count on that working in the future.

Quote:

proofduedate is time stamp with zone
maybe this is the problem but I am a little lost.

SELECT jobnumseq, proofduedate FROM jobs WHERE
proofduedate BETWEEN to_timestamp('29 September 2003',
'DD Month YYYY') AND to_timestamp('31 September 2003
23:59', 'DD Month YYYY HH24:MI');
jobnumseq | proofduedate
-----------+------------------------
10080 | 2003-09-30 17:00:00-04
10081 | 2003-09-30 22:00:00-04
10082 | 2003-10-01 16:00:00-04
10074 | 2003-09-29 16:00:00-04
10077 | 2003-09-29 16:00:00-04
10078 | 2003-09-29 18:00:00-04
10079 | 2003-09-30 15:00:00-04
10083 | 2003-10-01 13:00:00-04
10084 | 2003-10-01 13:00:00-04
10085 | 2003-10-01 13:00:00-04
10086 | 2003-10-01 16:00:00-04
10087 | 2003-10-01 16:00:00-04
10088 | 2003-10-01 16:00:00-04
(13 rows)

Ted

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#3

I am sorry, I wasn't clear... when i refered to

01 October 2003

I got zero results. I only got results for the October
1 date when i refered to it as september 31....

I am on 7.3.4 on OS X

Ted

--- Bruno Wolff III <bruno (AT) wolff (DOT) to> wrote:

Quote:

On Wed, Oct 01, 2003 at 10:26:57 -0700,
Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> wrote:
I am not sure but i thought I read something of
this
problem and maybe its not a problem but my
understanding.

There are only 30 days in september however I can
not
seem to make a query that asks for october 1.

You shouldn't be refering to September 31 as that
isn't a valid date.
From your output it looks like it may have been
treated as the same
as October 1, but I wouldn't count on that working
in the future.

proofduedate is time stamp with zone
maybe this is the problem but I am a little lost.

SELECT jobnumseq, proofduedate FROM jobs WHERE
proofduedate BETWEEN to_timestamp('29 September
2003',
'DD Month YYYY') AND to_timestamp('31 September
2003
23:59', 'DD Month YYYY HH24:MI');
jobnumseq | proofduedate
-----------+------------------------
10080 | 2003-09-30 17:00:00-04
10081 | 2003-09-30 22:00:00-04
10082 | 2003-10-01 16:00:00-04
10074 | 2003-09-29 16:00:00-04
10077 | 2003-09-29 16:00:00-04
10078 | 2003-09-29 18:00:00-04
10079 | 2003-09-30 15:00:00-04
10083 | 2003-10-01 13:00:00-04
10084 | 2003-10-01 13:00:00-04
10085 | 2003-10-01 13:00:00-04
10086 | 2003-10-01 16:00:00-04
10087 | 2003-10-01 16:00:00-04
10088 | 2003-10-01 16:00:00-04
(13 rows)

Ted

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product
search
http://shopping.yahoo.com

---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your free space
map settings

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: 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

#4

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:

Quote:

I am sorry, I wasn't clear... when i refered to
01 October 2003
I got zero results. I only got results for the October
1 date when i refered to it as september 31....

That's a bit hard to believe. Could you show us the results of the
individual to_timestamp operations ("select to_timestamp(...)")?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#5

here is the actual query:

agencysacks=# SELECT jobnumseq, (SELECT cname FROM
clientinfo ci WHERE ci.acode = j.clientid) as client,
shrtdesc, to_char(proofduedate, 'Dy FMMon DD, YYYY
HH12 am') FROM jobs j WHERE proofduedate BETWEEN
to_timestamp('01 October 2003 00:01', 'DD Month YYYY
HH24:MI') AND to_timestamp ('01 October 2003 23:59',
'DD Month YYYY HH24:MI') ORDER BY client,
proofduedate;
jobnumseq | client | shrtdesc | to_char
-----------+--------+----------+---------
(0 rows)

agencysacks=# select proofduedate from jobs where
proofduedate > '2003-09-30';

proofduedate
------------------------
2003-09-30 17:00:00-04
2003-09-30 22:00:00-04
2003-10-01 16:00:00-04
2003-09-30 15:00:00-04
2003-10-01 13:00:00-04
2003-10-01 13:00:00-04
2003-10-01 13:00:00-04
2003-10-01 16:00:00-04
2003-10-01 16:00:00-04
2003-10-01 16:00:00-04
2003-10-02 00:00:00-04
(11 rows)

I am trying to create a 'today' type query. between
october 1, 2003 00:01 am and october 1, 2003 23:59

above is my query (which worked as expected on
september 29 and 30 then on october 1 thinks there are
no rows. Maybe I am just high and screwing up the
query. I have been looking at it so long. That's when
I tried september 31 and it 'worked', giving me
october 1s rows.

Ted

here is more:

agencysacks=# SELECT jobnumseq, (SELECT cname FROM
clientinfo ci WHERE ci.acode = j.clientid) as client,
shrtdesc, to_char(proofduedate, 'Dy FMMon DD, YYYY
HH12 am') FROM jobs j WHERE proofduedate BETWEEN
to_timestamp('29 September 2003 00:01', 'DD Month YYYY
HH24:MI') AND to_timestamp ('29 September 2003 23:59',
'DD Month YYYY HH24:MI') ORDER BY client,
proofduedate;

jobnumseq | client | shrtdesc |
to_char
-----------+---------------+------------------+--------------------------
10077 | Chelsea | ad resize to 7x9 | Mon
Sep 29, 2003 04 pm
10078 | Chelsea | | Mon
Sep 29, 2003 06 pm
10074 | Ellen's Magic | new york times | Mon
Sep 29, 2003 04 pm
(3 rows)

agencysacks=# SELECT jobnumseq, (SELECT cname FROM
clientinfo ci WHERE ci.acode = j.clientid) as client,
shrtdesc, to_char(proofduedate, 'Dy FMMon DD, YYYY
HH12 am') FROM jobs j WHERE proofduedate BETWEEN
to_timestamp('30 September 2003 00:01', 'DD Month YYYY
HH24:MI') AND to_timestamp ('30 September 2003 23:59',
'DD Month YYYY HH24:MI') ORDER BY client,
proofduedate;

jobnumseq | client | shrtdesc |
to_char
-----------+----------------+-------------------+--------------------------
10079 | Lazare | New York Times Ad | Tue
Sep 30, 2003 03 pm
10080 | Lazare | Boston Globe | Tue
Sep 30, 2003 05 pm
10081 | Leading Hotels | Philly News | Tue
Sep 30, 2003 10 pm
(3 rows)

Here is september 31, 2003

agencysacks=# SELECT jobnumseq, (SELECT cname FROM
clientinfo ci WHERE ci.acode = j.clientid) as client,
shrtdesc, to_char(proofduedate, 'Dy FMMon DD, YYYY
HH12 am') FROM jobs j WHERE proofduedate BETWEEN
to_timestamp('31 September 2003 00:01', 'DD Month YYYY
HH24:MI') AND to_timestamp ('31 September 2003 23:59',
'DD Month YYYY HH24:MI') ORDER BY client,
proofduedate;

jobnumseq | client | shrtdesc |
to_char
-----------+----------------+-----------------------+--------------------------
10085 | Chelsea | NYT Valentine's day |
Wed Oct 01, 2003 01 pm
10087 | Chelsea | find ring images |
Wed Oct 01, 2003 04 pm
10083 | Ellen's Magic | fgh |
Wed Oct 01, 2003 01 pm
10084 | Ellen's Magic | test the notify stuff |
Wed Oct 01, 2003 01 pm
10086 | Ellen's Magic | test of notify |
Wed Oct 01, 2003 04 pm
10082 | Leading Hotels | Atlanta Herald |
Wed Oct 01, 2003 04 pm
10088 | Leading Hotels | NYT |
Wed Oct 01, 2003 04 pm
(7 rows)

--- Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:
I am sorry, I wasn't clear... when i refered to
01 October 2003
I got zero results. I only got results for the
October
1 date when i refered to it as september 31....

That's a bit hard to believe. Could you show us the
results of the
individual to_timestamp operations ("select
to_timestamp(...)")?

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#6

I should have sent this... it is very interesting..

agencysacks=# select to_timestamp('01 October 2003
00:01', 'DD Month YYYY HH24:MI');
to_timestamp
---------------------
0003-10-01 00:01:00
(1 row)

agencysacks=# select to_timestamp('01 October 2003
23:59', 'DD Month YYYY HH24:MI');
to_timestamp
---------------------
0003-10-01 23:59:00
(1 row)

--- Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:
I am sorry, I wasn't clear... when i refered to
01 October 2003
I got zero results. I only got results for the
October
1 date when i refered to it as september 31....

That's a bit hard to believe. Could you show us the
results of the
individual to_timestamp operations ("select
to_timestamp(...)")?

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#7

I like this one:

agencysacks=# select to_timestamp('31 September 2003
23:59', 'DD Month YYYY HH24:MI');
to_timestamp
------------------------
2003-10-01 23:59:00-04
(1 row)

agencysacks=# select to_timestamp('32 September 2003
23:59', 'DD Month YYYY HH24:MI');
to_timestamp
------------------------
2003-10-02 23:59:00-04
(1 row)

--- Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:
I am sorry, I wasn't clear... when i refered to
01 October 2003
I got zero results. I only got results for the
October
1 date when i refered to it as september 31....

That's a bit hard to believe. Could you show us the
results of the
individual to_timestamp operations ("select
to_timestamp(...)")?

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#8

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:

Quote:

I should have sent this... it is very interesting..
agencysacks=# select to_timestamp('01 October 2003
00:01', 'DD Month YYYY HH24:MI');
to_timestamp
---------------------
0003-10-01 00:01:00
(1 row)

Oh ... duh ... you should have said

regression=# select to_timestamp('01 October 2003 00:01', 'DD FMMonth YYYY HH24:MI');
to_timestamp
------------------------
2003-10-01 00:01:00-04
(1 row)

There's been repeated discussion about whether our to_timestamp code
should be more forgiving of input that does not match the given format,
but right at the moment it's pretty unforgiving.

BTW, have you considered the likelihood that you shouldn't be using
to_timestamp at all? The timestamp datatype input converter gets this
right:

regression=# select '01 October 2003 00:01'::timestamp;
timestamp
---------------------
2003-10-01 00:01:00
(1 row)

ISTM that to_timestamp is intended for cases where you want to be rigid
about the data format. If you think that the input data is
self-explanatory then try just casting it to timestamp.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#9

On Wed, Oct 01, 2003 at 11:38:55PM -0400, Tom Lane wrote:

Quote:

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:
I should have sent this... it is very interesting..
agencysacks=# select to_timestamp('01 October 2003
00:01', 'DD Month YYYY HH24:MI');
to_timestamp
---------------------
0003-10-01 00:01:00
(1 row)

Oh ... duh ... you should have said

regression=# select to_timestamp('01 October 2003 00:01', 'DD FMMonth YYYY HH24:MI');
to_timestamp
------------------------
2003-10-01 00:01:00-04
(1 row)

There's been repeated discussion about whether our to_timestamp code
should be more forgiving of input that does not match the given format,
but right at the moment it's pretty unforgiving.

BTW, have you considered the likelihood that you shouldn't be using
to_timestamp at all? The timestamp datatype input converter gets this
right:

regression=# select '01 October 2003 00:01'::timestamp;
timestamp
---------------------
2003-10-01 00:01:00
(1 row)

ISTM that to_timestamp is intended for cases where you want to be rigid
about the data format. If you think that the input data is
self-explanatory then try just casting it to timestamp.

The to_timestamp() do nothing with date/time and use internal
tm2timestamp() routine only. I don't think that check all date/time
ranges in to_timestamp() is good idea if it's already implemented at
the another place in our code.

Karel

--
Karel Zak <zakkr (AT) zf (DOT) jcu.cz>
http://home.zf.jcu.cz/~zakkr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#10

So really if I had started coding this in a month
other than September I would have seen the problem
sooner...

Thanks for the help....

Ted

--- Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

Theodore Petrosky <tedpet5 (AT) yahoo (DOT) com> writes:
I should have sent this... it is very
interesting..
agencysacks=# select to_timestamp('01 October 2003
00:01', 'DD Month YYYY HH24:MI');
to_timestamp
---------------------
0003-10-01 00:01:00
(1 row)

Oh ... duh ... you should have said

regression=# select to_timestamp('01 October 2003
00:01', 'DD FMMonth YYYY HH24:MI');
to_timestamp
------------------------
2003-10-01 00:01:00-04
(1 row)

There's been repeated discussion about whether our
to_timestamp code
should be more forgiving of input that does not
match the given format,
but right at the moment it's pretty unforgiving.

BTW, have you considered the likelihood that you
shouldn't be using
to_timestamp at all? The timestamp datatype input
converter gets this
right:

regression=# select '01 October 2003
00:01'::timestamp;
timestamp
---------------------
2003-10-01 00:01:00
(1 row)

ISTM that to_timestamp is intended for cases where
you want to be rigid
about the data format. If you think that the input
data is
self-explanatory then try just casting it to
timestamp.

regards, tom lane

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match