Re: Timestamp <-> ctime conversion question ... - Mailing list pgsql-general

First, I don't think the epoch conversion has anything to do with this,
so it'd be better to focus on simple timestamps.

That said, you need to be careful about testing this using psql. I
believe psql is what's converting the timestamp to your local timezone.
If you test this using a different interface (such as ODBC), you could
well have different results.

I believe converting a timestam without timezone that you know to be in
UTC into text and then appending 'UTC' to it is valid, btw. I would
however make sure you use the same timezone string for both fields; ie:
don't take a timestamp AT TIME ZONE 'UTC' and append '+00'.

On Tue, Dec 13, 2005 at 05:20:41PM +0100, Alex Mayrhofer wrote:
> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461