Check date fall between two date : time stamp (milliseconds)

Question

I have start_time and end_time column with milliseconds values in database.

start_time > Wed Feb 14 2018 11:00:00(real date) = 1518586200000(database value)

Now I want all record for particular day with duration.

Ex. Date : 14th Feb 2018

Case 1 :

Start Time : 14/02/2018 11:00 AM
End Time : 14/02/2018 11:30 AM
Result : Appear (30 min)

Case 2 :

Start Time : 13/02/2018 11:00 AM (start date change)
End Time : 14/02/2018 11:30 AM
Result : Appear (11 hour 30 min)

Case 3 :

Start Time : 14/02/2018 11:00 AM
End Time : 15/02/2018 11:30 AM (end date change)
Result : Appear (13 hour)

Case 4 :

Start Time : 13/02/2018 11:00 AM (start date change)
End Time : 16/02/2018 11:30 AM (end date change)
Result : Appear (24 hour)

Is it possible to fetch all record using sqlite query?

Note: I don't want to compare current time, I want date wise all duration.

Answer 1

Using

  input date = 14/02/2018;

Use the following variables beginning_of_day and end_of_day;

  // beginning_of_day indicates first hour of input_date
  beginning_of_day = 14/02/2018 00:00 a.m. // in millisecond format
  // end_of_day indicates end hour of input_date (or) the first hour of next valid date of input_date
  end_of_day = 15/02/2018 00:00 a.m.  // in millisecond format

Then you can use the following where clause;

WHERE NOT ((start_time<beginning_of_day AND end_time<beginning_of_day) OR (start_time>end_of_day AND end_time>end_of_day))

Answer 2

First, converting milliseconds since the Unix epoch to date and time depends on time zone. The sample value you give corresponds to Feb 14 at 5:30 AM in UTC. Since you translate it to 11 AM, it would seem your time zone is at offset +05:30 from UTC, Lemme guess, India or Sri Lanka?

Second, I don’t know whether this is possible in SQLite. It certainly is in Java, and not difficult when you know how. The following method will do the job;

public static ZoneId zone = ZoneId.of("Asia/Colombo");

public static Duration dateAppearingBetweenTimestamps(LocalDate date, long start, long end) {
    if (end < start) {
        throw new IllegalArgumentException("end must be on or after start");
    }
    Instant startOfDay = date.atStartOfDay(zone).toInstant();
    Instant startOfNextDay = date.plusDays(1).atStartOfDay(zone).toInstant();
    Instant startTime = Instant.ofEpochMilli(start);
    Instant endTime = Instant.ofEpochMilli(end);
    if (endTime.isBefore(startOfDay) || startTime.isAfter(startOfNextDay)) {
        return Duration.ZERO;
    }
    if (startTime.isBefore(startOfDay)) {
        startTime = startOfDay;
    }
    if (endTime.isAfter(startOfNextDay)) {
        endTime = startOfNextDay;
    }
    return Duration.between(startTime, endTime);
}

For demonstration I cooked this little auxiliary method:

public static void demo(String start, String end) {
    long startTimestamp
            = LocalDateTime.parse(start).atZone(zone).toInstant().toEpochMilli();
    long endTimestamp
            = LocalDateTime.parse(end).atZone(zone).toInstant().toEpochMilli();
    Duration result = dateAppearingBetweenTimestamps(
            LocalDate.of(2018, Month.FEBRUARY, 14), startTimestamp, endTimestamp);
    System.out.format("%13d %16s %14d %16s = %2d hours %2d minutes%n", 
            startTimestamp, start, endTimestamp, end, result.toHours(), result.toMinutesPart());
}

I called it with the 4 cases from your question:

    demo("2018-02-14T11:00", "2018-02-14T11:30");
    demo("2018-02-13T11:00", "2018-02-14T11:30");
    demo("2018-02-14T11:00", "2018-02-15T11:30");
    demo("2018-02-13T11:00", "2018-02-16T11:30");

This printed:

1518586200000 2018-02-14T11:00  1518588000000 2018-02-14T11:30 =  0 hours 30 minutes
1518499800000 2018-02-13T11:00  1518588000000 2018-02-14T11:30 = 11 hours 30 minutes
1518586200000 2018-02-14T11:00  1518674400000 2018-02-15T11:30 = 13 hours  0 minutes
1518499800000 2018-02-13T11:00  1518760800000 2018-02-16T11:30 = 24 hours  0 minutes

In all cases the output hours and minutes agree with what you expected.

I am using and recommending java.time, the modern Java date and time API, and its Duration class. Can you use java.time on Android? Yes you can, it just requires at least Java 6.

• In Java 8 and later and on modern Android devices java.time comes built-in.
• In Java 6 and 7 get the ThreeTen Backport, the backport of the new classes (ThreeTen for JSR 310, where the modern API was first described).
• On older Android, use the Android edition of ThreeTen Backport. It’s called ThreeTenABP. Make sure you import the date and time classes from package org.threeten.bp and subpackages.

The toMinutesPart method I used in the demonstration was only introduced in Java 9, though. In earlier Java and in the backports you can still use toMinutes() to get the total number of minutes in the Duraiotn, and Duration.toString() will tell you the hours and minutes in ISO 8601 format, for example PT11H30M for 11 hours 30 minutes.

Links

• Oracle tutorial: Date Time, explaining how to use java.time.
• ThreeTen Backport project
• ThreeTenABP, Android edition of ThreeTen Backport
• Question: How to use ThreeTenABP in Android Project, with a very thorough explanation.
• Java Specification Request (JSR) 310.