CODEWITHSUNDEEP
sql
George Hernando
George Hernando

Reputation: 2630

Convert milliseconds to Date/Time

I'm trying to generate a report in PostgreSQL. It's working -- except one column stored in the database represents a date/time value, but it is stored as an integer representing the number of milliseconds since 1970.

Needless to say, the millisecond value has not much meaning to people reading the report. Is there a way in SQL to convert milliseconds into some sort of readable date/time value? I'm not easily able to have the report generated in some other program or language. I'd like the report to be run as a SQL script.

Upvotes: 0

Views: 1300

Answers (1)

pilcrow
pilcrow

Reputation: 58534

This recipe is from the docs:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + (millisecond_column/1000) * INTERVAL '1 second'
  FROM mytable;

Recent versions of PostgreSQL provide a to_timestamp function to achieve the same.

Upvotes: 1

Related Questions