How to write bigint (timestamp in milliseconds) value as timestamp in postgresql
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);
This is how I convert ms to timestamp and keep ms instead seconds.The accepted answer will drop ms.
WITH ts AS (SELECT 1401432881230 AS ts)SELECT to_timestamp(ts / 1000) + ((ts % 1000 ) || ' milliseconds') :: INTERVALFROM ts;-- FOR ALTER COLUMNALTER TABLE my_info ALTER COLUMN tstmp TYPE TIMESTAMP USING to_timestamp(tstmp / 1000) + ((tstmp % 1000) || ' milliseconds') :: INTERVAL;
Okay, I understood. My INSERT should looks like:
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'm converting bigint-timestamp with milliseconds to text with required format ('YYYY-MM-DD HH24:MI:SS.MS') and pass it to to_timestamp function.