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@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
