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

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.