Working with Unix timestamps in MySQL and Postgres

Nov 17, 2020

The following are examples of how to work with unix timestamps in both MySQL/MariaDB and Postgres.

MySQL/MariaDB

To get the current date and time in MySQL (based on the timezone of the server):

select unix_timestamp(now());

If you just want the date:

select unix_timestamp(curdate());

To convert a unix timestamp to a readable date/time format use:

select from_unixtime(<unix timestamp);

To get both the current date and time in unix timestamp format to the nearest second (trunc) based on the time zone of the server:

select trunc(extract(epoch from now()));

Conversely to convert a unix timestamp to a Postgres timestamp use:

select to_timestamp(<unix timestamp>);