pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed7.2 date/time format function problems
3+ messages / 3 participants
[nested] [flat]
* 7.2 date/time format function problems
@ 2002-10-22 15:39 Nicholas Barthelemy <nbarth@adjuvantmedical.com>
2002-10-22 16:03 ` Re: 7.2 date/time format function problems Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nicholas Barthelemy @ 2002-10-22 15:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 7.2 date/time format function problems
2002-10-22 15:39 7.2 date/time format function problems Nicholas Barthelemy <nbarth@adjuvantmedical.com>
@ 2002-10-22 16:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-10-22 16:03 UTC (permalink / raw)
To: Nicholas Barthelemy <nbarth@adjuvantmedical.com>; +Cc: pgsql-sql
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).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 7.2 date/time format function problems
@ 2002-10-22 15:47 Ian Harding <ianh@tpchd.org>
0 siblings, 0 replies; 3+ messages in thread
From: Ian Harding @ 2002-10-22 15:47 UTC (permalink / raw)
To: nbarth@adjuvantmedical.com; pgsql-sql
Oooh. Looks like TIMESTAMP became a reserved keyword.
http://archives.postgresql.org/pgsql-patches/2001-11/msg00038.php
>>> Nicholas Barthelemy <nbarth@adjuvantmedical.com> 10/22/02 08:39AM >>>
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-10-22 16:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-22 15:39 7.2 date/time format function problems Nicholas Barthelemy <nbarth@adjuvantmedical.com>
2002-10-22 16:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-22 15:47 Re: 7.2 date/time format function problems Ian Harding <ianh@tpchd.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox