Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9415E47621D for ; Tue, 22 Oct 2002 11:48:05 -0400 (EDT) Received: from mail.tpchd.org (unknown [192.103.155.40]) by postgresql.org (Postfix) with SMTP id 722934760FD for ; Tue, 22 Oct 2002 11:48:04 -0400 (EDT) Received: from TPC-Message_Server by mail.tpchd.org with Novell_GroupWise; Tue, 22 Oct 2002 08:48:07 -0700 Message-Id: X-Mailer: Novell GroupWise Internet Agent 5.5.2.1 Date: Tue, 22 Oct 2002 08:47:26 -0700 From: "Ian Harding" To: , Subject: Re: 7.2 date/time format function problems Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/287 X-Sequence-Number: 10004 Oooh. Looks like TIMESTAMP became a reserved keyword. http://archives.postgresql.org/pgsql-patches/2001-11/msg00038.php >>> Nicholas Barthelemy 10/22/02 08:39AM >>> I have just installed redhat 8.0. It comes with postgresql rpms for=20 7.2.2. I have been trying to get an application I have written to work, but my queries fail whenever I have=20 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=20 "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)=20 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 =3D 1 AND s.scheduleid =3D a.scheduleid AND s.accountid =3D 3 AND s.accountid =3D ac.accountid AND s.locationid =3D 1 AND s.locationid =3D l.locationid AND s.groupid =3D g.groupid AND s.scheduleid =3D s.scheduleid AND a.jobid =3D 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)=20 functions. If anyone would be so kind as to help me with this issue, it would be greatly=20 appreciated. I don't know if I have to enable something for these functions to work or if the format changed=20 for 7.2.2. I checked the documentation and it was exactly like 7.1. Thanks for your help, Nicholas ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org