Thread: 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