Re: definative way to place secs from epoc into timestamp - Mailing list pgsql-sql

On 2005-03-04, Bret Hughes <bhughes@elevating.com> wrote:
>> Unix epoch times correspond to timestamp _with_ time zone.
>> 
>> (Why are you using timestamp without time zone anyway? For recording the
>> time at which an event occurred that usage is simply wrong - in fact I
>> can't see any situation in which a Unix epoch time can correctly be
>> converted to a timestamp without time zone.)
>
> Valid question.  Because there is no reason to keep up with time zones

It's a common mistake to think that just because you don't need to keep
track of time zones that somehow using timestamp without time zone is
correct. It is _not_. "timestamp with time zone" and "timestamp without
time zone" have _very_ different semantics.

One way to look at it is that "timestamp with time zone" designates a
specific instant in absolute time (past or future). It is therefore the
correct type to use for recording when something happened. In contrast,
"timestamp without time zone" designates a point on the calendar, which
has a different meaning according to where you are, and when. So the
latter type crops up in some cases in calendar applications, and also in
input/output conversions, but it's more often than not the _wrong_ type
to use for storage, since the meaning changes with the timezone (and data
_does_ get moved across timezones, whether due to physical relocation or
other factors).

Unix epoch times have the same semantics as "timestamp with time zone".

> and the fact that  I want the same value from the data base that I put
> into it.

"same" in which sense? The same absolute point in time? Or the same point
on a calendar? Obviously if the timezone doesn't change, then the two are
equivalent; but which one is your application actually looking for? (If
your app is using Unix epoch times, then it's looking only at the absolute
time and not the calendar time...)

Here's an example of how it breaks (using your own conversion functions):

test=> set timezone to 'UTC';
SET
test=> insert into ttst values (int2ts(1109916954));
INSERT 887766166 1
test=> select ts,ts2int(ts) from ttst;        ts          |   ts2int   
---------------------+------------2005-03-04 06:15:54 | 1109916954
(1 row)

(that is the correct UTC time corresponding to 1109916954)

test=> set timezone to 'America/Denver';
SET
test=> select ts,ts2int(ts) from ttst;        ts          |   ts2int   
---------------------+------------2005-03-04 06:15:54 | 1109942154
(1 row)

test=> set timezone to 'America/New_York';
SET
test=> select ts,ts2int(ts) from ttst;        ts          |   ts2int   
---------------------+------------2005-03-04 06:15:54 | 1109934954
(1 row)

Notice the value stored in the DB didn't change, but it suddenly means
something different...

In contrast, if you do the same thing with "timestamp with time zone",
then the Unix time that you get back will _always_ be the same, as you
would expect, regardless of the time zone. Using functions identical to
yours except using "with time zone":

test=> insert into tztst values (int2tsz(1109916954));
INSERT 889130554 1
test=> select ts,ts2int(ts) from tztst;          ts           |   ts2int   
------------------------+------------2005-03-04 06:15:54+00 | 1109916954
(1 row)

test=> set timezone to 'America/New_York';
SET
test=> select ts,ts2int(ts) from tztst;          ts           |   ts2int   
------------------------+------------2005-03-04 01:15:54-05 | 1109916954
(1 row)

test=> set timezone to 'America/Los_Angeles';
SET
test=> select ts,ts2int(ts) from tztst;          ts           |   ts2int   
------------------------+------------2005-03-03 22:15:54-08 | 1109916954
(1 row)

Notice that the stored timestamp doesn't actually change; it is displayed
differently according to the timezone. The Unix time correctly _doesn't_
change, reflecting the fact that what we stored was the absolute time.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services