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?
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
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.