Introduction:

Timestamps play a vital role in databases, recording the exact date and time when a particular event occurs. On the other hand, Unix timestamps, also known as epoch time, represent the number of seconds that have passed since January 1, 1970, at 00:00:00 UTC. Unix timestamps are widely used for various calculations and comparisons, making it essential to be able to convert between these two formats. In this blog, we will demonstrate several methods to convert TIMESTAMP to UNIX_TIMESTAMP in MySQL.

Method 1: Using the UNIX_TIMESTAMP() Function

The simplest way to convert a TIMESTAMP to a UNIX_TIMESTAMP in MySQL is by using the built-in UNIX_TIMESTAMP() function. This function directly converts a given TIMESTAMP value to its corresponding Unix timestamp.

SELECT UNIX_TIMESTAMP(your_timestamp_column) AS unix_timestamp_value
FROM your_table;

Output:

+---------------------+
| unix_timestamp_value|
+---------------------+
| 1628425417          |
| 1630123456          |
| 1631856789          |
+---------------------+

Method 2: Using the TIMESTAMPDIFF() Function

If you need more control over the conversion process and want to calculate the time difference between the TIMESTAMP and the Unix epoch, you can use the TIMESTAMPDIFF() function in conjunction with UNIX_TIMESTAMP().

SELECT TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', your_timestamp_column) AS unix_timestamp_value
FROM your_table;

Output:

+---------------------+
| unix_timestamp_value|
+---------------------+
| 1628425417          |
| 1630123456          |
| 1631856789          |
+---------------------+

Method 3: Using the DATE_FORMAT() Function

If you want to retain the original TIMESTAMP column while also having the Unix timestamp as a separate column, you can use the DATE_FORMAT() function.

SELECT your_timestamp_column,
       UNIX_TIMESTAMP(your_timestamp_column) AS unix_timestamp_value
FROM your_table;

Output:

+---------------------+---------------------+
| your_timestamp_column| unix_timestamp_value|
+---------------------+---------------------+
| 2021-08-08 12:34:56 | 1628426096          |
| 2021-08-28 18:45:21 | 1630166721          |
| 2021-09-17 09:30:45 | 1631881845          |
+---------------------+---------------------+

Method 4: Using User-Defined Functions (UDFs)

For more complex scenarios or custom formatting, you can create a User-Defined Function (UDF) to perform the conversion. This approach is particularly useful when you need to apply specific business logic during the conversion.

Here's a simplified example of a UDF for converting TIMESTAMP to UNIX_TIMESTAMP:

DELIMITER //
CREATE FUNCTION ConvertToUnixTimestamp(ts TIMESTAMP) RETURNS BIGINT
BEGIN
    DECLARE unix_timestamp BIGINT;
    SET unix_timestamp = UNIX_TIMESTAMP(ts);
    RETURN unix_timestamp;
END //
DELIMITER ;

SELECT your_timestamp_column,
       ConvertToUnixTimestamp(your_timestamp_column) AS unix_timestamp_value
FROM your_table;

Output:

+---------------------+---------------------+
| your_timestamp_column| unix_timestamp_value|
+---------------------+---------------------+
| 2021-08-08 12:34:56 | 1628426096          |
| 2021-08-28 18:45:21 | 1630166721          |
| 2021-09-17 09:30:45 | 1631881845          |
+---------------------+---------------------+

Conclusion:

Converting TIMESTAMP to UNIX_TIMESTAMP in MySQL is a fundamental operation that allows you to work seamlessly with time-related data. In this blog, we explored multiple methods for performing this conversion, ranging from built-in functions to user-defined functions.