From: Ken Kennedy <kkennedy@kenzoid.com>
To: pgsql-sql@postgresql.org
Subject: Timezone issue with date_part
Date: Sat, 2 Nov 2002 01:56:54 -0500
Message-ID: <20021102065654.GA8564@roark.kenzoid.com> (raw)
In trying to debug some code, I've come across this SQL issue that's
causing my problem.
I've got two epoch time values that I have to compare. Time #1 seems
to be working straightforwardly enough, but a tricky timezone-related
error has surfaced with Time #2.
Looking at the straight timestamp:
kenzoid=# select max(posted_date) from pinds_blog_entries
kenzoid-# where package_id = '2969'
and draft_p = 'f'
and deleted_p = 'f'
kenzoid-# kenzoid-# kenzoid-# ;
max
----------------------------
2002-11-01 09:56:41.474084
That's correct, for my timezone. (EST5EDT)
The query that's in the script now to return that as an epoch time is:
kenzoid=# select coalesce
(date_part('epoch',max(posted_date)),0) as last_update
from pinds_blog_entries
where package_id = '2969'
and draft_p = 'f'
and deleted_p = 'f'
kenzoid-# kenzoid-# kenzoid-# kenzoid-# kenzoid-# ;
last_update
------------------
1036144601.47408
I finally realized something was amiss, and reconstituted that epoch
value:
kenzoid=# select timestamp 'epoch' + interval '1036144601.47408
seconds';
?column?
------------------------------
2002-11-01 04:56:41.47408-05
I'm five hours off...my timezone value, I imagine.
I tried putting the TIMESTAMP into the date_part, but no joy:
kenzoid=# select coalesce (date_part('epoch',
kenzoid-# TIMESTAMP max(posted_date)),0)
kenzoid-# as last_update
kenzoid-# from pinds_blog_entries
where package_id = '2969'
and draft_p = 'f'
and deleted_p = 'f'
kenzoid-# kenzoid-# kenzoid-# kenzoid-# ;
ERROR: parser: parse error at or near "max"
I kinda figured that.
So I'm stuck, without making two calls. If I call to the db and get
max(posted_date), and then turn around and call the date_part
with that value, things work. But I'm trying to avoid the two db
calls. Any ideas? Thanks!!
--
Ken Kennedy | http://www.kenzoid.com | kenzoid@io.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kkennedy@kenzoid.com
Subject: Re: Timezone issue with date_part
In-Reply-To: <20021102065654.GA8564@roark.kenzoid.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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