pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTimezone issue with date_part
4+ messages / 3 participants
[nested] [flat]
* Timezone issue with date_part
@ 2002-11-02 06:56 Ken Kennedy <kkennedy@kenzoid.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ken Kennedy @ 2002-11-02 06:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Timezone issue with date_part
@ 2002-11-02 14:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ken Kennedy <kkennedy@kenzoid.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2002-11-02 14:17 UTC (permalink / raw)
To: kenzoid@io.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Timezone issue with date_part
@ 2002-11-02 20:55 Ken Kennedy <kkennedy@kenzoid.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Ken Kennedy @ 2002-11-02 20:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: kenzoid@io.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Timezone issue with date_part
@ 2002-11-02 21:21 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-11-02 21:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; kenzoid@io.com; +Cc: pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-11-02 21:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-02 06:56 Timezone issue with date_part Ken Kennedy <kkennedy@kenzoid.com>
2002-11-02 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-02 20:55 ` Ken Kennedy <kkennedy@kenzoid.com>
2002-11-02 21:21 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox