Search Postgresql Archives

timestamp <-> ctime conversion question...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux