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@sss.pgh.pa.us> wrote:
> Theodore Petrosky <tedpet5@yahoo.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