本文详细介绍了MySQL中用于操作日期和时间的函数，如NOW(), CURDATE(), CURTIME()，以及DATE(), EXTRACT(), DATE_ADD(), DATE_SUB(), DATEDIFF(), DATE_FORMAT()等的用法和示例。掌握这些函数有助于高效管理时间数据。
MySQL 提供了很多可用于操作时间值的函数，暂时整理一些常用的，更多的参看文档
NOW(),CURDATE(),CURTIME()
NOW()返回当前的日期和时间,等价于 CURRENT_TIMESTAMP。CURDATE()返回当前的日期。CURTIME()返回当前的时间。1SELECT NOW(),CURDATE(),CURTIME()
1
2
3
4
5+---------------------+------------+-----------+
| NOW() | CURDATE() | CURTIME() |
+---------------------+------------+-----------+
| 2017-11-24 16:46:19 | 2017-11-24 | 16:46:19 |
+---------------------+------------+-----------+
DATE()
提取日期或日期/时间表达式的日期部分1SELECT DATE('2003-12-31 01:02:03');
1
2
3
4
5+-----------------------------+
| DATE('2003-12-31 01:02:03') |
+-----------------------------+
| 2003-12-31 |
+-----------------------------+
EXTRACT(unit FROM date)
返回日期/时间按的单独部分,unit可以是：MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH1SELECT EXTRACT(YEAR FROM '2009-07-02');1
2
3
4
5+---------------------------------+
| EXTRACT(YEAR FROM '2009-07-02') |
+---------------------------------+
| 2009 |
+---------------------------------+1SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');1
2
3
4
5+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
| 200907 |
+------------------------------------------------+1SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');1
2
3
4
5+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03') |
+------------------------------------------------+
| 20102 |
+------------------------------------------------+1SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');1
2
3
4
5+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123') |
+--------------------------------------------------------+
| 123 |
+--------------------------------------------------------+
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
给日期添加指定的时间间隔，date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。unit 与对应的 expr 参数格式：unit ValueExpected expr FormatMICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
SECOND_MICROSECOND‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND‘MINUTES:SECONDS’
HOUR_MICROSECOND‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE‘HOURS:MINUTES’
DAY_MICROSECOND‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE‘DAYS HOURS:MINUTES’
DAY_HOUR‘DAYS HOURS’
YEAR_MONTH‘YEARS-MONTHS’1SELECT DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND);1
2
3
4
5+--------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) |
+--------------------------------------------------------------+
| 2101-01-01 00:01:00 |
+--------------------------------------------------------------+1SELECT DATE_SUB('2005-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);1
2
3
4
5+---------------------------------------------------------------+
| DATE_SUB('2005-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) |
+---------------------------------------------------------------+
| 2004-12-30 22:58:59 |
+---------------------------------------------------------------+
DATEDIFF(expr1,expr2)
返回 expr1 − expr2 的天数，这只对日期部分做计算1SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
1
2
3
4
5+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+1SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');1
2
3
4
5+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
| -31 |
+----------------------------------------------+
DATE_FORMAT(date,format)
用不同的格式显示日期/时间,可以使用的格式有：标识符描述%a缩写星期名 (Sun..Sat)
%b缩写月名 (Jan..Dec)
%c月，数值 (0..12)
%D带有英文前缀的月中的天 (0th, 1st, 2nd, 3rd, …)
%d月的天，数值 (00..31)
%e月的天，数值 (0..31)
%f微秒 (000000..999999)
%H小时 (00..23)
%h小时 (01..12)
%I小时 (01..12)
%i分钟，数值 (00..59)
%j年的天 (001..366)
%k小时 (0..23)
%l小时 (1..12)
%M月名 (January..December)
%m月，数值 (00..12)
%pAM 或 PM
%r时间，12时制 (hh:mm:ss AM 或 PM)
%S秒 (00..59)
%s秒 (00..59)
%T时间, 24时制 (hh:mm:ss)
%U周 (00..53), 星期日是一周的第一天; WEEK() mode 0
%u周 (00..53), 星期一是一周的第一天; WEEK() mode 1
%V周 (01..53), 星期日是一周的第一天; WEEK() mode 2; 与 %X 使用
%v周 (01..53), 星期一是一周的第一天; WEEK() mode 3; 与 %x 使用
%W星期名 (Sunday..Saturday)
%w周的天 (0=Sunday..6=Saturday)
%X年，其中的星期日是周的第一天，4 位，与 %V 使用
%x年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y年，4 位
%y年，2 位1SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');1
2
3
4
5+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+1SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');1
2
3
4
5+------------------------------------------------+
| DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+1SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');1
2
3
4
5+------------------------------------------------------------+
| DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+1SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');1
2
3
4
5+------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------+
扫一扫
2738
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
