Thread: is this my date problem

is this my date problem

From
Theodore Petrosky
Date:
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

Re: is this my date problem

From
Bruno Wolff III
Date:
On Wed, Oct 01, 2003 at 10:26:57 -0700,
  Theodore Petrosky <tedpet5@yahoo.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

Re: is this my date problem

From
Theodore Petrosky
Date:
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@wolff.to> wrote:
> On Wed, Oct 01, 2003 at 10:26:57 -0700,
>   Theodore Petrosky <tedpet5@yahoo.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

Re: is this my date problem

From
Tom Lane
Date:
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

Re: is this my date problem

From
Theodore Petrosky
Date:
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

Re: is this my date problem

From
Theodore Petrosky
Date:
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@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

Re: is this my date problem

From
Theodore Petrosky
Date:
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@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

Re: is this my date problem

From
Tom Lane
Date:
Theodore Petrosky <tedpet5@yahoo.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

Re: is this my date problem

From
Karel Zak
Date:
On Wed, Oct 01, 2003 at 11:38:55PM -0400, Tom Lane wrote:
> Theodore Petrosky <tedpet5@yahoo.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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Re: is this my date problem

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

Re: is this my date problem

From
Richard Ellis
Date:
On Wed, Oct 01, 2003 at 07:57:18PM -0700, Theodore Petrosky wrote:
> 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,
>...
> I am trying to create a 'today' type query. between
> october 1, 2003 00:01 am and october 1, 2003 23:59

If you want a "today" type query, why are you using between?  This
should work, and be a whole lot more reliable:

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 date(proofduedate) = '2003-10-01')
ORDER BY client,

or if you want to derive "today" automatically this should work:

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 date(proofduedate) = date(now()))
ORDER BY client,

Re: is this my date problem

From
Tom Lane
Date:
Karel Zak <zakkr@zf.jcu.cz> writes:
>  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.

But it evidently *isn't* checked.  As of CVS tip:

regression=# select to_timestamp('44 October 2003', 'DD FMMonth YYYY');
      to_timestamp
------------------------
 2003-11-13 00:00:00-05
(1 row)

The regular timestamp input converter certainly has the checks:

regression=# select '44 October 2003'::timestamp;
ERROR:  invalid input syntax for type timestamp: "44 October 2003"

but evidently those checks are not in whatever code path to_timestamp
is calling.

            regards, tom lane

Re: is this my date problem

From
Karel Zak
Date:
On Thu, Oct 02, 2003 at 10:04:39AM -0400, Tom Lane wrote:
> Karel Zak <zakkr@zf.jcu.cz> writes:
> >  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.
>
> But it evidently *isn't* checked.  As of CVS tip:
>
> regression=# select to_timestamp('44 October 2003', 'DD FMMonth YYYY');
>       to_timestamp
> ------------------------
>  2003-11-13 00:00:00-05
> (1 row)
>
> The regular timestamp input converter certainly has the checks:
>
> regression=# select '44 October 2003'::timestamp;
> ERROR:  invalid input syntax for type timestamp: "44 October 2003"

 Sure, because it check date/time parsers and there isn't common way
 how check it if you don't use there parses.

 OK, I will add 'tm' struct checker to my TODO for 7.5

    Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/