The FROM_UNIXTIME SQL Function in MySQL and MariaDB - Format Unix timestamp |
|
| | FROM_UNIXTIME | Syntax: | FROM_UNIXTIME(Unix timestamp [, Format]) | | Return value: | DATETIME | | Function type: | Date and Time function | |
| | The FROM_UNIXTIME() function returns the argument "Unix timestamp" as a value in the format YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS.uuuuuu.
The type of return value depends on whether the function is used in a string or numeric context.
The return value is returned expressed in the current time zone.
The value of "Unix timestamp" is an internal timestamp value as produced by the UNIX_TIMESTAMP() function.
It should be noted that the function calculates with a UTC date and the result varies depending on the time zone. See example.
If the value of "Unix timestamp" is invalid or NULL, or "Format" is NULL, the FROM_UNIXTIME() function returns NULL.
If the argument "Format" is given, the result will be formatted according to the format string.
Options for the "Format" string:
| 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. |
| SQL Examples for the FROM_UNIXTIME function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
| SELECT from_unixtime(unix_timestamp());
SELECT from_unixtime(unix_timestamp() + 1);
SELECT from_unixtime(unix_timestamp()) + 1;
SELECT from_unixtime(unix_timestamp()) + 0.001;
SELECT from_unixtime(1670799600);
SELECT from_unixtime(1670799600) + 1;
SELECT from_unixtime(1670799600) + 0.001;
SET SESSION time_zone = '+1:00';
SELECT '+1:00' AS time_zone;
SELECT from_unixtime(0);
SET SESSION time_zone = '+0:00';
SELECT '+0:00' AS time_zone;
SELECT from_unixtime(0);
SELECT from_unixtime(unix_timestamp(), '%Y-%m-%d');
SELECT from_unixtime(unix_timestamp(), '%y-%M-%d');
SELECT from_unixtime(unix_timestamp(), '%y-%M-%d %h:%m:%s');
SELECT from_unixtime(unix_timestamp(), 'Date: %Y_%m_%d, Time: %h.%m.%s');
SELECT from_unixtime(111111111111111111111);
SELECT from_unixtime(null);
SELECT from_unixtime(unix_timestamp(), null);
|
|
| from_unixtime(unix_timestamp()) |
|---|
| datetime(19) |
|---|
| 2023-03-28 16:03:31 |
|
|
| from_unixtime(unix_timestamp() + 1) |
|---|
| datetime(19) |
|---|
| 2023-03-28 16:03:32 |
|
|
| from_unixtime(unix_timestamp()) + 1 |
|---|
| bigint(16) |
|---|
| 20230328160332 |
|
|
| from_unixtime(unix_timestamp()) + 0.001 |
|---|
| decimal(20) |
|---|
| 20230328160331.001 |
|
|
| from_unixtime(1670799600) |
|---|
| datetime(19) |
|---|
| 2022-12-12 00:00:00 |
|
|
| from_unixtime(1670799600) + 1 |
|---|
| bigint(16) |
|---|
| 20221212000001 |
|
|
| from_unixtime(1670799600) + 0.001 |
|---|
| decimal(20) |
|---|
| 20221212000000.001 |
|
|
| time_zone |
|---|
| varchar(5) BINARY |
|---|
| +1:00 |
|
|
| from_unixtime(0) |
|---|
| datetime(19) |
|---|
| 1970-01-01 01:00:00 |
|
|
| time_zone |
|---|
| varchar(5) BINARY |
|---|
| +0:00 |
|
|
| from_unixtime(0) |
|---|
| datetime(19) |
|---|
| 1970-01-01 00:00:00 |
|
|
| from_unixtime(unix_timestamp(), '%Y-%m-%d') |
|---|
| varchar(10) BINARY |
|---|
| 2023-03-28 |
|
|
| from_unixtime(unix_timestamp(), '%y-%M-%d') |
|---|
| varchar(70) BINARY |
|---|
| 23-March-28 |
|
|
| from_unixtime(unix_timestamp(), '%y-%M-%d %h:%m:%s') |
|---|
| varchar(79) BINARY |
|---|
| 23-March-28 02:03:31 |
|
|
| from_unixtime(unix_timestamp(), 'Date: %Y_%m_%d, Time: %h.%m.%s') |
|---|
| varchar(32) BINARY |
|---|
| Date: 2023_03_28, Time: 02.03.31 |
|
|
| from_unixtime(111111111111111111111) |
|---|
| datetime(19) |
|---|
| NULL |
|
|
| from_unixtime(null) |
|---|
| datetime(19) |
|---|
| NULL |
|
|
| from_unixtime(unix_timestamp(), null) |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the FROM_UNIXTIME() function in MySQL and MariaDB databases | | In MySQL and MariaDB the FROM_UNIXTIME() function is used to convert a Unix timestamp represented as Number of seconds since January 1, 1970, to an equivalent date and time. The function returns the date and time in the format "YYYY-MM-DD HH:MM:SS" and can be used to convert a Unix timestamp into a human-readable date and time format, to combine with other date and time functions, and to perform various calculations or manipulations. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | STR_TO_DATE | STR_TO_DATE(String, Format) | | More about STR_TO_DATE Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMEDIFF | TIMEDIFF(Date 1, Date 2) | | More about TIMEDIFF Function |
| | TIMESTAMP | TIMESTAMP(Date [, Addition]) | | More about TIMESTAMP Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | EXTRACT | EXTRACT(Unit FROM Date) | | More about EXTRACT Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
| | TIME_FORMAT | TIME_FORMAT(Time, Format) | | More about TIME_FORMAT Function |
|
|
| | | More information about the FROM_UNIXTIME SQL function: and |
|
|
|
|
