Re: any built-in function to get time in seconds?

On Friday 02 April 2010 5:41:09 pm zhong ming wu wrote:
> I have been using this one liner c function that I call my_now() to
> get the number of seconds since some fixed point in the past. I find
> it more convenient than built-in now()
> and if I want abstime I do abstime(my_now()). Thing is everytime I
> do a major version upgrade I had to recompile this and it's a pain in
> the neck. I feel there must be something
> built-in with pg to get the same thing since I can get abstime from it
> like that.

http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html

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
adrian(dot)klaver(at)gmail(dot)com