How to get timestamp as timestamp from postgres?

Question

I want to get data from Postgres. There is a column type timestamp without a time zone. A simple SELECT returns me a formatted date, but I need a timestamp in milliseconds simply. How can I do this in SQL?

Answer 1

select extract(epoch from my_timestamp) 

This returns the time in seconds. However, this is a decimal number with fractions of the second after the decimal point. More on accuracy below.

For millions 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' 

Precision Note

extract returns a double precision number that corresponds to 15 digits in Postgres. This means that around 2016 (as I write this) the actual accuracy is 10 microseconds. Notice how it rounds the last digit:

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

For dates such as 270 years in the future, this data type will only be able to represent accuracy of 100 microseconds. From the point of view of 2016, it seems to be decent accuracy, and I think things can change a little before we reach this year.

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

In any case, it will continue to work fine for millisecond accuracy for a while.