In MySQL databases, the date and time functions UNIX_TIME () and UNIX_TIMESTAMP ()

The date and time functions UNIX_TIME () and UNIX_TIMESTAMP () in the MySQL database write code over the past few days. It is used repeatedly to convert the date and time fields into numbers and date format strings, I feel that I am still in the unknown half solution. Simply take some time to check the relevant information and find out the usage of the two commonly used date functions in MySQL, which is recorded here for reference. 1. FROM_UNIXTIME (unix_timestamp) parameter: Usually a ten-digit number, such as: 1344887103 return value: there are two, may be similar to 'yyyy-MM-DD HH: MM: A string like 'ss 'may also be similar to YYYYMMDDHHMMSS. A number such as uuuuuu. The specific returned result depends on the form of the function being called. 1 www.2cto.com mysql> select FROM_UNIXTIME (1344887103); 2 + ------------------------- + 3 | FROM_UNIXTIME (1344887103) | 4 + ------------------------- + 5 | 03:45:03 | 6 + ------------------------------- + 71 row in set (0.00 sec)
2. FROM_UNIXTIME (unix_timestamp, format) parameter unix_timestamp: Same as the parameter meaning in the method FROM_UNIXTIME (unix_timestamp); parameter format: format of the time string displayed after conversion; return value: string displayed in the specified time format; 01 mysql> select FROM_UNIXTIME (1344887103, '% Y-% M-% D % h: % I: % s '); 02 + ------------------------------------------- + 03 www.2cto.com | FROM_UNIXTIME (1344887103, '% Y-% M-% D % h: % I: % s ') | 04 + Jun + 05 | 2012-August-14th 03:45:03 | 06 + ----------------------------------------------- + 071 row in set (0.00 sec) 08 mysql> select FROM_UNIXTIME (1344887103, '% Y-% m-% D % h: % I: % s'); 09 + ------------------------------------------------- + 10 | FROM_UNIXTIME (1344887103, '% Y-% m-% D % h: % I: % s ') | 11 + ------------------------------------------- + 12 | 2012-08-14th 03:45:03 | 13 + ------------------------------------------------- + 14151 row in set (0.00 sec) Reference link: https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime 1. Return Value of UNIX_TIMESTAMP () www.2cto.com: the UNIX-format numeric string of the current time, or the UNIX timestamp (the number of seconds since UTC time '2017-01-01 00:00:00 ), usually 10, such as 1344887103. 1 mysql> select unix_timestamp (); 2 + ------------------ + 3 | unix_timestamp () | 4 + ------------------ + 5 | 1344887103 | 6 + ------------------ + 71 row in set (0.00 sec) 2. UNIX_TIMESTAMP (date) parameter: date may be a DATE string, DATETIME string, TIMESTAPE string, or a numeric string similar to YYMMDD or YYYYMMDD. Return the number of seconds from UTC time '2017-01-01 00:00:00 'to this parameter. The server interprets the date parameter as a value of the current time zone and converts it to an internal time in UTC format. The client can set the current time zone on its own. When UNIX_TIMESTAMP () is used for a TIMESTAMP column, the function directly returns the internal TIMESTAMP value. If you pass a time out of the range to UNIX_TIMESTAMP (), the return value is zero. 01 www.2cto.com mysql> SELECT UNIX_TIMESTAMP (); 02 + ------------------ + 03 | UNIX_TIMESTAMP () | 04 + ---------------- + 05 | 1344888895 | 06 + rows + 071 row in set (0.00 sec) 08 09 mysql> SELECT UNIX_TIMESTAMP ('2017-08-14 16:19:23 '); 10 + ----------------------------------------- + 11 | UNIX_TIMESTAMP ('2017-08-14 16:19:23 ') | 12 + ------------------------------------- + 13 | 1344932363 | 14 + ------------------- -------------------- + 15 www.2cto.com 1 row in set (0.00 sec) Note: If you use UNIX_TIMESTAMP () and FROM_UNIXTIME () to convert the TIMESTAMP value and Unix TIMESTAMP value, the precision will be lost, because the ing is not one-to-one in two directions. For example, due to changes in the local time zone, two UNIX_TIMESTAMP () values may be mapped to the same Unix timestamp value. FROM_UNIXTIME () is only mapped to the original timestamp value. Here is an example in the CET time zone using TIMESTAMP: 01 mysql> SELECT UNIX_TIMESTAMP ('2017-03-27 03:00:00 '); 02 + ------------------------------------- + 03 | UNIX_TIMESTAMP ('2017-03-27 03:00:00 ') | 04 + ----------------------------------- + 05 | 1111885200 | 06 + ------------------------------------------- + 07 mysql> SELECT UNIX_TIMESTAMP ('2017-03-27 02:00:00 '); 08 + hour + 09 | UNIX_TIMESTAMP ('2017-03-27 02:00:00 ') | 10 + ----------------------------------------- + 11 | 2005 | 12 + hour + 13 mysql> SELECT FROM_UNIXTIME (1111885200 ); 14 www.2cto.com + ------------------------- + 15 | FROM_UNIXTIME (1111885200) | 16 + --------------------------- + 17 | 03:00:00 | 18 + hour + reference link: https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp Author: Bairrfhoinn