FROM_UNIXTIME
Syntax
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
Description
Returns a representation of the unix_timestamp argument as a value 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. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.
If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.
Timestamps in MariaDB have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. 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.
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.
MariaDB starting with 11.7
From MariaDB 11.7, FROM_UNIXTIME(0) now returns NULL instead of '1970-01-01 00:00:00' (assuming time_zone='+00:00'). See MDEV-15751.
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 | +---------------------------------------------------------+
