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'