timestamp <-> ctime conversion question... - Mailing list pgsql-general

Hi,

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 those 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/