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 | Sub seconds 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, 1 = 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 |
+---------------------------------------------------------+
 


URL: https://mariadb.com/kb/en/from_unixtime/