By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,592 Members | 1,005 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,592 IT Pros & Developers. It's quick & easy.

timestamp/date comparison

P: n/a
Hi,
I'm trying to compare a timestamp to current_timestamp but I'm having
trouble.
I want to compare just the date piece of my timestamp column to just the
date piece of current_timestamp.

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_date,'YYYY-MM-DD HH24:MM:SS')
FROM anna_onestop_database_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_timestamp,'MM/DD/YYYY
') FROM anna_onestop_database_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
Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
On Fri, 13 Feb 2004, Campano, Troy wrote:
Hi,
I'm trying to compare a timestamp to current_timestamp but I'm having
trouble.
I want to compare just the date piece of my timestamp column to just the
date piece of current_timestamp.

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_date,'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_timestamp,'MM/DD/YYYY
') FROM anna_onestop_database_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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2

P: n/a
"Campano, Troy" <Tr**********@LibertyMutual.com> writes:
anna> SELECT request_date,TO_DATE(request_date,'YYYY-MM-DD HH24:MM:SS')


The correct way to write the format string would have been

SELECT request_date,TO_DATE(request_date,'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_date 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

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.