Home > mailing lists

Thread: timestamp/date comparison

timestamp/date comparison

From

"Campano, Troy"

Date:

13 February 2004, 16:26:39

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

0182-10-03 | 0180-10-26

0183-10-04 | 0180-10-26

thanks!

Troy Campano

Re: timestamp/date comparison

From

Stephan Szabo

Date:

13 February 2004, 17:27:09

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.

Re: timestamp/date comparison

From

Tom Lane

Date:

15 February 2004, 16:05:12

"Campano, Troy" <Troy.Campano@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