DATE_FORMAT, FROM_UNIXTIME, UNIX_TIME

DATE_FORMAT, FROM_UNIXTIME, UNIX_TIME

DATE_FORMAT (NOW (), '% B % d % Y % h: % I % p ') DATE_FORMAT (NOW (), '% m-% d-% Y ') DATE_FORMAT (NOW (), '% d % B % Y ') DATE_FORMAT (NOW (), '% d % B % Y % T: % f ') Results: Dec 29 2008 16:25:46 PM 12-29-2008 29 Dec 08 29 Dec 2008 2. MySQL database date and time functions FROM_UNIXTIME (), UNIX_TIME ()... instance: date => int (11) SELECT FROM_UNIXTIME (date, '% Y-% c-% d % h: % I: % S') as post_date, Date_format (NOW (), '% Y-% c-% d % h: % I: % S') as post_date_gmt FROM 'article' where outkey = 'y' 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. Mysql> select FROM_UNIXTIME (1344887103 ); + --------------------------- + | FROM_UNIXTIME (1344887103) | + --------------------------- + | 03:45:03 | + --------------------------- + 1 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: A string displayed in the specified time format; Mysql> select FROM_UNIXTIME (1344887103, '% Y-% M-% D % h: % I: % S '); + ----------------------------------------------- + | FROM_UNIXTIME (1344887103, '% Y-% M-% D % h: % I: % S') | + ----------------------------------------------- + | 2012-August-14th 03:45:03 | + ----------------------------------------------- + 1 row in set (0.00 sec) Mysql> select FROM_UNIXTIME (1344887103, '% Y-% m-% D % h: % I: % S '); + ----------------------------------------------- + | FROM_UNIXTIME (1344887103, '% Y-% m-% D % h: % I: % S') | + ----------------------------------------------- + | 03:45:03 | + ----------------------------------------------- + 1 row in set (0.00 sec) 1. return value of UNIX_TIMESTAMP (): 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. Mysql> select unix_timestamp (); + ------------------ + | Unix_timestamp () | + ------------------ + | 1, 1344887103 | + ------------------ + 1 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. Mysql> SELECT UNIX_TIMESTAMP (); + ------------------ + | UNIX_TIMESTAMP () | + ------------------ + | 1, 1344888895 | + ------------------ + 1 row in set (0.00 sec) Mysql> SELECT UNIX_TIMESTAMP ('2017-08-14 16:19:23 '); + --------------------------------------- + | UNIX_TIMESTAMP ('2017-08-14 16:19:23 ') | + --------------------------------------- + | 1, 1344932363 | + --------------------------------------- + 1 row in set (0.00 sec) Note: If you use UNIX_TIMESTAMP () and FROM_UNIXTIME () to convert the TIMESTAMP value to the Unix TIMESTAMP value, the precision will be lost because the ING is not one-to-one in both 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 where TIMESTAMP is used in the CET time zone: Mysql> SELECT UNIX_TIMESTAMP ('2017-03-27 03:00:00 '); + --------------------------------------- + | UNIX_TIMESTAMP ('2017-03-27 03:00:00 ') | + --------------------------------------- + | 1, 1111885200 | + --------------------------------------- + Mysql> SELECT UNIX_TIMESTAMP ('2017-03-27 02:00:00 '); + --------------------------------------- + | UNIX_TIMESTAMP ('2017-03-27 02:00:00 ') | + --------------------------------------- + | 1, 1111885200 | + --------------------------------------- + Mysql> SELECT FROM_UNIXTIME (1111885200 ); + --------------------------- + | FROM_UNIXTIME (1111885200) | + --------------------------- + | 03:00:00 | + --------------------------- + Reference: https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp