How to write bigint (timestamp in milliseconds) value as timestamp in postgresql

postgresql timestamp unix-timestamp milliseconds bigint
Clyde picture Clyde · May 30, 2014 · Viewed 25.3k times · Source

I'm trying to store in timestamp with timezone field my value. It is in milliseconds from 1970.

select TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')

Expected 30/5/2014 11:29:42 10:54:41.230, but get 22/08/46379 23:27:02.000

Answer

pozs picture pozs · May 30, 2014

Unix timestamps measures time with seconds, and not milliseconds (almost everywhere, in PostgreSQL too).

Therefore you need to call

SELECT TO_TIMESTAMP(1401432881230 / 1000);

If you want to preserve milliseconds, call with double precision:

SELECT TO_TIMESTAMP(1401432881230::double precision / 1000);

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
Difference between timestamps with/without time zone in PostgreSQL

Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONE versus WITHOUT TIME ZONE? Can the differences be illustrated with simple test cases?

Read More
Using current time in UTC as default value in PostgreSQL

I have a column of the TIMESTAMP WITHOUT TIME ZONE type and would like to have that default to the current time in UTC. Getting the current time in UTC is easy: postgres=# select now() at time zone 'utc'; timezone …

Read More