Re: 7.2 date/time format function problems

On Tue, 22 Oct 2002, Nicholas Barthelemy wrote:

> I have just installed redhat 8.0. It comes with postgresql rpms for
> 7.2.2. I have been trying to get an
> application I have written to work, but my queries fail whenever I have
> queries that use internal
> date/time formatting functions.
> example:
>
> SELECT a.assignmentid AS "id",
> u.lastname || ', ' || u.firstname || ' ' || u.middleint AS "assigned",
> j.name AS "job_name",
> extract(DOW FROM TIMESTAMP TIMESTAMP(a.startdate)) AS "dow",
> TO_CHAR(TO_TIMESTAMP(a.starttime, 'HH:MI:SS'), 'HH12:MI am') AS
> "starttime",
> TO_CHAR(TO_TIMESTAMP(a.stoptime, 'HH:MI:SS'), 'HH12:MI am') AS "stoptime",
> ((extract(HOUR FROM TIME (a.stoptime - a.starttime)) +
> (extract(MINUTE FROM TIME (a.stoptime - a.starttime))/60)) - a.break)
> AS "hrs",
> a.break AS "break"
> FROM assignment a LEFT JOIN users u USING(userid),
> schedule s, job j, account ac, location l, groups g
> WHERE s.scheduleid = 1 AND
> s.scheduleid = a.scheduleid AND
> s.accountid = 3 AND
> s.accountid = ac.accountid AND
> s.locationid = 1 AND
> s.locationid = l.locationid AND
> s.groupid = g.groupid AND
> s.scheduleid = s.scheduleid AND
> a.jobid = j.jobid
> ORDER BY j.name ASC, a.starttime ASC, u.lastname ASC;
>
> ERROR: parser: parse error at or near "TIMESTAMP"
>
> The problem areas are the timestamp() and extract(hour from time)
> functions. If anyone would
> be so kind as to help me with this issue, it would be greatly
> appreciated. I don't know if I have to
> enable something for these functions to work or if the format changed
> for 7.2.2. I checked the
> documentation and it was exactly like 7.1.

timestamp() and time() became the type specifiers for the type with
a particular precision. You can use "timestamp"() or "time"() or
it'd probably be better to use SQL standard casts, CAST (expr AS type).