mysql时间日期_MySql 时间和日期函数

下面是MySql各种内置时间函数：

1. curdate() ,  current_date() 用于获取当前的日期。

2. curtime() ,  current_time() 用于获取现在的时钟时间。

3. now()，current_timestamp()，localtime()，sysdate() 四类函数可以获取当前的日期和时钟时间

4. datediff()用于获取两个日期相隔的天数：datediff('2015.08.20','2016.02.03')   /* 得到的结果是有符号的。*/

5. adddate('n',d): 求得日期是n经过d天后的新日期。

6. adddate('n',interval 'a' type)  函数返回日期n后经过时间段a后的新时间。

7. subdate ('n',d) 日期n前d天。

8. unix_timestamp() 返回当前时间的

unix时间戳

unix_timestamp('n')  返回时间是n的

unix时间戳

#

Unix时间戳(Unix timestamp)

是从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数，不考虑闰秒。

9. monthname(n),dayname(n)  获取日期的月份和星期名

10. dayofyear(n),weekofyear(n),dayofmonth(n);  获取日期的天数，星期数、详见实践。

11. hour(n) | minute(n) | second(n) 获取时间的时分秒。

12. extract(type from n)  从时间n提取type信息

13. time_to_sec(n) 将n转化成秒为单位的时间

sec_to_time(n) 将单位的时间转化成时分秒的表达式

14. date_format(d,f) 将日期d转化成f格式

time_format(t,f) 将时间t转化成f格式

C:\Users\Administrator>mysql -u root -p

Enter password: ********

1.

mysql> select curdate(),current_date();

+------------+----------------+

| curdate() | current_date() |

+------------+----------------+

| 2016-02-03 | 2016-02-03 |

+------------+----------------+

1 row in set (0.01 sec)

2.

mysql> select curtime(),current_time();

+-----------+----------------+

| curtime() | current_time() |

+-----------+----------------+

| 21:36:39 | 21:36:39 |

+-----------+----------------+

1 row in set (0.00 sec)

3.

mysql> select now(),current_timestamp(),localtime(),sysdate();

+---------------------+---------------------+---------------------+---------------------+

| now() | current_timestamp() | localtime() | sysdate() |

+---------------------+---------------------+---------------------+---------------------+

| 2016-02-03 21:40:50 | 2016-02-03 21:40:50 | 2016-02-03 21:40:50 | 2016-02-03 21:40:50 |

+---------------------+---------------------+---------------------+---------------------+

1 row in set (0.00 sec)

4.

mysql> select datediff('2015.08.20','2016.02.03');

+-------------------------------------+

| datediff('2015.08.20','2016.02.03') |

+-------------------------------------+

| -167 |

+-------------------------------------+

1 row in set (0.02 sec)

5.

mysql> select adddate('2016.02.03','10');

+----------------------------+

| adddate('2016.02.03','10') |

+----------------------------+

| 2016-02-13 |

+----------------------------+

1 row in set (0.03 sec)

# 不过他们的格式没有严格的要求：

mysql> select adddate('2016-02-03',10);

+--------------------------+

| adddate('2016-02-03',10) |

+--------------------------+

| 2016-02-13 |

+--------------------------+

1 row in set (0.00 sec)

6.

mysql> select adddate('2016-02-03',interval '12 3' year_month);

+--------------------------------------------------+

| adddate('2016-02-03',interval '12 3' year_month) |

+--------------------------------------------------+

| 2028-05-03 |

+--------------------------------------------------+

1 row in set (0.00 sec)

7.

mysql> select subdate('2016-02-03',10);

+--------------------------+

| subdate('2016-02-03',10) |

+--------------------------+

| 2016-01-24 |

+--------------------------+

1 row in set (0.00 sec)

8.

mysql> select unix_timestamp(),unix_timestamp('2016.02.03');

+------------------+------------------------------+

| unix_timestamp() | unix_timestamp('2016.02.03') |

+------------------+------------------------------+

| 1454509344 | 1454428800 |

+------------------+------------------------------+

1 row in set (0.00 sec)

9.

mysql> select monthname(curdate()),dayname(curdate());

+----------------------+--------------------+

| monthname(curdate()) | dayname(curdate()) |

+----------------------+--------------------+

| February | Wednesday |

+----------------------+--------------------+

1 row in set (0.04 sec)

10.

mysql> select dayofyear(curdate()),weekofyear(curdate()),dayofmonth(curdate());

+----------------------+-----------------------+-----------------------+

| dayofyear(curdate()) | weekofyear(curdate()) | dayofmonth(curdate()) |

+----------------------+-----------------------+-----------------------+

| 34 | 5 | 3 |

+----------------------+-----------------------+-----------------------+

1 row in set (0.00 sec)

11.

mysql> select hour(now()),minute(now()),second(now());

+-------------+---------------+---------------+

| hour(now()) | minute(now()) | second(now()) |

+-------------+---------------+---------------+

| 22 | 36 | 29 |

+-------------+---------------+---------------+

1 row in set (0.03 sec)

12.

mysql> select extract(hour from now()),extract(month from now()),extract(year from now());

+--------------------------+---------------------------+--------------------------+

| extract(hour from now()) | extract(month from now()) | extract(year from now()) |

+--------------------------+---------------------------+--------------------------+

| 22 | 2 | 2016 |

+--------------------------+---------------------------+--------------------------+

1 row in set (0.00 sec)

13.

mysql> select time_to_sec(now()),sec_to_time('10000');

+--------------------+----------------------+

| time_to_sec(now()) | sec_to_time('10000') |

+--------------------+----------------------+

| 81870 | 02:46:40 |

+--------------------+----------------------+

1 row in set (0.00 sec)

14.

mysql> select date_format(curdate(),'year:%y month:%m day:%d');

+-------------------------+

| date |

+-------------------------+

| year:16 month:02 day:03 |

+-------------------------+

1 row in set (0.00 sec)

mysql> select date_format(now(),'%T');

+----------+

| time |

+----------+

| 22:57:02 |

+----------+

1 row in set (0.00 sec)

mysql> select date_format(now(),'%y.%m.%d %T');

+----------------------------------+

| date_format(now(),'%y.%m.%d %T') |

+----------------------------------+

| 16.02.03 23:02:15 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select time_format(now(),'date:%y-%m-%d time:%h:%m:%s');

+--------------------------------------------------+

| time_format(now(),'date:%y-%m-%d time:%h:%m:%s') |

+--------------------------------------------------+

| date:00-00-00 time:11:00:33 |

+--------------------------------------------------+

1 row in set (0.00 sec)