How FROM_UNIXTIME() Works in MariaDB

In MariaDB, FROM_UNIXTIME() is a built-in date and time function that returns a datetime value based on a given unix timestamp.

You pass the unix timestamp to the function when you call it.

The result is returned in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

The value is expressed in the current time zone.

Syntax

The function can be used in the following ways:

FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)

Where unix_timestamp is the unix timestamp, and format is an optional format string to format the result.

Example

Here’s an example:

SELECT FROM_UNIXTIME(1721428321);

Result:

+---------------------------+
| FROM_UNIXTIME(1721428321) |
+---------------------------+
| 2024-07-20 08:32:01       |
+---------------------------+

Microseconds

Here’s an example that includes microseconds:

SELECT FROM_UNIXTIME(1721428321.123456);

Result:

+----------------------------------+
| FROM_UNIXTIME(1721428321.123456) |
+----------------------------------+
| 2024-07-20 08:32:01.123456       |
+----------------------------------+

Numeric Context

When using FROM_UNIXTIME() in a numeric context, the result is returned in YYYYMMDDHHMMSS.uuuuuu format:

SELECT FROM_UNIXTIME(1721428321.123456) + 0;

Result:

+--------------------------------------+
| FROM_UNIXTIME(1721428321.123456) + 0 |
+--------------------------------------+
|                20240720083201.123456 |
+--------------------------------------+

Format the Result

Here’s an example of passing a format string to format the result:

SELECT FROM_UNIXTIME(1721428321, '%W, %D %M %Y');

Result:

+-------------------------------------------+
| FROM_UNIXTIME(1721428321, '%W, %D %M %Y') |
+-------------------------------------------+
| Saturday, 20th July 2024                  |
+-------------------------------------------+

See MariaDB Date Format Strings for a list of format strings that can be used with the FROM_UNIXTIME() function.

Timestamp Limit

Timestamps in MariaDB have a maximum value of 2147483647. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this results in null being returned.

Here’s an example that demonstrates this limitation:

SELECT 
    FROM_UNIXTIME(2147483647),
    FROM_UNIXTIME(2147483648);

Result:

+---------------------------+---------------------------+
| FROM_UNIXTIME(2147483647) | FROM_UNIXTIME(2147483648) |
+---------------------------+---------------------------+
| 2038-01-19 13:14:07       | NULL                      |
+---------------------------+---------------------------+

Timezone

The result of FROM_UNIXTIME() is expressed in the current time zone.

The following examples use the same unix timestamp with different timezones:

SET time_zone = 'America/New_York';
SELECT FROM_UNIXTIME(2147483647);

Result:

+---------------------------+
| FROM_UNIXTIME(2147483647) |
+---------------------------+
| 2038-01-18 22:14:07       |
+---------------------------+

Switch to another timezone and run it again:

SET time_zone = 'Africa/Abidjan';
SELECT FROM_UNIXTIME(2147483647);

Result:

+---------------------------+
| FROM_UNIXTIME(2147483647) |
+---------------------------+
| 2038-01-19 03:14:07       |
+---------------------------+

Current Unix Timestamp

Here’s an example that uses the UNIX_TIMESTAMP() function to return the current unix timestamp:

SELECT
    UNIX_TIMESTAMP(),
    FROM_UNIXTIME(UNIX_TIMESTAMP());

Result:

+------------------+---------------------------------+
| UNIX_TIMESTAMP() | FROM_UNIXTIME(UNIX_TIMESTAMP()) |
+------------------+---------------------------------+
|       1621734047 | 2021-05-23 11:40:47             |
+------------------+---------------------------------+

Missing Argument

Calling FROM_UNIXTIME() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT FROM_UNIXTIME();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FROM_UNIXTIME'

And another example:

SELECT FROM_UNIXTIME( 1, 2, 3 );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FROM_UNIXTIME'