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). ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
