After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist

Nico Grubert <nicogrubert@gmail.com> writes:

SELECT a.*
FROM tblevent a
WHERE to_timestamp(a.from_datetime,'YYYY/MM/DD') >= to_timestamp(
NOW(),'YYYY/MM/DD' )
ORDER BY a.from_datetime

In PostgreSQL 8.2.6 everything works fine.
In PostgreSQL 8.3.6 I get the following error:
------------------------------------------------------------------------
ERROR: function to_timestamp(timestamp without time zone, unknown) does
not exist

This query makes little sense. Why are you trying to convert a
timestamp to a timestamp? Is this a bizarre substitute for date_trunc()?

The reason it "works" in 8.2 is that 8.2 will allow an implicit coercion
from timestamp to text --- although I put "work" in quotes because it
will not work too well if datestyle is anything but the default. You
could make it not-quite-work the same in 8.3 by adding an explicit
coercion of from_datetime to text, but I think you ought to read up on
date_trunc instead.

regards, tom lane

On Thu, Mar 5, 2009 at 9:48 AM, Nico Grubert <nicogrubert@gmail.com> wrote:

Dear list members

I have upgraded my PostgreSQL 8.2.6 to 8.3.6 and I am having trouble with
calling "to_timestamp" function.

Here is the query I use:

 SELECT a.*
 FROM tblevent a
 WHERE to_timestamp(a.from_datetime,'YYYY/MM/DD') >= to_timestamp(
NOW(),'YYYY/MM/DD' )
 ORDER BY a.from_datetime

In PostgreSQL 8.2.6 everything works fine.
In PostgreSQL 8.3.6 I get the following error:

OK, so you want to see if a timestamp is greater than now()? Why not
just compare them?

where a.from_datetime >= now()

?

This query makes little sense. Why are you trying to convert a
timestamp to a timestamp? Is this a bizarre substitute for date_trunc()?

The "from_datetime" column is of type "timestamp" but I want to check
only the date, not the time.
In this example I want to retrieve all records whose "from_datetime" is
e.g. >= 2009/05/06 (Now()) so I'd like to get results with a
"from_datetime" like e.g.
- 2009/05/06 00:05:00
- 2009/05/06 23:30:00
- 2009/05/07 10:15:00

Regards
Nico

On Thu, Mar 5, 2009 at 11:53 PM, Nico Grubert <nicogrubert@gmail.com> wrote:

This query makes little sense.  Why are you trying to convert a
timestamp to a timestamp?  Is this a bizarre substitute for date_trunc()?

The "from_datetime" column is of type "timestamp" but I want to check only
the date, not the time.
In this example I want to retrieve all records whose "from_datetime" is e.g.

= 2009/05/06 (Now()) so I'd like to get results with a "from_datetime" like

e.g.
- 2009/05/06 00:05:00
- 2009/05/06 23:30:00
- 2009/05/07 10:15:00

Then just cast it to date...

select * from table where timestampfield::date >= '2009-01-01'::date;

This query makes little sense. Why are you trying to convert a
timestamp to a timestamp? Is this a bizarre substitute for date_trunc()?

Got it:
Thanks for the "date_trunc" tip.

This query works fine:
date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())

Or use date_trunc:

select * from sometable where timestampfield >= date_trunc('day',now());

OK, so you want to see if a timestamp is greater than now()? Why not
just compare them?

where a.from_datetime >= now()

No, not the whole timestamp. I dont want to check the time.
So I had to truncate the datetime with:

date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())

On Thu, Mar 5, 2009 at 11:58 PM, Nico Grubert <nicogrubert@gmail.com> wrote:

This query makes little sense.  Why are you trying to convert a
timestamp to a timestamp?  Is this a bizarre substitute for date_trunc()?

Got it:
Thanks for the "date_trunc" tip.

This query works fine:
date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())

Note that if you need to do this a lot you can make an index on that function.

smarlowe=# create table sometable (ts timestamp);
CREATE TABLE
smarlowe=# create index sometable_ts_day on sometable((date_trunc('day',ts)));

On Thu, Mar 5, 2009 at 11:59 PM, Nico Grubert <nicogrubert@gmail.com> wrote:

OK, so you want to see if a timestamp is greater than now()?  Why not
just compare them?

where a.from_datetime >= now()

No, not the whole timestamp. I dont want to check the time.
So I had to truncate the datetime with:

date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())

The functionality is the same, since the now() will get rounded down
to the date with time of 00:00:00. So, anytime for that day will be

This query makes little sense. Why are you trying to convert a
timestamp to a timestamp? Is this a bizarre substitute for

date_trunc()?

The "from_datetime" column is of type "timestamp" but I want to check
only the date, not the time.
In this example I want to retrieve all records whose "from_datetime" is
e.g. >= 2009/05/06 (Now()) so I'd like to get results with a
"from_datetime" like e.g.
- 2009/05/06 00:05:00
- 2009/05/06 23:30:00
- 2009/05/07 10:15:00

Regards
Nico

I use something like this:

Where from_datetime::date >= current_date
Or
Where date_trunc('day', from_datetime) >= current_date

(current_date is like "now()" except it's a date instead of timestamp)

OK, so you want to see if a timestamp is greater than now()? Why not
just compare them?

where a.from_datetime >= now()

No, not the whole timestamp. I dont want to check the time.
So I had to truncate the datetime with:

date_trunc('day', a.from_datetime) >= date_trunc('day', NOW())

If you're going to truncate the NOW(), just go with CURRENT_DATE instead.

If you're going to truncate the NOW(), just go with CURRENT_DATE instead.

Thanks for the "CURRENT_DATE" tip, Adam. Works fine!

Scott Marlowe <scott.marlowe@gmail.com> writes:

The functionality is the same, since the now() will get rounded down
to the date with time of 00:00:00. So, anytime for that day will be

= to the output of date_trunc('day',now())

Yeah. So actually it's sufficient to do
a.from_datetime >= current_date
and a regular index on from_datetime would be fine for that.

regards, tom lane