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?
Add a comment
|
2 Answers
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.
2 Comments
Wiebke
This suggesstion does not help if you need a millisecond-precision
Konrad Garus
@Wiebke It does. See if the updated answer explains it better.
If you are only interested in the seconds, use the trunc function around it:
select trunc(extract(epoch from now() )* 1000);
