Introduction:

Epoch time, also known as Unix time or POSIX time, is a widely used system for representing timestamps as a single integer, representing the number of seconds that have elapsed since January 1, 1970 (UTC). While epoch time is convenient for storage and computation, it's often more intuitive to work with human-readable date and time formats. In this blog, we'll explore various methods to convert epoch time to date and time in MySQL.

Method 1: Using MySQL's FROM_UNIXTIME Function

MySQL provides a built-in function called FROM_UNIXTIME that allows us to convert epoch time directly to a formatted date and time string. The syntax for using this function is as follows:

SELECT FROM_UNIXTIME(epoch_time) AS formatted_datetime;

Where epoch_time is the epoch timestamp you want to convert. This function returns a formatted date and time string, making it easy to work with the converted timestamp.

Output:

The FROM_UNIXTIME function takes the epoch timestamp as input and returns a formatted date and time string in the default format YYYY-MM-DD HH:MI:SS. You can customize the output format using additional formatting options.

Method 2: Using Date and Time Functions

Another approach is to use MySQL's date and time functions to extract individual components (year, month, day, hour, minute, second) from the epoch timestamp and construct a formatted date and time string.

SELECT
    DATE_FORMAT(FROM_UNIXTIME(epoch_time), '%Y-%m-%d %H:%i:%s') AS formatted_datetime;

Output:

In this method, we combine the FROM_UNIXTIME function with the DATE_FORMAT function. The DATE_FORMAT function allows us to specify a custom output format using formatting codes. In the example above, %Y represents the year, %m represents the month, %d represents the day, %H represents the hour, %i represents the minute, and %s represents the second.

Method 3: Using Arithmetic Operations

Epoch time represents the number of seconds since a reference point. We can leverage this property to manually calculate the individual date and time components using arithmetic operations.

SELECT
    DATE_ADD('1970-01-01', INTERVAL epoch_time SECOND) AS formatted_datetime;

Output:

In this method, we use the DATE_ADD function to add the epoch time (in seconds) to the reference date '1970-01-01', effectively converting it to a human-readable date and time format.

Conclusion:

In this blog, we explored three approaches: using MySQL's FROM_UNIXTIME function, utilizing date and time functions, and performing arithmetic operations. This blog covered the essential techniques for converting epoch time to date and time in MySQL. Whether you're dealing with timestamps in a database or building analytical tools, having a solid grasp of these conversion methods can significantly enhance your ability to work with time-based data.