18.3 日期和时间函数
MySQL所支持的日期和时间函数有:
函数 | 功能 |
---|---|
CURDATE() | 获取当前日期 |
CURTIME() | 获取当前时间 |
NOW() | 获取当前的日期和时间 |
UNIX_TIMESTAMP() | 获取日期date的UNIX时间戳 |
FROM_UNIXTIME() | 获取UNIX时间戳的日期值 |
WEEK(date) | 获取日期date为一年中的第几周 |
YEAR(date) | 返回日期date的年份 |
HOUR(time) | 返回时间time的小时值 |
MINUTE(time) | 返回时间time的分钟值 |
MONTHNAME(date) | 返回时间time的月份值 |
18.3.1 获取当前日期和时间
MySQL中可以通过4个函数来获取当前日期和时间,它们分别是NOW()、CURRENT_TIMESTAMP()、LOCALTIME()和SYSDATE(),这四个函数不仅可以获取当前日期和时间,而且显示的格式也一样。不过具体使用中,推荐使用NOW()。
例如:
mysql> select now() NOW方式,current_timestamp() timestamp方式,localtime() localtime方式,sysdate() systemdate方式;
18.3.2 获取当前日期
MySQL中可以通过2个函数来获取当前日期,它们分别是CURDATE()和CURRENT_DATE(),推荐使用CURDATE()。
例如:
mysql> select curdate() CURDATE方式, current_date() CURRENT_DATE方式;
18.3.3 获取当前时间
MySQL中可以通过2个函数来获取当前日期,它们分别是CURTIME ()和CURRENT_TIME(),推荐使用CURTIME()。
例如:
mysql> select curtime() CURTIME方式, current_time() CURRENT_TIME方式;
18.3.4 通过UNIX方式显示日期和时间
MySQL中可以使用UNIX方式显示时间。所谓UNIX是指Unix epoch、Unix time、POSIX time或Unix timestamp的缩写,意为时间戳。
根据ISO-8601规范,该方式将显示从1970年1月1日开始所经过的秒数,即一分钟表示为UNIX时间戳格式为60秒,一小时表示为UNIX时间戳格式为3600秒,一天表示为UNIX时间戳格式为86400秒。
MySQL提供UNIX_TIMESTAMP()函数返回时间戳格式的时间,FROM_UNIXTIME()函数将时间戳格式时间转换为普通格式的时间。
例如:
mysql> select now() 当前时间,unix_timestamp(now()) UNIX格式,from_unixtime(unix_timestamp(now())) 普通格式;
说明:当UNIX_TIMESTAMP()函数没有传入参数时,则会显示当前日期和时间的时间戳;当UNIX_TIMESTAMP()函数传入参数时,则显示传入时间的时间戳形式。
18.3.5 通过UTC方式显示日期和时间
MySQL中,也可以使用UTC方式显示日期和时间。所谓UTC,就是Universal Coordinated Time,即国际协调时间。MySQL提供了两个函数UTC_DATE()和UTC_TIME()来实现日期和时间的UTC格式显示。
例如:
mysql> select NOW() 当前日期和时间,
-> UTC_DATE() UTC日期,
-> UTC_TIME() UTC时间;
说明:从上面的结果可以看出,UTC_DATE()函数返回的日期和当前日期相同,而UTC_TIME()返回的时间与当前时间相差8个时区。
18.3.6 通过日期和时间各部分值
MySQL中,YEAR()返回日期中的年份;QUARTER()返回日期属于第几个季度;MONTH()返回日期属于第几个月;WEEK()返回日期属于第几个星期;DAYOFMONTH()返回日期属于当前月的第几天;HOUR()返回时间的小时部分;MINUTE()返回时间的分钟部分;SECOND()返回时间的秒。
例如:
mysql> select NOW() 当前日期和时间,
-> YEAR(NOW()) 年,
-> QUARTER(NOW()) 季度,
-> MONTH(NOW()) 月,
-> WEEK(NOW()) 星期,
-> DAYOFMONTH(NOW()) 天,
-> HOUR(NOW()) 小时,
-> MINUTE(NOW()) 分,
-> SECOND(NOW()) 秒;
mysql> select NOW() 当前日期和时间,
-> MONTH(NOW()) 月,
-> MONTHNAME(NOW()) 月;
mysql> select NOW() 当前日期和时间,
-> WEEK(NOW()) 年中第几个星期,
-> WEEKOFYEAR(NOW()) 年中第几个星期,
-> DAYNAME(NOW()) 星期,
-> DAYOFWEEK(NOW()) 星期,
-> WEEKDAY(NOW()) 星期;
说明:
DAYOFWEEK():返回日期和时间中星期是星期几,返回值是1~7。如果返回值是1则表示星期日;返回值是2则表示星期一,以此类推。
WEEKDAY():返回日期和时间中星期是星期几,返回值是0~6。如果返回值是0则表示星期一,返回值是1则表示星期二,以此类推。
mysql> select NOW() 当前日期和时间,
-> DAYOFYEAR(NOW()) 年中第几天,
-> DAYOFMONTH(NOW()) 月中第几天;
注意:如果要获取日期和时间的各部分值,需要记住上述的各种函数,比较麻烦。MySQL还提供了一个EXTRACT()函数来统一获取日期和时间的各部分值。
语法:
EXTRACT(type from date)
上述函数会从日期和时间参数date中获取指定类型参数type的值。关于type参数的取值可以是YEAR、MONTH、DAY、HOUR、MINUTE和SECOND
mysql> select NOW() 当前日期和时间,
-> EXTRACT(YEAR FROM NOW()) 年,
-> EXTRACT(MONTH FROM NOW()) 月,
-> EXTRACT(DAY FROM NOW()) 天,
-> EXTRACT(HOUR FROM NOW()) 小时,
-> EXTRACT(MINUTE FROM NOW()) 分钟,
-> EXTRACT(SECOND FROM NOW()) 秒;
18.3.7 计算日期和时间的函数
MySQL提供了两种类型的计算日期和时间函数,第一种是计算与默认日期和时间(0000年1月1日)相互操作的函数;第二种是计算与指定日期和时间相互操作的函数。
(1)与默认日期和时间操作
MySQL中提供了两个函数来实现与默认日期和时间的操作,分别为:TO_DAYS()和FROM_DAYS()。
TO_DAYS(date):该函数计算日期参数date与默认日期和时间(0000年1月1日)之间相隔天数;
FROM_DAYS(number):该函数计算从默认日期和时间(0000年1月1日)开始经历number天后的日期和时间。
mysql> select NOW() 当前日期和时间,
-> TO_DAYS(NOW()) 相隔天数,
-> FROM_DAYS(TO_DAYS(NOW())) 一段时间后日期和时间;
在具体应用时,有时需要获取指定两个日期之间相隔天数,这时就用到了DATEDIFF()函数
语法:
DATEDIFF(date1,date2) //返回date1和date2之间相隔的天数
例如:
mysql> select NOW() 当前日期和时间,
-> DATEDIFF(NOW(),”2012-10-01”) 相隔天数;
(2)与指定日期和时间操作
MySQL中提供了两个函数来实现与指定日期操作,分别为ADDDATE()和SUBDATE()函数。
ADDDATE(date,n):该函数计算日期参数date加上n天后的日期;
SUBDATE(date,n):该函数计算日期参数date减去n天后的日期。
mysql> select CURDATE() 当前日期,
-> ADDDATE(CURDATE(),5) 5天后的日期,
-> SUBDATE(CURDATE(),5) 5天前的日期;
ADDDATE()和SUBDATE()函数除了可以接受上述参数以外,还可以接受其他参数,具体如下:
ADDDATE(date,INTERVAL expr type):该函数返回日期参数date加上一段时间后的日期,表达式expr决定了时间的长度,参数type决定了所操作的对象;
SUBDATE(date,INTERVAL expr type):该函数返回日期参数date减去一段时间后的日期,表达式expr决定了时间的长度,参数type决定了所操作的对象。
关于参数type的取值如下:
type的值 | 含义 | expr表达式 |
---|---|---|
YEAR | 年 | YY |
MONTH | 月 | MM |
DAY | 日 | DD |
HOUR | 小时 | Hh |
MINUTE | 分钟 | Mm |
SECOND | 秒 | Ss |
YEAR_MONTH | 年和月 | YY与MM之间用任意符号隔开 |
DAY_HOUR | 日和小时 | DD与hh之间用任意符号隔开 |
DAY_MINUTE | 日和分钟 | DD与mm之间用任意符号隔开 |
DAY_SECOND | 日和秒 | DD与ss之间用任意符号隔开 |
HOUR_MINUTE | 小时和分钟 | hh与mm之间用任意符号隔开 |
HOUR_SECOND | 小时和秒 | hh与ss之间用任意符号隔开 |
MINUTE_SECOND | 分钟和秒 | mm与ss之间用任意符号隔开 |
例如:
mysql> select CURDATE() 当前日期,
-> ADDDATE(CURDATE(),INTERVAL ‘2,3’ YEAR_MONTH) 2年3个月后的日期,
-> SUBDATE(CURDATE(),INTERVAL ‘2,3’ YEAR_MONTH) 2年3个月前的日期;
MySQL中除了可以通过ADDDATE()和SUBDATE()函数来操作指定日期外,还可以通过ADDTIME()和SUBTIME()函数实现操作时间
ADDTIME(time,n):该函数计算参数time加上n秒后的时间;
SUNTIME(time,n):该函数计算参数time减去n秒后的时间。
例如:
mysql> select CURTIME() 当前时间,
-> ADDTIME(CURTIME(),5) 5秒后的时间,
-> SUBTIME(CURTIME(),5) 5秒前的时间;