Re: timestamp with time zone tutorial - Mailing list pgsql-general

On Sunday 19 July 2009 4:56:09 pm Dennis Gearon wrote:
> I read it better, and it makes more sense now.
>
> But,
> I'd like it to show how to insert:
>     'strings' - which it does
>     timestampz value -->using to_timestampz(...)

For above:
http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html

>     integers::timestampz

See above or:
http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html
In particular see
9.9.1. EXTRACT, date_part

epoch

    For date and timestamp values, the number of seconds since 1970-01-01
00:00:00 UTC (can be negative); for interval values, the total number of
seconds in the interval

    SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
20:38:40-08');
    Result: 982384720

    SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
    Result: 442800

    Here is how you can convert an epoch value back to a time stamp:

    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';



--
Adrian Klaver
aklaver@comcast.net