Обсуждение: 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