|
) :1~12
monthname() :月份的英文名字
mysql> select now() as 'now',
-> month(now()) as 'month',
-> monthname(now()) as 'month';
+---------------------+-------+-------+
| now | month | month |
+---------------------+-------+-------+
| 2015-04-30 07:33:08 | 4 | April |
+---------------------+-------+-------+
2、星期
week()和weekofyear() :返回日期和时间中星期是当前年的第几个星期，范围为1~53
实际应用中，经常不需要实现上述功能
mysql> select now() as 'now',week(now()) as 'week',weekofyear(now()) as 'weekofyear';
+---------------------+------+------------+
| now | week | weekofyear |
+---------------------+------+------------+
| 2015-04-30 07:41:59 | 17 | 18 |
+---------------------+------+------------+
dayname() :星期的英文名
dayofweek() :星期几，1~7，星期日为1
weekday() :星期几，0~6,0表示星期一，依次类推
mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now()) as 'dayofweek',weekday(now()) as 'weekday';
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04-30 07:45:08 | Thursday | 5 | 3 |
+---------------------+----------+-----------+---------+
mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now())-1 as 'dayofweek',weekday(now())+1 as 'weekday';
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04-30 08:07:24 | Thursday | 4 | 4 |
+---------------------+----------+-----------+---------+
3、天
dayofmonth() :月中的第几天
dayofyear() :年中的第几天
mysql> select now() as 'now',dayofmonth(now()) as 'dayofmonth',dayofyear(now()) as 'dayofyear';
+---------------------+------------+-----------+
| now | dayofmonth | dayofyear |
+---------------------+------------+-----------+
| 2015-04-30 07:52:37 | 30 | 120 |
+---------------------+------------+-----------+
1 row in set (0.00 sec)
4、获取指定时间域的值
extract() :extract,提取，获取
extract(type from date)
type:
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_MONTH :年和月
mysql> select now() as 'now',extract(year from now()) as 'year',extract(month from now()) as 'month',extract(day from now()) as 'day',extract(hour from now()) as 'hour',extract(minute from now()) as 'minute',extract(second from now()) as 'second',extract(week from now()) as 'week',extract(hour_minute from now()) as 'hour_minute',extract(quarter from now()) as 'quarter',extract(day_second from now()) as 'day_second' ;
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
| now | year | month | day | hour | minute | second | week | hour_minute | quarter | day_second |
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
| 2015-04-30 08:14:22 | 2015 | 4 | 30 | 8 | 14 | 22 | 17 | 814 | 2 | 30081422 |
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
6、计算日期和时间的函数
1、计算与默认日期和时间（0000年1月1日）相互操作的函数，
to_days(date)
计算日期参数date与默认日期和时间(0000年1月1日)之间相隔天数
from_days(number)
|