How to format bigint field into a date in Postgresql?
I have a table with a field of type bigint. This field store a timestamp. I want to date format the field like this :
to_char( bigint_field,'DD/MM/YYYY HH24:MI:SS')
I get the following error :
ERROR: multiple decimal points État SQL :42601
Solution 1:
TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS')
Solution 2:
This is what worked for me
to_timestamp( bigint_field/1000)::date
Solution 3:
This depends on what the bigint value represents - offset of epoch time, or not.
select to_timestamp(20120822193532::text, 'YYYYMMDDHH24MISS')
returns
"2012-08-22 19:35:32+00"