Query data between two dates

Question

I need to get data between two dates from the Mongodb data that hosted in online server. I tried this code and it's working good in my Localhost (local data & live data). But when I uploaded the app in online and it's not working properly in Live.

The results are not accurate in live site. It fetches some records before and after the specified dates. For example, I give the dates 01-02-2018 and 28-02-2018, and the results are coming with records of 31-01-2018 and 01-03-2018.

I think the problem is dates are stored in UTC timezone ( 2018-02-15T23:33:30.000Z ).

Code:

SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");      

Date fromDate = format.parse(from + " 00:00:00");
Date  toDate = format.parse(to + " 23:59:59");

Calendar cal = Calendar.getInstance();
cal.setTime(order.getOrderDate());
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
sdf.setTimeZone(TimeZone.getTimeZone("Asia/Kolkata"));      
String strDate = sdf.format(cal.getTime());
Date orderDate = sdf.parse(strDate);

for(Order order : orders){
    if(orderDate.after(fromDate) || orderDate.equals(fromDate) && orderDate.before(toDate) || orderDate.equals(toDate)){
    //do something
    }
}

Your `if` condition is part of the problem. Without brackets it consists of three partial conditions separated by `||` (or), the first of which is `orderDate.after(fromDate)`, which is why it accepts dates in March. I think you meant to have brackets around each half, before and after `&&`. Time zone may be another contributor to the problem. — Ole V.V., Mar 06 '18 at 13:46
You are putting `order.getOrderDate()` into a `Calendar` and taking it out again, then formatting it to a string and parsing it back again to a `Date`. Isn’t all of that just waste? — Ole V.V., Mar 06 '18 at 13:49
The date and time classes you are using, `Date`, `Calendar` and `SimpleDateFormat`, are long outdated and the last in particular notoriously troublesome. [java.time, the modern Java date and time API,](https://docs.oracle.com/javase/tutorial/datetime/) is much nicer to work with and much better suited for solving time zone issues. I recommend it. — Ole V.V., Mar 06 '18 at 13:52
I tried to convert the order date from UTC to IST, so I have the calendar object. — Arjun Karthik, Mar 08 '18 at 07:31

xunts · Accepted Answer · 2018-03-06T15:56:53.553

java.util.Date doesn't have a timezone in it, so there's no point in parsing and formatting the order date. Formatting converts it to a String and parsing converts it back to a Date, which is pointless, because the order date is already a Date object.

You must set the timezone in the first formatter (the format variable), and then parse the from and to dates: they'll be set to the respective dates and times at Kolkata's timezone - in this case it's valid, because you have strings and want to convert them to dates.

Then you make your comparison using extra parenthesis to avoid any ambiguities (as pointed in the comments). And there's no point in setting the Date to a Calendar, just to get it back later - the Calendar instance has no purpose in your code.

And the call to getOrderDate shouldn't be inside the for loop?

The full code will be like this:

SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");      
// from and to dates are from Kolkata's timezone, so the formatter must know that
format.setTimeZone(TimeZone.getTimeZone("Asia/Kolkata"));      

// dates will be equivalent to this date and time in Kolkata, although
// the date itself doesn't store the timezone in it
Date fromDate = format.parse(from + " 00:00:00");
Date  toDate = format.parse(to + " 23:59:59");

for(Order order : orders){
    Date orderDate = order.getOrderDate();
    // note the extra parenthesis, they make all the difference
    if( (orderDate.after(fromDate) || orderDate.equals(fromDate)) &&
        (orderDate.before(toDate) || orderDate.equals(toDate)) ) {
    ....
    }
}

If you have Java >= 8, it's better to use the java.time API:

DateTimeFormatter fmt = DateTimeFormatter.ofPattern("dd-MM-yyyy");
ZoneId zone = ZoneId.of("Asia/Kolkata");
ZonedDateTime fromDate = LocalDate
    // parse "from" string
    .parse(from, fmt)
    // start of day at Kolkata timezone
    .atStartOfDay(zone);
ZonedDateTime toDate = LocalDate
    // parse "to" string
    .parse(to, fmt)
    // get start of next day
    .plusDays(1).atStartOfDay(zone);

// convert the Date to ZonedDateTime
for (Order order : orders) {
    ZonedDateTime orderDate = order.getOrderDate().toInstant().atZone(zone);
    if ((orderDate.isAfter(fromDate) || orderDate.isEqual(fromDate)) && (orderDate.isBefore(toDate))) {
        ...
    }
}

It's a different code because this API introduces new types and concepts, but it's quite a improvement from the previous API (Date and Calendar are messy, buggy and outdated).

Take some time to study this API, it's totally worth it: https://docs.oracle.com/javase/tutorial/datetime/

I think the declaration of `orderDate` should go inside the `for` loop in both snippets? The Java 8 version is great! I’d probably let `fromDate` and `toDate` be `Instant`s to avoid calling `atZone` for each order, but it’s a minor detail. Using a half-open interval (from inclusive and to exclusive) is absolutely recommended. Thanks for your answer, upvoted. — Ole V.V., Mar 06 '18 at 15:47
Even if using Java 6 or 7 I still recommend the latter version. You just need to get [ThreeTen Backport](http://www.threeten.org/threetenbp/) and add it to your Java project. And then the conversion from `Date` to `Instant` happens a little differently, that’s all. — Ole V.V., Mar 06 '18 at 15:49
@OleV.V. I believe that `ZonedDateTime` makes the code more clear about what dates we're using to compare - using `Instant` would require a mental conversion to the respective local time, harder to debug and maintain, IMO. And you're right about the loop, I updated that, thanks! — xunts, Mar 06 '18 at 16:00
You’ve got a point there. I may have been thinking optimization where there’s probably no reason to. — Ole V.V., Mar 06 '18 at 16:17

Query data between two dates

Code:

1 Answers1