Timezone issue with date_part

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(at)io(dot)com