Re: timestamp <-> ctime conversion question...

On Tue, Dec 13, 2005 at 05:31:49PM +0100, Alex Mayrhofer wrote:
> 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.

According to the Date/Time Types documentation,

All timezone-aware dates and times are stored internally in UTC.
They are converted to local time in the zone specified by the
timezone configuration parameter before being displayed to the
client.

As far as I know there isn't a way to defeat this. However, the
developers' TODO file does have the following item:

Allow TIMESTAMP WITH TIME ZONE to store the original timezone
information, either zone name or offset from UTC

Presumably this would allow timestamps to be displayed with a
timezone other than the current setting.

If you don't mind having the timestamp as a text value (which you
could cast to timestamptz, albeit with a loss of the desired time
zone) then you could try something like this:

CREATE FUNCTION settz(tz text, ts timestamptz) RETURNS text AS $$
DECLARE
savetz text;
retval text;
BEGIN
savetz := current_setting('TimeZone');
PERFORM set_config('TimeZone', tz, true);
retval := ts;
PERFORM set_config('TimeZone', savetz, true);
RETURN retval;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

Examples:

test=> SELECT now();
now
-------------------------------
2005-12-13 10:20:54.109306-07
(1 row)

test=> SELECT settz('UTC', now());
settz
-------------------------------
2005-12-13 17:20:54.109306+00
(1 row)

test=> SELECT settz('UTC', now())::timestamptz;
settz
-------------------------------
2005-12-13 10:20:54.109306-07
(1 row)

--
Michael Fuhr