How to get timestamp as Unix epoch from postgres?

Question

I want to get data from Postgres. There is a column in type timestamp without timezone. Simple SELECT returns me formatted date but I want timestamp in miliseconds simply. How I can do it in SQL?

Answer 1

select extract(epoch from my_timestamp)

This returns time in seconds. However, it is a decimal number with fractions of the second after the decimal point. Read more about precision below.

For millis, use:

select extract(epoch from my_timestamp)*1000

Test:

select my_date, 
extract(epoch from my_date)*interval'1s'+'epoch'::timestamp at time zone 'GMT'

Note on precision

extract returns a "double precision" number, which fits 15 digits in Postgres. It means that around 2016 (as I write this), the actual precision is 10 microseconds. Note how it rounds off the last digit:

select extract('epoch' from '2016-09-20T11:22:33.456789Z'::timestamp);
    date_part     
------------------
 1474370553.45679
(1 row)

For dates like 270 years in the future, this data type will only be able to represent 100-microsecond precision. Seems like a decent precision from the perspective of 2016, and I guess things might change a bit before we reach that year.

select extract('epoch' from '2290-09-20T11:22:33.456789Z'::timestamp);
    date_part     
------------------
 10120936953.4568
(1 row)

Either way, it will continue to work just fine for millisecond precision for a while.

Answer 2

If you are only interested in the seconds, use the trunc function around it:

select trunc(extract(epoch from now() )* 1000);