java获取年月日、时间与区间、Sql获取年月日区间
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());
获取某个区间内时间(从昨天开始往前推七天为例)：
/**
* 根据当前日期获取前一天的日期
*/
public static String yesterday() {
//获取今天的日期
LocalDate localDate = LocalDate.now();
//前一天日期是今天减1
LocalDate yesterday = localDate.plusDays(-1);
return yesterday.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}
/**
* 获取七天前的日期
*/
private static String getWeekAgo() {
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.DAY_OF_YEAR, -7);
Date date = calendar.getTime();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String sevenDaysAgo = sdf.format(date);
return sevenDaysAgo;
}
SELECT DATE_FORMAT( a.DAY, '%m-%d' ) AS DATE
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as day
from (select 0 as a union all select 1 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) as a
cross join (select 0 as a union all select 1 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) as b
cross join (select 0 as a union all select 1 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) as c
cross join (select 0 as a union all select 1 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) as d
) a
WHERE a.DAY BETWEEN "2023-08-10" AND "2023-08-15"
<!--将上面时间替换掉即可-->
<!--开发中发现上面这种存在日期显示问题，故更换下面这种-->
SELECT ADDDATE(#{startTime}, INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a FROM (SELECT 0 AS a UNION ALL SELECT 1 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) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 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) AS c
) a
JOIN (SELECT @i := -1) r
WHERE @i < DATEDIFF(#{endTime},#{startTime})) d
java获取时间来自CSDN博主文章，文下附上链接：
https://blog.csdn.net/web18536564348/article/details/126403332
