How to use the MySQL UNIX_TIMESTAMP() function

In this article, we will learn how to use the MySQL UNIX_TIMESTAMP() function, which returns the number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC) for a given date or datetime value.

Posted on

In this article, we will learn how to use the MySQL UNIX_TIMESTAMP() function, which returns the number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC) for a given date or datetime value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates and Unix timestamps.

Syntax

The syntax of the UNIX_TIMESTAMP() function is as follows:

UNIX_TIMESTAMP(date)

The date parameter can be any valid date or datetime expression, or a string that can be converted to a date or datetime value. If the date parameter is NULL, the function returns NULL. The UNIX_TIMESTAMP() function returns an integer value that represents the number of seconds since the Unix epoch for the given date or datetime value. For example, UNIX_TIMESTAMP('2023-01-15 10:02:34') returns 1673908954, which is the number of seconds since the Unix epoch for the datetime value ‘2023-01-15 10:02:34’.

Examples

Let’s see some examples of how to use the UNIX_TIMESTAMP() function in MySQL.

Example 1: Get the Unix timestamp for a date value

We can use the UNIX_TIMESTAMP() function to get the Unix timestamp for a date value. For example:

SELECT UNIX_TIMESTAMP('2023-01-15') AS result;

This query will return the Unix timestamp for the date value ‘2023-01-15’. The query will return 1673827200, which is the number of seconds since the Unix epoch for the date value ‘2023-01-15’.

Example 2: Get the Unix timestamp for a datetime value

We can use the UNIX_TIMESTAMP() function to get the Unix timestamp for a datetime value. For example:

SELECT UNIX_TIMESTAMP('2023-01-15 10:02:34') AS result;

This query will return the Unix timestamp for the datetime value ‘2023-01-15 10:02:34’. The query will return 1673908954, which is the number of seconds since the Unix epoch for the datetime value ‘2023-01-15 10:02:34’.

Example 3: Get the Unix timestamp for a string value

We can use the UNIX_TIMESTAMP() function to get the Unix timestamp for a string value that can be converted to a date or datetime value. For example:

SELECT UNIX_TIMESTAMP('January 15, 2023 10:02:34') AS result;

This query will return the Unix timestamp for the string value ‘January 15, 2023 10:02:34’ that can be converted to a datetime value. The query will return 1673908954, which is the same as the Unix timestamp for the datetime value ‘2023-01-15 10:02:34’.

Example 4: Get the difference between two date values in seconds

We can use the UNIX_TIMESTAMP() function with the TIMESTAMPDIFF() function, which returns the difference between two timestamp values, in a specified unit, to get the difference between two date values in seconds. For example:

SELECT TIMESTAMPDIFF(SECOND, UNIX_TIMESTAMP('2023-01-15'), UNIX_TIMESTAMP('2023-01-16')) AS result;

This query will return the difference between the two date values ‘2023-01-15’ and ‘2023-01-16’ in seconds. The query will use the UNIX_TIMESTAMP() function to convert the date values to the number of seconds since the Unix epoch, and then use the TIMESTAMPDIFF() function to get the difference between them. The query will return 86400, which is the number of seconds in one day.

Example 5: Get the difference between two datetime values in seconds

We can use the UNIX_TIMESTAMP() function with the TIMESTAMPDIFF() function, which returns the difference between two timestamp values, in a specified unit, to get the difference between two datetime values in seconds. For example:

SELECT TIMESTAMPDIFF(SECOND, UNIX_TIMESTAMP('2023-01-15 10:02:34'), UNIX_TIMESTAMP('2023-01-16 10:02:34')) AS result;

This query will return the difference between the two datetime values ‘2023-01-15 10:02:34’ and ‘2023-01-16 10:02:34’ in seconds. The query will use the UNIX_TIMESTAMP() function to convert the datetime values to the number of seconds since the Unix epoch, and then use the TIMESTAMPDIFF() function to get the difference between them. The query will return 86400, which is the same as the number of seconds in one day.

There are some other functions that are related to the UNIX_TIMESTAMP() function, and can be useful for working with dates and Unix timestamps. Here are some of them:

  • DATE(): This function returns the date part of a date or datetime value, or a string that can be converted to a date value. For example, DATE('2023-01-15 10:02:34') returns ‘2023-01-15’.
  • DATETIME(): This function returns the datetime value of a date or datetime value, or a string that can be converted to a datetime value. For example, DATETIME('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
  • TIMESTAMP(): This function returns the timestamp value of a date, datetime, or timestamp value, or a string that can be converted to a timestamp value. For example, TIMESTAMP('2023-01-15 10:02:34') returns ‘2023-01-15 10:02:34’.
  • TIMESTAMPDIFF(): This function returns the difference between two timestamp values, in a specified unit. For example, TIMESTAMPDIFF(SECOND, '2023-01-15 10:02:34', '2023-01-15 10:03:34') returns 60, which is the number of seconds between the two timestamp values.
  • FROM_UNIXTIME(): This function returns the date or datetime value that corresponds to the number of seconds since the Unix epoch. For example, FROM_UNIXTIME(1673908954) returns ‘2023-01-15 10:02:34’, which is the date or datetime value that corresponds to the number of seconds since the Unix epoch.

Conclusion

In this article, we learned how to use the MySQL UNIX_TIMESTAMP() function, which returns the number of seconds since the Unix epoch for a given date or datetime value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates and Unix timestamps.