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.