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