MariaDB FROM_UNIXTIME() Function

In MariaDB, FROM_UNIXTIME() is a built-in function that converts a given unix timestamp to a datetime value.

UNIX timestamp values ​​are seconds from 1970-01-01 00:00:00.

The UNIX_TIMESTAMP() function is the opposite of the FROM_UNIXTIME() function.

MariaDB FROM_UNIXTIME() Syntax

This is the syntax of the MariaDB FROM_UNIXTIME() function:

FROM_UNIXTIME(unix_timestamp[, format])

Parameters

unix_timestamp

Required. Seconds from 1970-01-01 00:00:00.

format

Optional. Used to format date/time values. For more information on formatting accounted datetimes, see the DATE_FORMAT() function.

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'FROM_UNIXTIME'.

Return value

The MariaDB FROM_UNIXTIME() function will unix_timestamp convert to a datetime or format it as a string representing a date/time.

If you provide format parameter, the FROM_UNIXTIME() function will return a string in the format format.

If no format parameter, the FROM_UNIXTIME() function returns a datetime value. If the function is in a string context, the YYYY-MM-DD HH:MM:SS format is used, otherwise the YYYYMMDDHHMMSS.uuuuuu format is used.

If the argument is NULL, the FROM_UNIXTIME() function will return NULL.

MariaDB FROM_UNIXTIME() Examples

Basic example

To convert a UNIX timestamp 1649839394 to UTC time, use the following statement:

SELECT FROM_UNIXTIME(1679839394);

Output:

+---------------------------+
| FROM_UNIXTIME(1679839394) |
+---------------------------+
| 2023-03-26 22:03:14       |
+---------------------------+

fractional seconds

FROM_UNIXTIME() allows you to convert a UNIX timestamp with fractional seconds to a datetime value, as follows:

SELECT FROM_UNIXTIME(1679839394.121311);

Output:

+----------------------------------+
| FROM_UNIXTIME(1679839394.121311) |
+----------------------------------+
| 2023-03-26 22:03:14.121311       |
+----------------------------------+

digital context

If format parameter is not specified for and when used in a numeric context, it returns a number in the YYYYMMDDHHMMSS[.uuuuuu] format:

SELECT FROM_UNIXTIME(1679839394.121311) + 0;

Output:

+--------------------------------------+
| FROM_UNIXTIME(1679839394.121311) + 0 |
+--------------------------------------+
|                20230326220314.121311 |
+--------------------------------------+

Format the result

FROM_UNIXTIME() allows you to specify the format of the output datetime, as follows:

SELECT FROM_UNIXTIME(1679839394, '%Y%m%d %H%i%S');

Output:

+--------------------------------------------+
| FROM_UNIXTIME(1679839394, '%Y%m%d %H%i%S') |
+--------------------------------------------+
| 20230326 220314                            |
+--------------------------------------------+

Current Unix timestamp

To get the timestamp of the current time, use the UNIX_TIMESTAMP() function:

SELECT UNIX_TIMESTAMP();

Output:

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1673316979 |
+------------------+

Conclusion

In MariaDB, FROM_UNIXTIME() is a built-in function that converts a given unix timestamp to a datetime value.