FROM_UNIXTIME
Syntax
FROM_UNIXTIME(unix_timestamp) FROM_UNIXTIME(unix_timestamp,format)
Description
Converts the number of seconds from the epoch (1970-01-01 00:00:00 UTC) to a
TIMESTAMP value, the opposite of what UNIX_TIMESTAMP() is doing. Returns NULL if the result would be outside of the valid range of TIMESTAMP values.
If format is given, the result is exactly equivalent to
DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)
MariaDB until 11.7
Before MariaDB 11.7, the one-argument form of FROM_UNIXTIME() was returning a
DATETIME. Meaning, it could return values outside of valid TIMESTAMP range,
in particular 1970-01-01 00:00:00. And it could return the same result for different values of unix_timestamp (around DST changes).
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to 2106-02-07 06:28:15. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use DATETIME as a storage type if you require dates beyond this.
MariaDB until 11.5
Before MariaDB 11.5, the maximum value was 2147483647, equivalent to 2038-01-19 05:14:07.
The options that can be used by FROM_UNIXTIME(), as well as DATE_FORMAT() and STR_TO_DATE(), are:
| Option | Description |
|---|---|
%a | Short weekday name in current locale (Variable lc_time_names). |
%b | Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec. |
%c | Month with 1 or 2 digits. |
%D | Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...). |
%d | Day with 2 digits. |
%e | Day with 1 or 2 digits. |
%f | Microseconds 6 digits. |
%H | Hour with 2 digits between 00-23. |
%h | Hour with 2 digits between 01-12. |
%I | Hour with 2 digits between 01-12. |
%i | Minute with 2 digits. |
%j | Day of the year (001-366) |
%k | Hour with 1 digits between 0-23. |
%l | Hour with 1 digits between 1-12. |
%M | Full month name in current locale (Variable lc_time_names). |
%m | Month with 2 digits. |
%p | AM/PM according to current locale (Variable lc_time_names). |
%r | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'. |
%S | Seconds with 2 digits. |
%s | Seconds with 2 digits. |
%T | Time in 24 hour format. Short for '%H:%i:%S'. |
%U | Week number (00-53), when first day of the week is Sunday. |
%u | Week number (00-53), when first day of the week is Monday. |
%V | Week number (01-53), when first day of the week is Sunday. Used with %X. |
%v | Week number (01-53), when first day of the week is Monday. Used with %x. |
%W | Full weekday name in current locale (Variable lc_time_names). |
%w | Day of the week. 0 = Sunday, 6 = Saturday. |
%X | Year with 4 digits when first day of the week is Sunday. Used with %V. |
%x | Year with 4 digits when first day of the week is Sunday. Used with %v. |
%Y | Year with 4 digits. |
%y | Year with 2 digits. |
%# | For str_to_date(), skip all numbers. |
%. | For str_to_date(), skip all punctation characters. |
%@ | For str_to_date(), skip all alpha characters. |
%% | A literal % character. |
Performance Considerations
If your session time zone is set to SYSTEM (the default), FROM_UNIXTIME() will call the OS function to convert the data using the system time zone. At least on Linux, the corresponding function (localtime_r) uses a global mutex inside glibc that can cause contention under high concurrent load.
Set your time zone to a named time zone to avoid this issue. See mysql time zone tables for details on how to do this.
Examples
SELECT FROM_UNIXTIME(1196440219); +---------------------------+ | FROM_UNIXTIME(1196440219) | +---------------------------+ | 2007-11-30 11:30:19 | +---------------------------+ SELECT FROM_UNIXTIME(1196440219) + 0; +-------------------------------+ | FROM_UNIXTIME(1196440219) + 0 | +-------------------------------+ | 20071130113019.000000 | +-------------------------------+ SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); +---------------------------------------------------------+ | FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') | +---------------------------------------------------------+ | 2010 27th March 01:03:47 2010 | +---------------------------------------------------------+
