You are here

PostgreSQL: "UNIX Time" To Date

In some effort to avoid time-zone drama, or perhaps due to fantasies of efficiency, some developer put a date-time field in a PostgreSQL database as an integer; specifically as a UNIX Time value. ¯\_(ツ)_/¯

How to present this as a normal date in a query result?

date_trunc('day', (TIMESTAMP 'epoch' + (j.last_modified * INTERVAL '1 second'))) AS last_action,

This is the start of the epoch plus the value in seconds - UNIX Time - calculated and cast as a non-localized year-month-day value.

Clarification#1: j is the alias of the table in the statement's FROM.

Clarification#2: last_modified is the field which is an integer time value.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer