Learn mysql date formatting functions DATE_FORMAT, FROM_UNIXTIME, UNIX

Learn mysql date formatting functions DATE_FORMAT, FROM_UNIXTIME, UNIX_TIME

WBOY

Release： 2016-07-25 09:04:22

Original

1438 people have browsed it

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 the code

The result is similar: Dec 29 2008 11:45 PM 12-29-2008 29 Dec 08 29 Dec 2008 16:25:46

2. Date and time functions FROM_UNIXTIME(), UNIX_TIME()... in MySQL database 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 ) Parameter: usually a ten-digit number, such as: 1344887103 Return value: There are two types, it may be a string like 'YYYY-MM-DD HH:MM:SS', or it may be a number like YYYYMMDDHHMMSS.uuuuuu. What is returned depends on the form in which 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: has the same meaning as the parameter in method FROM_UNIXTIME( unix_timestamp); Parameter format: the format for displaying the time string 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') |
+------------- ----------------------------------+
| 2012-08-14th 03:45:03 |
+ --------------------------------------------------+
1 row in set (0.00 sec)

Copy code

1、UNIX_TIMESTAMP() Return value: UNIX format numeric string of the current time, or UNIX timestamp (the number of seconds starting from UTC time '1970-01-01 00:00:00'), usually ten digits, such as 1344887103.

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+-------- ----------+
| 1344887103 |
+------------------+
1 row in set (0.00 sec)

Copy code

2、UNIX_TIMESTAMP(date) Parameters: date may be a DATE string, a DATETIME string, a TIMESTAPE string, or a numeric string similar to YYMMDD or YYYYMMDD. Returns: the number of seconds from UTC time '1970-01-01 00:00:00' to this parameter. The server interprets the date parameter as a value in the current time zone and converts it into an internal time in UTC format. The client can set the current time zone by itself. When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly; if you pass an out-of-range time 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 TIMESTAMP values to Unix timestamp values, precision will be lost because the mapping is not one-to-one in both directions. For example, due to local time zone changes, it is possible that two UNIX_TIMESTAMP() will map to the same Unix timestamp value. FROM_UNIXTIME() will only map to the original timestamp value. Here's an example using TIMESTAMP in the CET time zone: