How the UNIX_TIMESTAMP() function works in Mariadb?

The UNIX_TIMESTAMP() function in MariaDB is used to retrieve the current Unix timestamp or convert a date and time value into a Unix timestamp. A Unix timestamp is a numeric value representing the number of seconds that have elapsed since the Unix epoch, which is January 1, 1970, 00:00:00 UTC.

Syntax

The syntax for the MariaDB UNIX_TIMESTAMP() function is as follows:

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

• If called without an argument, UNIX_TIMESTAMP() returns the current Unix timestamp.
• If a date argument is provided, it returns the Unix timestamp for the given date and time value.

The date argument can be a DATE, DATETIME, or TIMESTAMP value, or a string in a format that can be parsed as a date and time value.

The function returns an unsigned integer representing the Unix timestamp.

Examples

Example 1: Getting the Current Unix Timestamp

This example demonstrates how to use the UNIX_TIMESTAMP() function without any arguments to retrieve the current Unix timestamp.

SELECT UNIX_TIMESTAMP();

Output:

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1710056972 |
+------------------+

The UNIX_TIMESTAMP() function returns the current Unix timestamp, which represents the number of seconds elapsed since January 1, 1970, 00:00:00 UTC.

Example 2: Converting a Date to a Unix Timestamp

This example shows how to convert a date and time value to a Unix timestamp using the UNIX_TIMESTAMP() function.

SELECT UNIX_TIMESTAMP('2023-05-01 12:30:00') AS timestamp;

Output:

+------------+
| timestamp  |
+------------+
| 1682915400 |
+------------+

The UNIX_TIMESTAMP() function converted the date and time value ‘2023-05-01 12:30:00’ to the corresponding Unix timestamp.

Example 3: Converting a DateTime Column to Unix Timestamps

This example demonstrates how to use the UNIX_TIMESTAMP() function to convert values from a DATETIME column in a table to Unix timestamps.

DROP TABLE IF EXISTS example;
CREATE TABLE example (created_at DATETIME);
INSERT INTO example VALUES ('2022-01-01 08:00:00'), ('2022-02-15 10:30:00'), ('2022-12-31 23:59:59');

SELECT created_at, UNIX_TIMESTAMP(created_at) AS timestamp FROM example;

Output:

+---------------------+------------+
| created_at          | timestamp  |
+---------------------+------------+
| 2022-01-01 08:00:00 | 1640995200 |
| 2022-02-15 10:30:00 | 1644892200 |
| 2022-12-31 23:59:59 | 1672502399 |
+---------------------+------------+

The UNIX_TIMESTAMP() function converted the values in the created_at column, which are DATETIME values, to their corresponding Unix timestamps.

Example 4: Handling Invalid Date and Time Values

This example shows the behavior of the UNIX_TIMESTAMP() function when provided with an invalid date and time value.

SELECT UNIX_TIMESTAMP('Invalid Date') AS timestamp;

Output:

+-----------+
| timestamp |
+-----------+
|      NULL |
+-----------+

When an invalid date and time value is provided as input to the UNIX_TIMESTAMP() function, it returns NULL because the input cannot be parsed as a valid date and time value.

Example 5: Converting Unix Timestamps to Date and Time Values

This example demonstrates how to use the FROM_UNIXTIME() function in combination with UNIX_TIMESTAMP() to convert Unix timestamps back to date and time values.

SELECT UNIX_TIMESTAMP('2023-06-01 00:00:00') AS timestamp,
       FROM_UNIXTIME(UNIX_TIMESTAMP('2023-06-01 00:00:00')) AS date_time;

Output:

+------------+---------------------+
| timestamp  | date_time           |
+------------+---------------------+
| 1685548800 | 2023-06-01 00:00:00 |
+------------+---------------------+

In this example, the UNIX_TIMESTAMP() function converts the date and time value to a Unix timestamp, and then the FROM_UNIXTIME() function converts the Unix timestamp back to a date and time value.

Related Functions

The following are a few functions related to the MariaDB UNIX_TIMESTAMP() function:

• MariaDB FROM_UNIXTIME() function is used to convert a Unix timestamp to a date and time value.
• MariaDB TIMESTAMP() function is used to retrieve the current date and time as a TIMESTAMP value.
• MariaDB NOW() function is used to retrieve the current date and time as a DATETIME value.

Conclusion

The UNIX_TIMESTAMP() function in MariaDB is a valuable tool for working with Unix timestamps, which are widely used for representing dates and times in various systems and applications. By understanding the syntax and usage examples, you can effectively incorporate this function into your SQL queries and data manipulation tasks. Whether you need to retrieve the current Unix timestamp, convert dates and times to Unix timestamps, or handle Unix timestamp conversions in your database, the UNIX_TIMESTAMP() function provides a convenient solution for managing date and time data in a standardized format.