EPCOH: extract returns millisecs too?
From the iDocs:
epoch
For date and timestamp values, the number of seconds since
1970-01-01 00:00:00-00 (Result may be negative.); for interval values,
the total number of seconds in the interval
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320
But if I seem to get milliseconds too in my queries:
$ psql sessions -c "SELECT EXTRACT(EPOCH FROM current_timestamp) as now"
now
------------------
1029298563.74248
(1 row)
Are the Docs out of date? How can I get EXTRACT to only return seconds?
Jc
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
[ the docs state ]
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320
Sure, that is an exact-second timestamp; it's not going to have any
fractional-second component.
But if I seem to get milliseconds too in my queries:
$ psql sessions -c "SELECT EXTRACT(EPOCH FROM current_timestamp) as now"
now
------------------
1029298563.74248
(1 row)
Are the Docs out of date? How can I get EXTRACT to only return seconds?
We return fractional seconds in current_timestamp since 7.2. You can
use current_timestamp(0) to round the result to seconds, or just apply
round() to EXTRACT's result.
regards, tom lane