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.klaver@gmail.com

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.

Well you haven't specified which fixed point in the past. But if you
want to get the number of seconds since the unix epoch use:

extract(epoch from current_timestamp)

Scott