Date: Sat, 2 Nov 2002 01:56:54 -0500
To: pgsql-sql@postgresql.org
Subject: Timezone issue with date_part
Message-ID: <20021102065654.GA8564@roark.kenzoid.com>
Reply-To: kenzoid@io.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.4i
From: Ken Kennedy <kkennedy@kenzoid.com>

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