Criteria api wrong time

Question

I have timestamp(6) column in my database. Example : "2020-10-01 00:00:00" By criteria API I made two predicates

private static Predicate withInstantGe(CriteriaBuilder cb, Root<Object> root, Instant date) {
return Optional.ofNullable(date).map(d -> cb.greaterThanOrEqualTo(root.<Instant>get("date"), d)).orElse(cb.and());
}

private static Predicate withInstantLe(CriteriaBuilder cb, Root<Object> root, Instant date) {
return Optional.ofNullable(date).map(d -> cb.lessThanOrEqualTo(root.<Instant>get("date"), d)).orElse(cb.and());
}

But it does not return columns with dates from "2020-10-01 00:00:00" to "2020-10-01 05:00:00" The difference is 5 hours... and my ZoneInfo[id="Asia/Yekaterinburg", but I don't undersend how it affects on criteria return.

Calendar startCalendar = getCalendarFrom(); (method returns Calendar where i setted yead day month etc)
TimeZone sdvsd = startCalendar.getTimeZone();
Instant start = getInstant(startCalendar);

Calendar endCalendar = getCalendarTo();
Instant end = getInstant(endCalendar);

private Instant getInstant(Calendar calendar) {
ZonedDateTime zdt = ZonedDateTime.ofInstant(calendar.toInstant(), calendar.getTimeZone().toZoneId());
return zdt.withZoneSameLocal(ZoneOffset.UTC).toInstant();
}

My mistake was that I use ZoneOffset.UTC, and hibernate automatically edit date in my requests, as i can see just add 5 hours. The solution is when you convert LocalDate to Instant, use local offset and hibernate doesn't add hours, like this : `.toInstant(OffsetDateTime.now().getOffset()) ` — Семен Немытов, Nov 02 '20 at 06:06

score 1 · Accepted Answer · answered Oct 29 '20 at 10:26

I recommend you do not contaminate the clean modern date-time API with error-prone legacy java.util date-time API.

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String[] args) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss");
        ZonedDateTime zdtStart = LocalDateTime.parse("2020-10-01 00:00:00", formatter)
                .atZone(ZoneId.of("Asia/Yekaterinburg"));
        System.out.println(zdtStart);

        // Add 5 hours to start date-time
        ZonedDateTime zdtEnd = zdtStart.plusHours(5);
        System.out.println(zdtEnd);

        // In case you need Instant from ZonedDateTime
        Instant start = zdtStart.toInstant();
        Instant end = zdtEnd.toInstant();
        System.out.println(start);
        System.out.println(end);
    }
}

Output:

2020-10-01T00:00+05:00[Asia/Yekaterinburg]
2020-10-01T05:00+05:00[Asia/Yekaterinburg]
2020-09-30T19:00:00Z
2020-10-01T00:00:00Z

Learn more about the modern date-time API at Trail: Date Time.

If you are doing it for your Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.

My mistake was that I use ZoneOffset.UTC, and hibernate automatically edit date in my requests, as i can see just add 5 hours. The solution is when you convert LocalDate to Instant, use local offset and hibernate doesn't add hours, like this : .toInstant(OffsetDateTime.now().getOffset()) — Семен Немытов, Nov 02 '20 at 06:06

score 0 · Answer 2 · answered Oct 29 '20 at 10:03

I would try to implement it in the following way to be able to abstract from the time zone, which does nothing but complicate:

Calendar start = Calendar.getInstance();
start.set(Calendar.YEAR,2020);
start.set(Calendar.MONTH,9);
start.set(Calendar.DAY_OF_MONTH,1);
start.set(Calendar.HOUR,0);
start.set(Calendar.MINUTE,0);
start.set(Calendar.SECOND,0);

Calendar end = Calendar.getInstance();
start.set(Calendar.YEAR,2020);
start.set(Calendar.MONTH,9);
start.set(Calendar.DAY_OF_MONTH,1);
start.set(Calendar.HOUR,5);
start.set(Calendar.MINUTE,0);
start.set(Calendar.SECOND,0);

And your predicates:

cb.and(
    cb.greaterThanOrEqualTo(root.get("date"),start.getTime()),
    cb.lessThanOrEqualTo(root.get("date"),end.getTime())
)

Criteria api wrong time

2 Answers2