Learn the MySQL formatted date function 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 ') Copy Code Results similar to: Dec 11:45 PM 12-29-2008 2008 16:25:46 2. MySQL database in date and time function From_unixtime (), Unix_time () ... Example: 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 ' Copy Code 1, From_unixtime (unix_timestamp) Parameters: usually a 10-digit number, such as: 1344887103 return value: There are two, may be similar to the ' yyyy-mm-dd HH:MM:SS ' such a string, it may be similar to the Yyyymmddhhmmss.uuuuuu such a number, specifically returns what depends on the form that the function is called. Mysql> Select From_unixtime (1344887103); +---------------------------+ | From_unixtime (1344887103) | +---------------------------+ | 2012-08-14 03:45:03 | +---------------------------+ 1 row in Set (0.00 sec) Copy Code 2. From_unixtime (unix_timestamp, format) parameter unix_timestamp: Same as the parameter in method From_unixtime (unix_timestamp); parameter format: after conversion The format of the time string display; return value: The 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 ') | +-----------------------------------------------+ | 2012-08-14th 03:45:03 | +-----------------------------------------------+ 1 row in Set (0.00 sec) Copy Code 1, Unix_timestamp () return value: The UNIX format number string for the current time, or the Unix timestamp (the number of seconds from UTC time ' 1970-01-01 00:00:00 '), usually 10 bits, such as 1344887103. Mysql> select Unix_timestamp (); +------------------+ | Unix_timestamp () | +------------------+ | 1344887103 | +------------------+ 1 row in Set (0.00 sec) Copy Code 2, Unix_timestamp (date) parameter: Date may be a date string, a DATETIME string, a timestape string, or a number string similar to YYMMDD or YYYYMMDD. Returns: The number of seconds between the start of UTC time ' 1970-01-01 00:00:00 ' to this parameter. The server interprets the parameter date as a value in the current time zone and converts it to the 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 returns the value of the internal timestamp directly, and if you pass a time out of range to Unix_timestamp (), its return value is zero. Mysql> SELECT Unix_timestamp (); +------------------+ | Unix_timestamp () | +------------------+ | 1344888895 | +------------------+ 1 row in Set (0.00 sec) mysql> SELECT unix_timestamp (' 2012-08-14 16:19:23 '); +---------------------------------------+ | Unix_timestamp (' 2012-08-14 16:19:23 ') | +---------------------------------------+ |1344932363 | +---------------------------------------+ 1 row in Set (0.00 sec) Copy Code Note: If you use Unix_timestamp () and From_unixtime () to convert the value of the TIMESTAMP value to the UNIX timestamp, the precision is lost because the mapping is not one by one corresponding in two directions. For example, due to changes in the local time zone, it is possible that two unix_timestamp () maps to the same UNIX timestamp value. From_unixtime () only maps to the value of the original timestamp. Here's an example of using TIMESTAMP in the CET time zone: mysql> SELECT unix_timestamp (' 2005-03-27 03:00:00 '); +---------------------------------------+ | Unix_timestamp (' 2005-03-27 03:00:00 ') | +---------------------------------------+ |1111885200 | +---------------------------------------+ mysql> SELECT unix_timestamp (' 2005-03-27 02:00:00 '); +---------------------------------------+ | Unix_timestamp (' 2005-03-27 02:00:00 ') | +---------------------------------------+ |1111885200 | +---------------------------------------+ Mysql> SELECT from_unixtime (1111885200); +---------------------------+ | From_unixtime (1111885200) | +---------------------------+ | 2005-03-27 03:00:00 | +---------------------------+ Copy Code Reference Link: Https://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp

