How the FROM_UNIXTIME() function works in Mariadb?

The FROM_UNIXTIME() function is a date and time function that converts a Unix timestamp to a datetime value.

Posted on

The FROM_UNIXTIME() function is a date and time function that converts a Unix timestamp to a datetime value. It can be useful for working with data that is stored or transmitted in the Unix epoch format, which is the number of seconds since January 1, 1970.

Syntax

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

FROM_UNIXTIME(unix_timestamp, [format])

The unix_timestamp parameter is an integer or a decimal that represents the number of seconds since the Unix epoch. The function returns a datetime value in the format of YYYY-MM-DD HH:MM:SS.

The optional format parameter is a string that specifies how to format the datetime value. It can contain any of the date and time format specifiers that are supported by the DATE_FORMAT() function. The function returns a formatted string instead of a datetime value if the format parameter is provided.

Examples

Example 1: Basic usage

The following example shows how to use the FROM_UNIXTIME() function to convert a Unix timestamp to a datetime value.

SELECT FROM_UNIXTIME(1611570000) AS datetime;

The output is:

+---------------------+
| datetime            |
+---------------------+
| 2021-01-25 18:20:00 |
+---------------------+

The function returns the datetime value that corresponds to the Unix timestamp, which is January 25, 2021 at 13:00:00.

Example 2: Decimal unix timestamp

The following example shows how to use the FROM_UNIXTIME() function with a decimal unix timestamp.

SELECT FROM_UNIXTIME(1611570000.123) AS datetime;

The output is:

+-------------------------+
| datetime                |
+-------------------------+
| 2021-01-25 18:20:00.123 |
+-------------------------+

The function returns the datetime value that includes the fractional seconds part of the unix timestamp, which is 0.123.

Example 3: Formatted datetime

The following example shows how to use the FROM_UNIXTIME() function with a format parameter.

SELECT FROM_UNIXTIME(1611570000, '%a, %d %b %Y %T') AS datetime;

The output is:

+---------------------------+
| datetime                  |
+---------------------------+
| Mon, 25 Jan 2021 18:20:00 |
+---------------------------+

The function returns the formatted string that follows the specified format, which is the abbreviated weekday name, the day of the month, the abbreviated month name, the year, and the time in 24-hour format.

Example 4: Invalid unix timestamp

The following example shows how to use the FROM_UNIXTIME() function with an invalid unix timestamp.

SELECT FROM_UNIXTIME('Invalid unix timestamp') AS datetime;

The output is:

+---------------------+
| datetime            |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+

The function returns ‘1970-01-01 08:00:00’ when the unix timestamp is invalid or out of range.

Example 5: Timezone conversion

The following example shows how to use the FROM_UNIXTIME() function to convert a unix timestamp to a different timezone. Suppose we have a table called events that stores the event name and the unix timestamp of the start time. We want to find the events that start at 10:00 AM in New York timezone, which is UTC-5.

SELECT event_name, FROM_UNIXTIME(start_time, '%Y-%m-%d %H:%i:%s') AS start_time
FROM events
WHERE FROM_UNIXTIME(start_time, '%H') = 10 + 5;

The output is:

+------------+---------------------+
| event_name | start_time          |
+------------+---------------------+
| Webinar    | 2021-01-25 15:00:00 |
| Meeting    | 2021-01-26 15:00:00 |
+------------+---------------------+

The query returns the events that start at 10:00 AM in New York timezone, which is equivalent to 15:00 in UTC. The FROM_UNIXTIME() function is used to convert the unix timestamp to a datetime value, and then to extract the hour part. The hour part is compared with 10 plus the timezone offset, which is 5 for UTC-5.

Some of the functions that are related to the FROM_UNIXTIME() function are:

  • UNIX_TIMESTAMP(): This function converts a datetime value to a unix timestamp. It is the inverse of the FROM_UNIXTIME() function. For example, UNIX_TIMESTAMP('2021-01-25 13:00:00') returns 1611570000.
  • TIMESTAMP(): This function converts one or two arguments to a datetime value. It can be used to perform datetime arithmetic without converting the datetime values to unix timestamps. For example, TIMESTAMP('2021-01-25 13:00:00', '1:30:00') returns ‘2021-01-25 14:30:00’.
  • CONVERT_TZ(): This function converts a datetime value from one timezone to another. It can be used to perform timezone conversion without converting the datetime values to unix timestamps. For example, CONVERT_TZ('2021-01-25 13:00:00', '+00:00', '-05:00') returns ‘2021-01-25 08:00:00’.

Conclusion

The FROM_UNIXTIME() function is a useful function for converting a unix timestamp to a datetime value. It can be used for working with data that is stored or transmitted in the unix epoch format. It can also be combined with other date and time functions to achieve various datetime operations.