Converting a Unix timestamp to a real Postgres date

Let’s say you have a Postgres database with a table that looks like this:

thing\_id unix\_date
1 1253764800
2 1253804507
3 1253764810
4 1253764801

See how unix_date field is a Unix timestamp?

Well what if we want to deal with it as an actual Postgres date type?

Turns out it’s pretty easy with the following SQL:

~~~~ {.sql name="code"} SELECT*, TIMESTAMP 'epoch' + unix_date * INTERVAL '1 second' as real_dateFROM mytable ~~~~