All,
i'm trying to convert time stamps to "seconds since epoch" and back. My
original timestamps are given with a time zone (UTC), and i have a
conversion function to "ctime" which works pretty well:
CREATE OR REPLACE FUNCTION to_ctime (timestamptz) RETURNS integer AS $$
SELECT date_part('epoch', $1)::integer;
$$ LANGUAGE SQL;
test=# select to_ctime('1970-01-01T00:00Z');
to_ctime
----------
0
(1 row)
However, i fail at converting ctime values back into timestamps with time
zone UTC. Inspired from the query on the date/time docs pages, i've tried
the following approaches:
test=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch'
+ 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';
timezone
---------------------
1970-01-01 00:00:00
(1 row)
This would yield the right timestamp, but loses the time zone. The nex approach:
test=# SELECT (TIMESTAMP WITHOUT TIME ZONE 'epoch'
+ 0 * INTERVAL '1 second') AT TIME ZONE 'UTC';
timezone
------------------------
1970-01-01 01:00:00+01
(1 row)
yields the right timestamp (from an absolute point of view) as well, but in
the wrong (my local) timezone. My next approach:
test=# select timezone('UTC', timestamp with time zone 'epoch' + 0 *
INTERVAL '1 second');
timezone
---------------------
1970-01-01 00:00:00
(1 row)
loses the time zone as well. I'm a bit reluctant to use tricks like manually
appending the "Z" as literal text so that it would "look like" a valid UTC
time stamp.
I'd appreciate any insight on this - am i simply missing something? I'm
using PostgreSQL 8.1.0, if that matters.
thanks & cheers
--
Alex Mayrhofer <axelm (at) nona.net>
http://nona.net/features/map/
