Working with timestamps in databases

Every database has functions to convert a Unix timestamp to a date and back.

• MySQL — timestamp→date FROM_UNIXTIME(ts), date→timestamp UNIX_TIMESTAMP(dt)
• PostgreSQL — to_timestamp(ts), extract(epoch from dt)
• SQLite — datetime(ts, 'unixepoch')
• MongoDB — new Date(ts * 1000) (drivers use milliseconds)

Choosing how to store it: an integer epoch is timezone-independent and simple to sort and compute on, which suits logs and events. A TIMESTAMP or DATETIME type, on the other hand, is human-readable and lets you use date functions directly. Most teams store an integer in UTC and convert to local time only at display time.