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