Re: timestamp with time zone tutorial

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(at)comcast(dot)net