timestamp/date comparison

[Campano, Troy]

Hi,
I'm trying to compare a timestamp to current_timesta mp but I'm having
trouble.
I want to compare just the date piece of my timestamp column to just the
date piece of current_timesta mp.

I'm getting weird results that I don't understand.
When I use TO_DATE it changes the year, month, etc.

Any ideas?
anna=> SELECT request_date,TO _DATE(request_d ate,'YYYY-MM-DD HH24:MM:SS')
FROM anna_onestop_da tabase_t ; request_date | to_date
---------------------+------------
2003-10-08 09:15:57 | 2004-03-09
2003-10-08 09:18:37 | 2004-06-09
2003-10-08 09:20:11 | 2004-08-09
2003-10-08 09:27:56 | 2005-03-11
2003-10-08 09:30:38 | 2005-06-11
2003-10-08 09:32:09 | 2005-08-11
2003-10-08 09:47:07 | 2006-11-13
2003-10-08 10:04:22 | 2003-04-08
2003-10-08 11:46:37 | 2006-10-14
2003-10-08 12:08:41 | 2003-08-08
2003-10-08 12:21:52 | 2004-09-09
2003-10-08 12:42:27 | 2006-06-13
2003-10-08 13:13:53 | 2004-01-08

anna=> SELECT
TO_DATE(request _date,'MM/DD/YYYY'),TO_DATE( current_timesta mp,'MM/DD/YYYY
') FROM anna_onestop_da tabase_t WHERE TO_DATE(request _date,'MM/DD/YYYY')

TO_DATE(current _timestamp,'MM/DD/YYYY');

to_date | to_date
------------+------------
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0181-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0182-10-03 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
0183-10-04 | 0180-10-26
thanks!

Troy Campano

[Stephan Szabo]

On Fri, 13 Feb 2004, Campano, Troy wrote:

Hi,
I'm trying to compare a timestamp to current_timesta mp but I'm having
trouble.
I want to compare just the date piece of my timestamp column to just the
date piece of current_timesta mp.

I'm getting weird results that I don't understand.
When I use TO_DATE it changes the year, month, etc.
I think you probably don't want to_date in any case. CAST(whatever AS
DATE) is probably better. The to_date way probably is taking the
timestamp converting it to text and then attempting to convert the text
back.
anna=> SELECT request_date,TO _DATE(request_d ate,'YYYY-MM-DD HH24:MM:SS')
Here, you're using the minutes as month information I think.
anna=> SELECT
TO_DATE(request _date,'MM/DD/YYYY'),TO_DATE( current_timesta mp,'MM/DD/YYYY
') FROM anna_onestop_da tabase_t WHERE TO_DATE(request _date,'MM/DD/YYYY')

TO_DATE(current _timestamp,'MM/DD/YYYY');

I'm not sure why this is working at all, but using the standard output
format for a timestamp, it doesn't follow the form MM/DD/YYYY I believe,
so the format string doesn't really line up with the data.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Campano, Troy" <Tr**********@L ibertyMutual.co m> writes:

anna> SELECT request_date,TO _DATE(request_d ate,'YYYY-MM-DD HH24:MM:SS')

The correct way to write the format string would have been

SELECT request_date,TO _DATE(request_d ate,'YYYY-MM-DD HH24:MI:SS')

(minutes are MI not MM). It was evidently taking the minute number as
month number, and not noticing that the field was out of range :-(.
People have complained before that to_date() and related functions don't
detect all the error cases one would reasonably expect them to complain
about ...

However, this all seems like the hard way to solve your problem.
Why don't you just cast the timestamp value to date type, ie
"CAST(request_d ate AS date)", or just "request_date:: date" if you don't
mind using a Postgres-specific syntax. The date_trunc() function also
is worth knowing about.

regards, tom lane

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