MySql常用日期时间查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

-- 某一天所在周的第一天： -- 我们知道国外的星期的第一天是从星期天开始的，所以DAYOFWEEK('2017-3-10')函数的第一天是星期天： SELECT CASE WHEN DAYNAME(DATE('2017-3-10'))='Sunday' THEN DATE_SUB(DATE('2017-3-10'),INTERVAL 6 DAY) ELSE DATE_ADD('2017-3-10',INTERVAL -DAYOFWEEK(DATE('2017-3-10'))+2 DAY) END   -- 某一天所在周的最后一天： SELECT CASE WHEN DAYNAME(DATE('2017-3-11'))='Sunday' THEN DATE('2017-3-11') ELSE DATE_ADD('2017-3-11',INTERVAL 7-DAYOFWEEK('2017-3-11')+1 DAY) END   -- 某一天的所在月的第一天： SELECT DATE_ADD( DATE_ADD(LAST_DAY('2017-03-03'),INTERVAL 1 DAY ),INTERVAL -1 MONTH );   -- 或 SELECT DATE_SUB(DATE_ADD(LAST_DAY('2017-03-03'),INTERVAL 1 DAY),INTERVAL 1 MONTH)   -- 或 SELECT DATE_SUB(LAST_DAY('2017-03-03'),INTERVAL TIMESTAMPDIFF(DAY,'2017-03-03',(DATE_ADD('2017-03-03',INTERVAL 1 MONTH)))-1 DAY)   -- 某一天所在月的最后一天： SELECT LAST_DAY('2017-03-03');   select DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s');   -- 某一天所在月的天数： SELECT TIMESTAMPDIFF(DAY,'2017-03-03',(DATE_ADD('2017-03-03',INTERVAL 1 MONTH)));     set @dt = now();  select date_add(@dt, interval 1 day);   - 加1天  select date_add(@dt, interval 1 hour);   -加1小时  select date_add(@dt, interval 1 minute);    - 加1分钟  select date_add(@dt, interval 1 second); -加1秒  select date_add(@dt, interval 1 microsecond);-加1毫秒  select date_add(@dt, interval 1 week);-加1周  select date_add(@dt, interval 1 month);-加1月  select date_add(@dt, interval 1 quarter);-加1季  select date_add(@dt, interval 1 year);-加1年    select TIMESTAMPDIFF(DAY,'2019-06-01','2019-06-05');   select DATE_FORMAT('20190606','%Y-%m-%d');   -- 遍历指定时间段集合 select t.date from ( SELECT DATE_FORMAT(DATE_ADD(date_sub('2019-06-01',INTERVAL 1 day), INTERVAL num DAY), '%Y-%m-%d') as date FROM (     SELECT @num:=@num+1 as num from     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) a,     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) b,     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) c,     (SELECT 1 UNION SELECT 2 UNION SELECT 3 ) d,       (SELECT @num:=0) m ) x)t where t.date<='2019-12-01';     -- 遍历指定时间段集合2 select a.date,DAYOFWEEK(a.date)-1,WEEKDAY(a.date)+1,DAYNAME(DATE(a.date)) from ( select @num:=@num+1, date_format(adddate(date_sub('2020-01-02',INTERVAL 1 day),INTERVAL @num day),'%Y-%m-%d') as date from t_student, (select @num:=0) t where ADDDATE('2020-01-02',INTERVAL @num day)<= date_format('2020-03-01','%Y-%m-%d') ) a;