获取今日,本周,本月开始结束时间
通常当作sql的查询时间使用
通过Calendar类可以获取当天/本周/本月始末日期后,通过拼接,获取具体的时间
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(System.currentTimeMillis());
//获取当天时间 年月日 时分秒
calendar.add(Calendar.DATE, 0);
String today = dateFormat.format(calendar.getTime());
System.out.println(today + " 00:00:00");
System.out.println(today + " 23:59:59");
//设置周一为一周的开始
calendar.setFirstDayOfWeek(Calendar.MONDAY);
calendar.setTimeInMillis(System.currentTimeMillis());
calendar.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
String monday = dateFormat.format(calendar.getTime()) + " 00:00:00";
calendar.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
String sunday = dateFormat.format(calendar.getTime()) + " 24:00:00";
System.out.println(monday);
System.out.println(sunday);
//获取当月的开始时间 结束时间
calendar.add(Calendar.MONTH, 0);
calendar.set(Calendar.DAY_OF_MONTH, 1);
String firstDayOfMonth = dateFormat.format(calendar.getTime()) + " 00:00:00";
calendar.add(Calendar.MONTH, 1);
calendar.set(Calendar.DAY_OF_MONTH, 0);
String lastDayOfMonth = dateFormat.format(calendar.getTime()) + " 24:00:00";
System.out.println(firstDayOfMonth);
System.out.println(lastDayOfMonth);
//LocalDateTime与String的转换
LocalDateTime localDateTime = LocalDateTime.now();
String time = DateTimeFormatter.ofPattern("yyyy-MM-dd").format(localDateTime);
System.out.println("test: " + time);
SQL中的时间条件查询
postgresql数据库中时间属性类型为timestamp
当条件时间为String
将时间转换为字符串后,进行比较
例子1
where
to_char(created_date,'yyyy-MM-dd HH24:MI:ss') <![CDATA[>=]]> #{startDate}
and
to_char(created_date,'yyyy-MM-dd HH24:MI:ss')<![CDATA[<=]]> #{endDate}
例子2
<if test="dto.startDate != null and dto.startDate != ''">
AND to_char(bo.order_time, 'yyyy-MM-dd') <![CDATA[>=]]> #{dto.startDate}
</if>
<if test="dto.endDate != null and dto.endDate != ''">
AND to_char(bo.order_time, 'yyyy-MM-dd') <![CDATA[<=]]> #{dto.endDate}
</if>
当条件时间为Instant或者Date类型
<if test="dto.startTime != null">
AND <![CDATA[ a.order_time > #{dto.startTime} ]]>
</if>
<if test="dto.endTime != null">
AND <![CDATA[ a.order_time < #{dto.endTime} ]]>
</if>