How to format bigint field into a date in Postgresql?

sql postgresql date timestamp bigint
Stephan picture Stephan · Mar 30, 2011 · Viewed 59.3k times · Source

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

Answer

Quassnoi picture Quassnoi · Mar 30, 2011 
TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS')

Related questions

How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Postgresql query between date ranges

I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i've been able to do …

Read More
PostgreSQL: how to convert from Unix epoch to date?

The statement gives me the date and time. How could I modify the statement so that it returns only the date (and not the time)? SELECT to_timestamp( TRUNC( CAST( epoch_ms AS bigint ) / 1000 ) );

Read More