Re: definative way to place secs from epoc into timestamp

On Mar 4, 2005, at 14:47, Bret Hughes wrote:

> On Thu, 2005-03-03 at 14:58, Andrew - Supernews wrote:
>> (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
> and the fact that I want the same value from the data base that I put
> into it. The app that this db supports is written in php and I kept
> getting something different out than what I put into it in the other
> passes I made while trying to get my head around this. the timestamps
> have historically been stored in flat files.

<snip />

> What goes in comes out. Gotta like it.

I think the reason this works is because your webserver and your
postgresql server are in the same time zone, which is probably an
assumption made in a great-many cases. You may run into problems if at
some time the dbms and webserver are not in the same time zone and
you're relying on dbms-generated times (such as now() or
current_timestamp), or if the system
is relocated to another time zone.

I think the following illustrates a problem that can occur if the
assumption that the time zone is not constant is no longer valid.

Your system is working for you, so that's great. I just wanted to
explore this for myself a bit more -- I find the time zone related
material hard to get my head around myself :). Since I went through it,
I thought I'd share it with the list.

Regards,

Michael Glaesemann
grzm myrealbox com

test=# create table ts2int (ts2int_id serial not null unique
, ts timestamp without time zone default current_timestamp
, tstz timestamptz default current_timestamp) without oids;
NOTICE: CREATE TABLE will create implicit sequence
"ts2int_ts2int_id_seq" for serial column "ts2int.ts2int_id"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"ts2int_ts2int_id_key" for table "ts2int"
CREATE TABLE
test=# \d ts2int
Table "public.ts2int"
Column | Type |
Modifiers
-----------+-----------------------------
+---------------------------------------------------------------
ts2int_id | integer | not null default
nextval('public.ts2int_ts2int_id_seq'::text)
ts | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
tstz | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
Indexes:
"ts2int_ts2int_id_key" UNIQUE, btree (ts2int_id)

test=# insert into ts2int (ts) values (default);
INSERT 0 1

To simulate webserver and postgresql server being in different time
zones, I'm
using the "at time zone" construct to convert to CST.

test=# insert into ts2int (ts) values (current_timestamp at time zone
'CST');
INSERT 0 1
test=# select * from ts2int;
ts2int_id | ts | tstz
-----------+----------------------------+-------------------------------
1 | 2005-03-04 15:46:20.443158 | 2005-03-04 15:46:20.443158+09
2 | 2005-03-04 00:46:50.336831 | 2005-03-04 15:46:50.336831+09
(2 rows)

test=# select ts2int_id
, extract('epoch' from ts) as ts_epoch
, extract ('epoch' from tstz) as tstz_epoch
from ts2int;
ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------
1 | 1109918780.44316 | 1109918780.44316
2 | 1109864810.33683 | 1109918810.33683
(2 rows)

Note that ts_epoch and tstz_epoch are the same for 1, but different for
2. Both
ts and tstz are being evaluated at +9 (the postgres server time zone
offset). As
ts for 2 wasn't inserted at +9, it's not the same.

test=# select ts2int_id
, extract('epoch' from ts at time zone 'CST') as ts_epoch
, extract ('epoch' from tstz) as tstz_epoch
from ts2int;
ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------
1 | 1109972780.44316 | 1109918780.44316
2 | 1109918810.33683 | 1109918810.33683
(2 rows)

Note that ts_epoch and tstz_epoch are the same for 2, but different for
1. ts
was inserted relative to CST and is now being evaluated "at time zone
'CST'", so
the ts and tstz values for 2 are "the same". ts_epoch for 2 is also the
Unix timestamp for the time that was originally inserted.

test=# select ts2int_id
, extract('epoch' from ts) as ts_epoch
, extract ('epoch' from tstz at time zone 'CST') as tstz_epoch
from ts2int;
ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------
1 | 1109918780.44316 | 1109864780.44316
2 | 1109864810.33683 | 1109864810.33683
(2 rows)

Note again that ts_epoch and tstz_epoch are the same for 2, but
different for 1. ts is
being evaluated at +9, while tstz is being converted to CST before
extracting the epoch. However, both ts_epoch and tstz_epoch for 2 are
not the Unix timestamps for the timestamps that were originally
inserted. Actually, none of them are.

And finally, for completeness:

test=# select ts2int_id
, extract('epoch' from ts at time zone 'CST') as ts_epoch
, extract ('epoch' from tstz at time zone 'CST') as tstz_epoch
from ts2int;
ts2int_id | ts_epoch | tstz_epoch
-----------+------------------+------------------
1 | 1109972780.44316 | 1109864780.44316
2 | 1109918810.33683 | 1109864810.33683
(2 rows)

Now everything's pretty screwed up. ts_epoch for 2 is actually
returning the "proper answer" (meaning the epoch for the time that was
originally inserted), but it's hard to tell as everything else is
out-of-whack.