Unix Timestamps

As you start developing in Moodle you'll quickly find that Unix timestamps are used everywhere in fields like timemodified, timecreated etc.

It can be handy to know how to deal with these at the database level.

MySQL and MariaDB

To convert a standard date to unix timestamp format (note this won't include timezone)

select unix_timestamp('2019-01-01')

If you need the timezone (presuming your database is set to the correct timezone, otherwise you'll need to adjust SYSTEM to the relevant timezone).

select unix_timestamp(convert_tz('2019-01-01', '+00:00', 'SYSTEM'))

To convert a unix timestamp to standard date format (UTC timezone)

select from_unixtime(1546300800)

Remember this doesn't take into account the timezone and will return the timezone as UTC, so you might need to add/subtract your timezone offset in seconds. E.g. GMT+11 = 11 hours * 60 minutes in an hour * 60 seconds in a minute = 39,600 seconds.

select from_unixtime(1546300800-(11*60*60)

To get the current date only (no timestamp):

select unix_timestamp(curdate());

To get the current date and time

select unix_timestamp(now());

PostgreSQL

To convert a standard date to unix timestamp format (with timezone):

select extract(epoch from timestamp '2019-01-01')

To convert a unix timestamp to standard date format (with timezone):

select to_timestamp('1546300800');

To get the current date (no timestamp)

select trunc(extract(epoch from current_date));

To get the current date and time (this will include your timezone)

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