Collections:
FROM_UNIXTIME() - Unix Time to Datetime
How to convert a Unix time to a datetime using the FROM_UNIXTIME() function?
✍: FYIcenter.com
FROM_UNIXTIME(unix_timestamp, format) is a MySQL built-in function that converts a Unix time to a datetime in a given format. For example:
SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP()); -- +------------------+---------------------------------+ -- | UNIX_TIMESTAMP() | FROM_UNIXTIME(UNIX_TIMESTAMP()) | -- +------------------+---------------------------------+ -- | 1700017460 | 2023-11-14 22:04:20 | -- +------------------+---------------------------------+ SELECT FROM_UNIXTIME(1700017460.123456), FROM_UNIXTIME(1700017460.123456)+1; -- +----------------------------------+------------------------------------+ -- | FROM_UNIXTIME(1700017460.123456) | FROM_UNIXTIME(1700017460.123456)+1 | -- +----------------------------------+------------------------------------+ -- | 2023-11-14 22:04:20.123456 | 20231114220421.123456 | -- +----------------------------------+------------------------------------+ SELECT FROM_UNIXTIME(1700017460.123456, '%Y %D %M %h:%i:%s %x'); -- +----------------------------------------------------------+ -- | FROM_UNIXTIME(1700017460.123456, '%Y %D %M %h:%i:%s %x') | -- +----------------------------------------------------------+ -- | 2023 14th November 10:04:20 2023 | -- +----------------------------------------------------------+
Reference information of the FROM_UNIXTIME() function:
FROM_UNIXTIME(unix_timestamp, format): val Returns a representation of unix_timestamp as a datetime value or character string value. Arguments, return value and availability: unix_timestamp: Required. The Unix timestamp number. format: Optional. Default is to output in datetime value. The string with format specifiers. int: Return value. Extracted components concatenated as an integer. Available since MySQL 5.7. Datetime format specifiers: Specifier Description --------- ----------- %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 %v Week (01..53), where Monday is the first day of the week %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
Related MySQL functions:
⇒ GET_FORMAT() - Standard Datetime Formats
⇐ FROM_DAYS() - Converting Days to Date
2023-11-17, 251🔥, 0💬
Popular Posts:
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...