Mysql FROM UNIXTIME
From w3cyberlearnings
Contents |
MySQL FROM_UNIXTIME Function
This function returns a representation of the unix_timestamp to a value in the format as 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu depending on the string context or numeric context. When this function uses format for the second argument, it will be used the same format as in DATE_FORMAT() function.
Syntax FROM_UNIXTIME
- X is a unix_timestamp
- format (optional):
- %a - Abbreviated weekday name (Sun..Sat)
- %b - Abbreviated month name (Jan..Dec)
- %c - Month, numeric (0..12)
- %D - Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
- %d - Day of the month, numeric (00..31)
- %e - Day of the month, numeric (0..31)
- %f - Microseconds (000000..999999)
- %H - Hour (00..23)
- %h - Hour (01..12)
- %I - Hour (01..12)
- %i - Minutes, numeric (00..59)
- %j - Day of year (001..366)
- %k - Hour (0..23)
- %l - Hour (1..12)
- %M - Month name (January..December)
- %m - Month, numeric (00..12)
- %p - AM or PM
- %r - Time, 12-hour (hh:mm:ss followed by AM or PM)
- %S - Seconds (00..59)
- %s - Seconds (00..59)
- %T - Time, 24-hour (hh:mm:ss)
- %U - Week (00..53), where Sunday is the first day of the week
- %u - Week (00..53), where Monday is the first day of the week
- %V - Week (01..53), where Sunday is the first day of the week; used with **%X
- %v - Week (01..53), where Monday is the first day of the week; used with **%x
- %W - Weekday name (Sunday..Saturday)
- %w - Day of the week (0=Sunday..6=Saturday)
- %X - Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
- %x - Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
- %Y - Year, numeric, four digits
- %y - Year, numeric (two digits)
- %% - A literal “%” character
- %x - x, for any “x” not listed above
FROM_UNIXTIME(X,format);
Note
Use UNIX_TIMESTAMP() to convert date or datetime to a unix timestamp.
Example 1
mysql> SELECT FROM_UNIXTIME(1896440329); +---------------------------+ | FROM_UNIXTIME(1896440329) | +---------------------------+ | 2030-02-04 07:58:49 | +---------------------------+ 1 row in set (0.00 sec)
Example 2
mysql> SELECT FROM_UNIXTIME(1896440329)+0; +-----------------------------+ | FROM_UNIXTIME(1896440329)+0 | +-----------------------------+ | 20300204075849.000000 | +-----------------------------+ 1 row in set (0.00 sec)
Example 3
mysql> SELECT FROM_UNIXTIME(1196440329,'%Y %D %M %h:%i:%s %p'); +--------------------------------------------------+ | FROM_UNIXTIME(1196440329,'%Y %D %M %h:%i:%s %p') | +--------------------------------------------------+ | 2007 30th November 11:32:09 AM | +--------------------------------------------------+ 1 row in set (0.00 sec)
