Re: is this my date problem

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(dot)pa(dot)us> wrote:
> 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