Classic Issues with Date based Tests

I have run across this classic problem too many times.

The setup:

• Spring MVC based REST endpoint or HTML Form search
• query string date range parameters — interested in date days, not time of day
• Database stores dates for the events as DateTime (e.g. date with time and timezone offset) at UTC or epoch timestamp

Here is the scene

I created an endpoint that returns a list of events within the given date range that might look something like this:

@GetMapping(path = "/events")
public ResponseEntity<PagedVenueList> getEvents(


        @RequestParam(required = false)
        final String startDate,

        @RequestParam(required = false)
        final String endDate
) {

So a user can call the end point with /events?startDate=2020-01-01&endDate=2020-01-31 and end expect it to return all of January’s events.

But the tester after testing this endpoint comes back and says: “ only 98 events are returned, and the database query on the database shows that there are 102 events in January.”

I go : “Oh?!”

I compare the results of the endpoint call to the database and there are indeed 4 events missing.

Can you guess which ones?

All the events occurring on the 31st of January are missing in your endpoint. The tester gives me an eye roll because they have seen this problem many, many times before.

So what happened?

The first thing I did in implementing the endpoint controller was to convert the string dates in the query string to java Date instances, so they can be passed into the service. I have a handy utility for this occasion. So in the controller, I parse the dates from the request parameters:

Date start = Utils.parseDate(eventStartDate);
Date end = Utils.parseDate(eventEndDate);

and the utility class :

public static Date parseDate(String from) {
    Date date = null;
    if (StringUtils.isNotEmpty(from)) {
        try {
            date = Date.from(LocalDate.parse(from).atStartOfDay(ZoneId.systemDefault()).toInstant());
        } catch(DateTimeParseException e) {
            LOGGER.debug("Invalid date");
            throw new EventSearchException(from);
        }
    }
    return date;
}

After the parameters are converted, I call the service method from the controller which needs those java Date objects:

eventSearchService.findEventsByDate(start, end);

“How easy can it get?” I arrogantly ask myself.

Famous last words.

Unfortunately the conversion utility does something that I didn’t think about.

The service calls a Dao to do the database search where the database query looks something like:

select * from events where eventDateTime >= ? and eventDateTime <= ?

The two query parameters are the start and end variables passed to the findEventsByDate method of the eventSearchService.

Looking closely, I can see that the utility I used to handle the end point’s date parameters parses the strings into a java Date object computed to be an instant at the beginning of the day in the default time zone.

date = Date.from(LocalDate.parse(from).atStartOfDay(ZoneId.systemDefault()).toInstant());

I used atStartOfDay because java’s Date object includes both the day and time of day components of the date. But converting the ‘end’ date to a Date object using a time at the beginning of the day has an unexpected consequence: it excludes any events on the same day as the endDate, where the time of day greater than the beginning of the day at the default time zone.

In the database query if you want this query to work and return all events in January, and since its times for such events are stored in UTC, I really need to do two things differently:

1. make sure the startDate is at the beginning of the day, and endDate is at the end of the day.
2. the start and end dates used in this query should be the same time zone as the database column

I find it helpful to understand this issue by looking at all dates as epoch timestamps which translates date and time information into a simple number independent of time zones.

Looking at the epoch timestamps of a range for all of January 2020, if the times are expressed in Central Standard Time (GMT-6:00):

Start of month: 1577858400 (Wednesday, January 1, 2020 12:00:00 AM GMT-6:00)
End of month: 1580497199 (Friday, January 31, 2020 11:59:59 PM GMT-6:00)

But the end date that the parse utility produced is very different, because is creating a time at the beginning of the day in the default timezone. So the date range used is actually:

Start of month: 1577858400 (Wednesday, January 1, 2020 12:00:00 AM GMT-06:00)
End of month: 1580450400 (Friday, January 31, 2020 12:00:00 AM GMT-06:00)

Supposing you have an event that is scheduled at 8:00 PM CST on January 31, 2020. Its actual timestamp stored in the db would be:

Event DateTime: 1580522400 (Friday, January 31, 2020 8:00:00 PM GMT-06:00)

So the database query with: eventDateTime <= 1580450400 would miss the above event since 1580522400 is NOT less than or equal to 1580450400!

So what I could do is make sure the end date is computed to the end of the day. I could do so by doing the following with my string endDate (i.e. “2020–01–31”).

Date end = Date.from(LocalDate.parse(endDate)
            .atZone(ZoneId.systemDefault())
            .plus(1, DAYS)
            .minus(1, MILLIS)
            .toInstant());
}

Now I will call my service using my default timezone (CST), the epoch timestamps for end at the end of the day are a bit different.

Start of month: 1577858400 Wednesday, January 1, 2020 12:00:00 AM GMT-06:00
End of month: 1580536799 Friday, January 31, 2020 11:59:59 PM GMT-06:00

And the database query where clause would now be: eventDateTime >= 2577858400 and eventDateTime <= 1580536799 and would no longer miss the above event since 1580522400 (the epoch timestamp of the event mentioned previously) is now less than or equal to the new end date: 1580536799.