MySQL 时间和日期函数

来源：互联网发布：ssh 端口修改编辑：程序博客网时间：2024/06/05 06:36

下面是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 -pEnter 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)

0 0