MySQL UNIX_TIMESTAMP() Function
Overview
The MySQL UNIX_TIMESTAMP() function is an inbuilt MySQL function that accepts only one argument which is either of a DATE or DATETIME data type and returns an unsigned integer which is the number of seconds passed since 1970-01-01 00:00:00 UTC.
If the UNIX_TIMESTAMP() function is called without any parameter, it will simply return the number of seconds elapsed from 1970-01-01 00:00:00 UTC till now.
Syntax of MySQL UNIX_TIMESTAMP() Function
The syntax for the MySQL UNIX_TIMESTAMP() function is as follows:
Syntax
We can pass the DATE or DATETIME data types as the parameter of the function.
Parameters of MySQL UNIX_TIMESTAMP() Function
We can pass any one of the below-mentioned data types as a parameter to the MySQL UNIX_TIMESTAMP() function:
- DATE
- DATETIME or TIMESTAMP
The parameter of the UNIX_TIMESTAMP() is optional, and if we don't pass any argument to the function, it will return the time elapsed from 1970-01-01 00:00:00 UTC till now.
UTC :- UTC, also known as the Coordinated Universal Time, is the primary time standard used worldwide to regulate clocks and time. This time standard is approximately one second ahead of mean solar time at the Prime Meridian (0° longitude) and does not take into account daylight saving time. Essentially, UTC has taken the place of Greenwich Mean Time and serves as its successor. If not specified that MySQL timestamp function uses the UTC timezone.
Let's have a closer look at each one of them.
DATE
The DATE data type is used to store a date only. The format of the DATE data type in MySQL is YYYY-MM-DD.
The DATE datatype in MySQL range between: 1000-01-01 to 9999-12-31
Example: 2003-11-14 or 1993-04-12
DATETIME or TIMESTAMP
The DATETIME or TIMESTAMP data types are used to store a date along with a time instance. The format of the data types in MySQL is YYYY-MM-DD hh:mm:ss.
The DATETIME or TIMESTAMP data types in MySQL range between: 1000-01-01 00:00:00 to 9999-12-31 23:59:59
Example: 2003-11-14 00:00:00 or 23:59:59 12:45:34
Return Value of the MySQL UNIX_TIMESTAMP() Function
The MySQL UNIX_TIMESTAMP function returns the number of seconds elapsed from 1970-01-01 00:00:00 UTC till the date provided by the user.
If the UNIX_TIMESTAMP function is called without any parameter, it will simply return the number of seconds elapsed from 1970-01-01 00:00:00 UTC till now.
Let's walk through some examples to clearly understand the working of the MySQL UNIX_TIMESTAMP() function.
| Time |
|---|
| 1647476516 |
The output of this query will be:
The output of this query will be:
| Time |
|---|
| 1068828600 |
We will discuss some more examples later in the subsequent section of this article.
Exceptions of MySQL UNIX_TIMESTAMP() Function
- If fractional seconds are provided to the MySQL UNIX_TIMESTAMP() function, it calculates the time elapsed from the Unix epoch and adds the fractional part to the return value.
- The UNIX_TIMESTAMP function does not support formats other than YYYY-MM-DD hh:mm:ss. If provided, it will throw an error.
- You can set the timezone manually using the SET time_zone command otherwise, it will use UTC as the default timezone.
- If the provided input parameter is NULL, it will return NULL.
- If the provided input is a string, MySQL attempts to convert it into a date or datetime value using its internal parsing rules. However, the function will return NULL if the string cannot be parsed.
How does the MySQL UNIX_TIMESTAMP() Function Works?
The MySQL UNIX_TIMESTAMP function returns the number of seconds that have elapsed from the 1970-01-01 00:00:00 UTC, also known as the Unix epoch until now (or the date provided).
The function calculates this time difference based on the UTC timezone.
Note that if you want to use other timezones, you must convert them using the CONVERT_TZ() function.
Working of CONVERT_TZ() Function:
Syntax
Here, the following parameter means:
- datetime: This is the DATETIME value that has to be converted.
- from_tz: The timezone from which the datetime has to be converted.
- to_tz: The timezone to which the datetime has to be converted.
Example:
The output of this query will be:
2023-01-26 04:00:00
| Time |
|---|
| 2023-01-26 04:00:00 |
Examples
Let's walk through some examples that will clearly explain the working the MySQL UNIX_TIMESTAMP() functions:
- When no parameter is passed to the functions to the MySQL timestamp function.
| Time |
|---|
| 1647476516 |
- When you've been given a date value and have to find the number of seconds from the given date.
| Time |
|---|
| 956514600 |
- When you've been given a DateTime value and have to find the number of seconds from the given DateTime instance.
| Time |
|---|
| 973944000 |
- When seconds are in fractional form.
| Time |
|---|
| 1673626920.2323 |
Explanation: If the timestamp has a fractional value, then the function returns the number of seconds since the Unix epoch, adding the fractional part to the return value. In the above example, the function calculates the number of seconds from 2023-01-13 21:52:00 and then adds the decimal part (.2323) to it.
These are just a few examples of how we can use the MySQL UNIX_TIMESTAMP() function in MySQL.
Conclusion
- TheUNIX_TIMESTAMP() function, built-in to MySQL, accepts only one argument, which can be either of the date or datetime data types. It then returns an unsigned integer that represents the number of seconds that have passed since 1970-01-01 00:00:00 UTC.
- If no parameter is provided to the MySQL UNIX_TIMESTAMP() function it returns the number of seconds passed till no from the Unix epoch.
- UNIX_TIMESTAMP() function works with both DATE and DATETIME data types.