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

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:
------------------------------------------------------------------------
ERROR:  function to_timestamp(timestamp without time zone, unknown) does
not exist
LINE 3:   WHERE to_timestamp(a.from_datetime,'YYYY/MM/DD') >= to_tim...
                 ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
------------------------------------------------------------------------

Is the "to_timestamp" function not supported anymore in 8.3.6? I could
not read anything about it in the 8.3 documentation.

Regards
Nico

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)));

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

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
>= to the output of 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())
>

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