How do I select the date as a readable string from epoch time in milliseconds?
Some like: SELECT *, to_date(time_in_milli_sec) FROM mytable
Add a comment
|
4 Answers
Per PostgreSQL docs:
SELECT *, to_timestamp(time_in_milli_sec / 1000) FROM mytable
2 Comments
Vincent
It should be divided by a float 1000.0, not 1000, otherwise milliseconds will be dropped.
Erwin Brandstetter
Be aware that
to_timestamp() returns timestamptz and depends on the current timezone setting. I added clarification to my answer.SELECT timestamp 'epoch' + time_in_millisec * interval '1 ms'
FROM mytable;
This returns type timestamp [without time zone], while to_timestamp() returns timestamp [without time zone], based on the timezone setting of the current session, which is a significant difference. The manual warns:
Beware that applying
to_timestampto an epoch extracted from adateortimestampvalue could produce a misleading result: the result will effectively assume that the original value had been given in UTC, which might not be the case.
Related:
answered Oct 24, 2011 at 8:13
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
1 Comment
Aiono
This should be the accepted answer because the accepted answer doesn't explicitly say that the result depends on the current
timezone setting.For milliseconds
SELECT timestamp 'epoch' + proyecto.fecha_inicio * interval '1 ms'
from proyecto.proyecto
where proyecto.fecha_inicio is not null
For seconds
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
In the manual : http://www.postgresql.org/docs/current/interactive/functions-datetime.html.
Line: .. "Here is how you can convert an epoch value back to a time stamp"..
Comments
Original question was related to Date data type, but all the answers so far relate to Timestamp data type.
One way to convert milliseconds to Date would be:
SELECT DATE(any_time_field_containing_milliseconds/ 1000) FROM mytable;
This seems to use the timezone defined for database
