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

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-0415: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
fromts2int; 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)
aststz_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')
aststz_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
timezone '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.