SQL 获取时、日、周、月日期

因工作上常用到统计分析，需要用到具体的时间，故写于此

24小时：

 SELECT 0 AS hour UNION ALL SELECT 1 AS hour UNION ALL
 SELECT 2 AS hour UNION ALL SELECT 3 AS hour UNION ALL 
 SELECT 4 AS hour UNION ALL SELECT 5 AS hour UNION ALL 
 SELECT 6 AS hour UNION ALL SELECT 7 AS hour UNION ALL 
 SELECT 8 AS hour UNION ALL SELECT 9 AS hour UNION ALL 
 SELECT 10 AS hour UNION ALL SELECT 11 AS hour UNION ALL 
 SELECT 12 AS hour UNION ALL SELECT 13 AS hour UNION ALL 
 SELECT 14 AS hour UNION ALL SELECT 15 AS hour UNION ALL 
 SELECT 16 AS hour UNION ALL SELECT 17 AS hour UNION ALL 
 SELECT 18 AS hour UNION ALL SELECT 19 AS hour UNION ALL 
 SELECT 20 AS hour UNION ALL SELECT 21 AS hour UNION ALL 
 SELECT 22 AS hour UNION ALL SELECT 23 AS hour

当天、前一天，前一周日期：

//当天：
SELECT DATE_SUB(CURDATE(),INTERVAL 0 DAY);

//前一天：
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);

//前一周(从今天开始往前推七天)
select DATE_ADD(curdate(),INTERVAL -0 DAY) day union
select DATE_ADD(curdate(),INTERVAL -1 DAY) day1 union
select DATE_ADD(curdate(),INTERVAL -2 DAY) day2 union
select DATE_ADD(curdate(),INTERVAL -3 DAY) day3 union
select DATE_ADD(curdate(),INTERVAL -4 DAY) day4 union
select DATE_ADD(curdate(),INTERVAL -5 DAY) day5 union
select DATE_ADD(curdate(),INTERVAL -6 DAY) day6 ;

//后七天（从今天开始往后推七天）
select DATE_SUB(curdate(),INTERVAL -1 DAY) day1 union
select DATE_SUB(curdate(),INTERVAL -2 DAY) day2 union
select DATE_SUB(curdate(),INTERVAL -3 DAY) day3 union
select DATE_SUB(curdate(),INTERVAL -4 DAY) day4 union
select DATE_SUB(curdate(),INTERVAL -5 DAY) day5 union
select DATE_SUB(curdate(),INTERVAL -6 DAY) day6 union
select DATE_SUB(curdate(),INTERVAL -7 DAY) day7;

本周：

<!--本周第一天：(周一开始)-->
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY);
<!--本周最后一天：(周日结束)-->  
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY);


<!--本周第一天：(周日开始)-->
select subdate(curdate(),date_format(curdate(),'%w'));
<!--本周最后一天：(周六结束)-->  
select subdate(curdate(),date_format(curdate(),'%w') - 6);

近一、三个月日期：

<!--近一个月-->
SELECT ADDDATE(y.first, x.d - 1) as day
FROM   
 (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
  SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
  SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
  SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
    (SELECT CONCAT('2022-09','-01') as FIRST, DAY(LAST_DAY(str_to_date('2022-09','%Y-%m-%d'))) AS last) y
WHERE x.d <= y.last


<!--近三个月（使用union all链接）-->
SELECT ADDDATE(y.first, x.d - 1) as day
FROM
 (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
  SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
  SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
  SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
    (SELECT CONCAT('2022-09','-01') as FIRST, DAY(LAST_DAY(str_to_date('2022-09','%Y-%m-%d'))) AS last) y
WHERE x.d <= y.last

union all

SELECT ADDDATE(y.first, x.d - 1) as day
FROM
 (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
  SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
  SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
  SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
    (SELECT CONCAT('2022-09','-01') as FIRST, DAY(LAST_DAY(str_to_date('2022-09','%Y-%m-%d'))) AS last) y
WHERE x.d <= y.last

union all

SELECT ADDDATE(y.first, x.d - 1) as day
FROM
 (SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
  SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
  SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
  SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) x,
    (SELECT CONCAT('2022-09','-01') as FIRST, DAY(LAST_DAY(str_to_date('2022-09','%Y-%m-%d'))) AS last) y
WHERE x.d <= y.last

Java获取当前的年月日时间

        Calendar cal=Calendar.getInstance();
        // 当前年
        int year = cal.get(Calendar.YEAR);
        // 当前月
        int month = cal.get(Calendar.MONTH) + 1;
        // 当前日
        int day = cal.get(Calendar.DATE);
        // 当前小时
        int hour = cal.get(Calendar.HOUR_OF_DAY);
        // 当前分钟
        int minute = cal.get(Calendar.MINUTE);
        // 当前秒
        int second = cal.get(Calendar.SECOND);
        // 当前是本周第几天
        int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);
        // 当前是本月第几天
        int dayOfMonth = cal.get(Calendar.DAY_OF_MONTH);
        // 当前是本年第几天
        int dayOfYear = cal.get(Calendar.DAY_OF_YEAR);

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        // 当月第一天
        cal= Calendar.getInstance();
        cal.add(Calendar.MONTH, 0);
        cal.set(Calendar.DAY_OF_MONTH, 1);
        String firstday = format.format(cal.getTime());

        // 当月最后一天
        cal= Calendar.getInstance();
        cal.add(Calendar.MONTH, 1);
        cal.set(Calendar.DAY_OF_MONTH, 0);
        String lastday = format.format(cal.getTime());