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

Question

I am trying to save my value in a timestamp with a timezone field. This is in milliseconds since 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 1

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 save milliseconds, call with double precision :

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

Answer 2

This is how I convert ms to timestamp and save ms instead of seconds. The accepted answer will go down ms.

 WITH ts AS (SELECT 1401432881230 AS ts) SELECT to_timestamp(ts / 1000) + ((ts % 1000 ) || ' milliseconds') :: INTERVAL FROM ts; -- FOR ALTER COLUMN ALTER TABLE my_info ALTER COLUMN tstmp TYPE TIMESTAMP USING to_timestamp(tstmp / 1000) + ((tstmp % 1000) || ' milliseconds') :: INTERVAL; 

Answer 3

OK I understood. My INSERT should look like this:

INSERT INTO events (timestamp) VALUES (to_timestamp(TO_CHAR(TO_TIMESTAMP(1401432881222 / 1000), 'YYYY-MM-DD HH24:MI:SS') || '.' || (1401432881222%1000), 'YYYY-MM-DD HH24:MI:SS.MS'))

I convert bigint-timestamp with milliseconds to text with the required format ('YYYY-MM-DD HH24: MI: SS.MS') and pass it to to_timestamp function.