​​How to Convert Epoch Timestamps (UNIX) to a Human-Readable Date

If you are anything like me, you have probably wondered if there is a way to convert Epoch Timestamps without ripping your hair out. In this article, we will take a look at converting Epoch timestamps, without creating a headache for your developers.

I was recently debugging a cron job on a Drupal website that Envano maintains. Since cron jobs run in the background at certain times of the day, it was difficult to see what the issue was that was causing it to break. Fortunately, Drupal offers its own internal logging system which stores logs in a database table called ‘watchdog’.

Below is an example of how to log information to Drupal’s watchdog table:

\Drupal::logger('product_import')->notice("Product Import Ran");

This is helpful, but one problem is when running MySQL queries on this table the date and time are returned as a UNIX epoch timestamp, i.e. 1639087126. 

For background, the “[UNIX epoch] is a system for describing a point in time. It is the number of seconds that have elapsed since the Unix epoch, excluding leap seconds. The Unix epoch is 00:00:00 UTC on 1 January 1970 (an arbitrary date).” (Wikipedia)

My example above of 1639087126 is the date and time in seconds since January 1st, 1970, which is December 9th, 2021 at 9:58:46 PM. There are websites such as epochconverter.com where the epoch time can be entered and it will return the date formatted in a human readable manner. However, this process is tedious, especially if there are a large number of dates to convert.

MySQL’s FROM_UNIXTIME() Function

After doing some research, I found that MySQL has a function that can be added to a SQL query to automatically convert UNIX epoch timestamps on the fly when running a query. The FROM_UNIXTIME function can be used by passing in the column name that is stored as a timestamp and a date format like below:

SELECT *, FROM_UNIXTIME(timestamp, '%h:%i:%s - %m/%d/%y') as nice_date FROM watchdog order by timestamp desc;

Other date formats can be found in the MySQL documentation.

Setting the Timezone of a Date in MySQL

One additional problem I had was the dates returned did not use my preferred time zone. Fortunately, MySQL offers a way to temporarily set the time zone when running a query using the following:

SET time_zone = '-06:00';

Combined with the original query the full SQL statement looks like this now:

SET time_zone = '-06:00';

SELECT *, FROM_UNIXTIME(timestamp, '%h:%i:%s - %m/%d/%y') as nice_date FROM watchdog order by timestamp desc;

Need additional development help with your website? Fill out our contact form and let’s chat!