Skip site navigation (1) Skip section navigation (2)

7.2 date/time format function problems

From: Nicholas Barthelemy <nbarth(at)adjuvantmedical(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: 7.2 date/time format function problems
Date: 2002-10-22 15:25:57
Message-ID: 3DB56E05.8070008@adjuvantmedical.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

Thanks for your help,
Nicholas


pgsql-admin by date

Next:From: Rod TaylorDate: 2002-10-22 15:26:30
Subject: Re: [HACKERS] Security question : Database access control
Previous:From: Gareth KirwanDate: 2002-10-22 15:19:08
Subject: Re: Quickie about Database locations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group