(一)日期的加减
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL 1 DAY)
UNION
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL 1 WEEK)
UNION
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL 1 MONTH)
UNION
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL 1 YEAR)
输出:
2001-01-02 00:00:00 2001-01-08 00:00:00 2001-02-01 00:00:00 2002-01-01 00:00:00 |
换成负数也可以:
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL -1 DAY)
UNION
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL -1 WEEK)
UNION
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL -1 MONTH)
UNION
SELECT DATE_ADD('2001-01-01 00:00:00',INTERVAL -1 YEAR)
2000-12-31 00:00:00 2000-12-25 00:00:00 2000-12-01 00:00:00 2000-01-01 00:00:00 |
(二)union和union ALL的区别 就是去重和不去重的区别
举例:
SELECT NOW() UNION SELECT NOW()
输出:
2017-05-23 10:48:48 |
SELECT NOW() UNION ALL SELECT NOW()
输出:
2017-05-23 10:49:16 2017-05-23 10:49:16 |
(三)MySQL 日期时间 Extract(选取) 函数。
1. 选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒
set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456
2. mysql Extract()函数使用:
set @dt = '2008-09-10 07:15:30.123456';
select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
select extract(day from @dt); -- 10
select extract(hour from @dt); -- 7
select extract(minute from @dt); -- 15
select extract(second from @dt); -- 30
select extract(microsecond from @dt); -- 123456
select extract(year_month from @dt); -- 200809
select extract(day_hour from @dt); -- 1007
select extract(day_minute from @dt); -- 100715
select extract(day_second from @dt); -- 10071530
select extract(day_microsecond from @dt); -- 10071530123456
select extract(hour_minute from @dt); -- 715
select extract(hour_second from @dt); -- 71530
select extract(hour_microsecond from @dt); -- 71530123456
select extract(minute_second from @dt); -- 1530
select extract(minute_microsecond from @dt); -- 1530123456
select extract(second_microsecond from @dt); -- 30123456
3. MySQL dayof... 函数:dayofweek(), dayofmonth(), dayofyear()
分别返回日期参数,在一周、一月、一年中的位置。
set @dt = '2008-08-08';
select dayofweek(@dt); -- 6
select dayofmonth(@dt); -- 8
select dayofyear(@dt); -- 221