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);
Postgres
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>);