Re: is this my date problem

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

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

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:
> 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

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

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

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(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

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(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

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

On Wed, Oct 01, 2003 at 11:38:55PM -0400, Tom Lane wrote:
> 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(dot)cz>
http://home.zf.jcu.cz/~zakkr/

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,

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

Karel Zak <zakkr(at)zf(dot)jcu(dot)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

On Thu, Oct 02, 2003 at 10:04:39AM -0400, Tom Lane wrote:
> Karel Zak <zakkr(at)zf(dot)jcu(dot)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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/