mysql常用日期与时间函数详解

在mysql数据库中日期与时间是我们常用的一些东西,下面我来给各位朋友总结一下在mysql中常用日期与时间函数一些用法句语,各位朋友可参考.

MySQL 日期类型:日期格式、所占存储空间、日期范围 比较.

1. 日期类型        存储空间       日期格式                 日期范围  
2. ------------  ---------   --------------------- -----------------------------------------  
3. datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59  
4. timestamp      4 bytes   YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038  
5. date           3 bytes   YYYY-MM-DD            1000-01-01          ~ 9999-12-31  
6. year           1 bytes   YYYY                  1901                ~ 2155 

NOW()函数获得当前的日期和时间,代码如下:

1. select now(); 
2. /* 
3. +---------------------+ 
4. | now() | 
5. +---------------------+ 
6. | 2009-05-18 20:11:24 | 
7. +---------------------+ 
8. 1 row in set (0.02 sec) 
9. */ 

呵,这个函数是我经常用的,没事就看一下当前时间,看离下班还有多久,呵呵.

CURTIME()和CURDATE()比NOW()更为具体化,返回当前的时间和日期,代码如下:

1. select curtime(),curdate(); 
2. /* 
3. +-----------+------------+ 
4. | curtime() | curdate() | 
5. +-----------+------------+ 
6. | 20:13:28 | 2009-05-18 | 
7. +-----------+------------+ 
8. 1 row in set (0.02 sec) 
9. */ 

UNIX_TIMESTAMP()函数返回UNIX时间戳,代码如下:

1. select unix_timestamp(); 
2. /* 
3. +------------------+ 
4. | unix_timestamp() | 
5. +------------------+ 
6. | 1242648913 | 
7. +------------------+ 
8. 1 row in set (0.00 sec) 
9. */ 

呃,时间戳咋转换为时间呢？难道一定要用PHP的strtotime()？呵,那太麻烦了吧？别急,这就来了.

FROM_UNIXTIME()函数把UNIX时间戳转换为MySQL易读的日期和时间,代码如下:

1. select from_unixtime(unix_timestamp()); 
2. /* 
3. +---------------------------------+ 
4. | from_unixtime(unix_timestamp()) | 
5. +---------------------------------+ 
6. | 2009-05-18 20:17:23 | 
7. +---------------------------------+ 
8. 1 row in set (0.02 sec) 
9. */ 

我感觉这个例子,比直接读取一个from_unixtime()更容易让你理解不是吗？

YEAR()——年,MONTH(),MONTHNAME()——月,DAYOFYEAR(),DAYOFWEEK(),DAYOFMONTH()——这天在年,星期,月份里的序数,代码如下:

1. select year(20030415012345),year(2009-05-18); 
2. /* 
3. +----------------------+------------------+ 
4. | year(20030415012345) | year(2009-05-18) | 
5. +----------------------+------------------+ 
6. | 2003 | NULL | 
7. +----------------------+------------------+ 
8. 1 row in set, 1 warning (0.02 sec) 
9. */ 
10. select year(20030415012345),year('2009-05-18'); 
11. /* 
12. +----------------------+--------------------+ 
13. | year(20030415012345) | year('2009-05-18') | 
14. +----------------------+--------------------+ 
15. | 2003 | 2009 | 
16. +----------------------+--------------------+ 
17. 1 row in set (0.02 sec) 
18. */ 

看到没？如果我不把带有格式的日期放入引号内,返回来的可是NULL,另外,如果year()不带参数的话,是会出错的,嗯,刚才有试验过了,代码如下:

1. select month(20030414012345),month('2009-05-18'),month(2009-05-18),month('2009-13-18'); 
2. /* 
3. +-----------------------+---------------------+-------------------+---------------------+ 
4. | month(20030414012345) | month('2009-05-18') | month(2009-05-18) | month('2009-13-18') | 
5. +-----------------------+---------------------+-------------------+---------------------+ 
6. | 4 | 5 | NULL | NULL | 
7. +-----------------------+---------------------+-------------------+---------------------+ 
8. 1 row in set, 2 warnings (0.09 sec) 
9. */ 

看到没？同样,如果月份不合法的话也会返回NULL的哈,并且带有格式的参数一定要放入引号内,代码如下:

1. select monthname('20090518'),monthname('2009-05-18'),monthname(2009-05-18),monthname('2009-13-18'); 
2. /* 
3. +-----------------------+-------------------------+-----------------------+-------------------------+ 
4. | monthname('20090518') | monthname('2009-05-18') | monthname(2009-05-18) | monthname('2009-13-18') | 
5. +-----------------------+-------------------------+-----------------------+-------------------------+ 
6. | May | May | NULL | NULL | 
7. +-----------------------+-------------------------+-----------------------+-------------------------+ 
8. 1 row in set, 2 warnings (0.02 sec) 
9. */ 

这个我个人感觉与MONTH()相同哈,只是返回结果是月份的名字,所以当然是英文的,代码如下:

1. select dayofyear(20090518) as day1,dayofyear('20090518') as day2,dayofyear('2009-05-18') as day3,dayofyear(20090518203000) as day4,dayofyear(20091318) as day5; 
2. /* 
3. +------+------+------+------+------+ 
4. | day1 | day2 | day3 | day4 | day5 | 
5. +------+------+------+------+------+ 
6. | 138 | 138 | 138 | 138 | NULL | 
7. +------+------+------+------+------+ 
8. 1 row in set, 1 warning (0.02 sec) 
9. */ 

呃,DAYOFMONTH()与DAYOFWEEK()同此一样,就不再多举例子了,嗯,同时,与MONTHNAME()一样,也有DAYNAME()获得该日期所代表的星期名,即星期几.

WEEK()函数返回指定日期是一年的第几个星期，YEERWEEK()函数返回指定的日期是哪一年的哪个星期,代码如下:

1. select week(20090301) as week1,week(2009-03-01) as week2,week('2009-03-01') as week3,yearweek(20090518) as week4,yearweek(2009-05-18) as week5,yearweek('2009-05-18') as week6; 
2. /* 
3. +-------+-------+-------+--------+-------+--------+ 
4. | week1 | week2 | week3 | week4 | week5 | week6 | 
5. +-------+-------+-------+--------+-------+--------+ 
6. | 9 | NULL | 9 | 200920 | NULL | 200920 | 
7. +-------+-------+-------+--------+-------+--------+ 
8. 1 row in set, 2 warnings (0.02 sec) 
9. */ 

HOUR(),MINUTE()和SECOND()函数分析时间值，并且分别返回小时、分钟和秒的部分,代码如下:

1. select hour(182300),second(123400),minute('20:56'); 
2. /* 
3. +--------------+----------------+-----------------+ 
4. | hour(182300) | second(123400) | minute('20:56') | 
5. +--------------+----------------+-----------------+ 
6. | 18 | 0 | 56 | 
7. +--------------+----------------+-----------------+ 
8. 1 row in set (0.00 sec) 
9. */ 

由WEEK()和YEARWEEK()返回的值通常都是在0到53之间,不明白的算一下一年有多少个星期,但是,我们可以把它修改到1到54之间,而且还可以通过选择函数的第二个参数来决定一个星期是从周日开始还是周一开始.

TIME_TO_SEC()——把时间转为秒,SEC_TO_TIME()——把秒数转为一个易读的时间,代码如下:

1. select sec_to_time(80),sec_to_time(3720),time_to_sec('24:01:10'); 
2. /* 
3. +-----------------+-------------------+-------------------------+ 
4. | sec_to_time(80) | sec_to_time(3720) | time_to_sec('24:01:10') | 
5. +-----------------+-------------------+-------------------------+ 
6. | 00:01:20 | 01:02:00 | 86470 | 
7. +-----------------+-------------------+-------------------------+ 
8. 1 row in set (0.00 sec) 
9. */ 

日期相加减也是我们常要用到的,那么MySQL给了我们哪些现成的方法呢？让我来告诉你.

DATE_ADD(),DATE_SUB()——日期相加减,代码如下:

1. select date_add('2009-05-18 00:00:00',INTERVAL 6 MONTH); 
2. /* 
3. +--------------------------------------------------+ 
4. | date_add('2009-05-18 00:00:00',INTERVAL 6 MONTH) | 
5. +--------------------------------------------------+ 
6. | 2009-11-18 00:00:00 | 
7. +--------------------------------------------------+ 
8. 1 row in set (0.04 sec) 
9. */ 
10. select date_add('2009-05-18 00:00:00', interval '12 03:45' day_minute); 
11. /* 
12. +-----------------------------------------------------------------+ 
13. | date_add('2009-05-18 00:00:00', interval '12 03:45' day_minute) | 
14. +-----------------------------------------------------------------+ 
15. | 2009-05-30 03:45:00 | 
16. +-----------------------------------------------------------------+ 
17. 1 row in set (0.02 sec) 
18. */ 
19. select date_sub('2009-05-18 00:00:00',interval 6 hour); 
20. /* 
21. +-------------------------------------------------+ 
22. | date_sub('2009-05-18 00:00:00',interval 6 hour) | 
23. +-------------------------------------------------+ 
24. | 2009-05-17 18:00:00 | 
25. +-------------------------------------------------+ 
26. 1 row in set (0.00 sec) 
27. */ 
28. select date_sub('2009-05-18 00:00:00', interval '13-4' year_month); 
29. /*  Vevb.com 
30. +-------------------------------------------------------------+ 
31. | date_sub('2009-05-18 00:00:00', interval '13-4' year_month) | 
32. +-------------------------------------------------------------+ 
33. | 1996-01-18 00:00:00 | 
34. +-------------------------------------------------------------+ 
35. 1 row in set (0.02 sec) 
36. */ 

下面的查询选择了所有记录,其date_col的值是在最后30天以内,代码如下:

1. mysql> SELECT something FROM table  
2. WHERE TO_DAYS(NOW()) - TO_DAYS(date_col)<= 30; 
3. DAYOFWEEK(date)  

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六),这些索引值对应于ODBC标准,代码如下:

1. mysql> select DAYOFWEEK('1998-02-03');  
2. -> 3 
3. WEEKDAY(date)  

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天),代码如下:

1. mysql> select WEEKDAY('1997-10-04 22:23:00');  
2. -> 5  
3. mysql> select WEEKDAY('1997-11-05');  
4. -> 2 
5. DAYOFMONTH(date)  

返回date的月份中日期,在1到31范围内,代码如下:

1. mysql> select DAYOFMONTH('1998-02-03');  
2. -> 3 
3. DAYOFYEAR(date) 

返回date在一年中的日数,在1到366范围内,代码如下:

1. mysql> select DAYOFYEAR('1998-02-03');  
2. -> 34 
3. MONTH(date) 

返回date的月份,范围1到12,代码如下:

1. mysql> select MONTH('1998-02-03');  
2. -> 2 
3. DAYNAME(date)  

返回date的星期名字,代码如下:

1. mysql> select DAYNAME("1998-02-05");  
2. -> 'Thursday' 
3. MONTHNAME(date) 

返回date的月份名字,代码如下:

1. mysql> select MONTHNAME("1998-02-05");  
2. -> 'February' 
3. QUARTER(date) 

返回date一年中的季度,范围1到4,代码如下:

1. mysql> select QUARTER('98-04-01');  
2. -> 2 
3. WEEK(date) 　  
4. WEEK(date,first) 

对于星期天是一周的第一天的地方,有一个单个参数,返回date的周数,范围在0到52,2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一,如果第二个参数是0,星期从星期天开始,如果第二个参数是1,从星期一开始,代码如下:

1. mysql> select WEEK('1998-02-20');  
2. -> 7  
3. mysql> select WEEK('1998-02-20',0);  
4. -> 7  
5. mysql> select WEEK('1998-02-20',1);  
6. -> 8 
7. YEAR(date) 

返回date的年份,范围在1000到9999,代码如下:

1. mysql> select YEAR('98-02-03');  
2. -> 1998 
3. HOUR(time) 

返回time的小时,范围是0到23,代码如下:

1. mysql> select HOUR('10:05:03');  
2. -> 10 
3. MINUTE(time) 

返回time的分钟,范围是0到59,代码如下:

1. mysql> select MINUTE('98-02-03 10:05:03');  
2. -> 5 
3. SECOND(time) 

回来time的秒数,范围是0到59,代码如下:

1. mysql> select SECOND('10:05:03');  
2. -> 3 
3. PERIOD_ADD(P,N) 

增加N个月到阶段P,以格式YYMM或YYYYMM,以格式YYYYMM返回值,注意阶段参数P不是日期值,代码如下:

1. mysql> select PERIOD_ADD(9801,2);  
2. -> 199803 
3. PERIOD_DIFF(P1,P2) 

返回在时期P1和P2之间月数,P1和P2应该以格式YYMM或YYYYMM,注意,时期参数P1和P2不是日期值,代码如下:

1. mysql> select PERIOD_DIFF(9802,199703);  
2. -> 11 
3. DATE_ADD(date,INTERVAL expr type)  
4. 　  
5. DATE_SUB(date,INTERVAL expr type)  
6. 　  
7. ADDDATE(date,INTERVAL expr type)  
8. 　  
9. SUBDATE(date,INTERVAL expr type)