Обсуждение: 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@io.com

Ken Kennedy <kkennedy@kenzoid.com> writes:
> [ date_part('epoch') is wrong for a timestamp value ]

The epoch value is really only correct for a TIMESTAMP WITH TIME ZONE
value.  If you apply date_part('epoch') to a timestamp without time zone,
as you appear to be doing here, what you will get is the epoch for the
given value interpreted as GMT.

A hack solution is to cast the value to TIMESTAMP WITH TIME ZONE before
extracting the epoch; the cast will assume that the given value is local
time.  But a better idea is to store the column as TIMESTAMP WITH TIME
ZONE in the first place.

(IMHO, the SQL spec is really brain-dead to define timestamp without
time zone as the default form of timestamp; the variant with time zone
is much more useful for most applications.  It's far too easy to shoot
yourself in the foot when working with zoneless timestamps --- usually
in a way that you won't notice until daylight-savings transition time
comes around, or you roll out the app to users in other time zones.)
        regards, tom lane

On Sat, Nov 02, 2002 at 09:17:14AM -0500, Tom Lane wrote:
> Ken Kennedy <kkennedy@kenzoid.com> writes:
> > [ date_part('epoch') is wrong for a timestamp value ]
> 
> The epoch value is really only correct for a TIMESTAMP WITH TIME ZONE
> value.  If you apply date_part('epoch') to a timestamp without time zone,
> as you appear to be doing here, what you will get is the epoch for the
> given value interpreted as GMT.

Excellent! I see. The table is indeed using TIMESTAMP WITHOUT TIME
ZONE. (It is, in fact, an old 'datetime' hold-on in the table creation
DDL.) Hopefully, I can alter that sucker in place...it'll help for
upgrade scripts.
> A hack solution is to cast the value to TIMESTAMP WITH TIME ZONE before
> extracting the epoch; the cast will assume that the given value is local
> time.  But a better idea is to store the column as TIMESTAMP WITH TIME
> ZONE in the first place.

Gotcha. I've confirmed the hack solution is working for now, and
eliminates my even hackier (more hackish?) two-call solution. I'll get
with the package owner (this is in an OpenACS package) and we'll work
out an upgrade for the table and procs.

> (IMHO, the SQL spec is really brain-dead to define timestamp without
> time zone as the default form of timestamp; the variant with time zone
> is much more useful for most applications.  

I see exactly what you're saying now. I guess that's the reason
datetime resolves to 'TIMESTAMP WITHOUT TIME ZONE'? I agree...the TZ
is very useful to have tagging along!

Thanks so much for your help, Tom!

-- 

Ken Kennedy    | http://www.kenzoid.com    | kenzoid@io.com

Tom,

> (IMHO, the SQL spec is really brain-dead to define timestamp without
> time zone as the default form of timestamp; the variant with time zone
> is much more useful for most applications.  It's far too easy to shoot
> yourself in the foot when working with zoneless timestamps --- usually
> in a way that you won't notice until daylight-savings transition time
> comes around, or you roll out the app to users in other time zones.)

It's pretty easy to shoot yourself in the foot with time zones, as well.   For
example, most people are thrown off by the daylight-savings-time shift in
date calculations; for example:
select '2002-10-20 00:00:00 PDT'::TIMESTAMPTZ + '2 weeks'::INTERVAL
jwnet-> ;       ?column?
------------------------2002-11-02 23:00:00-08

This sort of behavior can really muck with calendar applications.  Of course,
it could be solved with a DAY/WEEK subtype, but I've already advocated for
that.

--

-Josh BerkusAglio Database SolutionsSan Francisco