Search Postgresql Archives

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

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

 



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


[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