Compare date and time range from database with user input date and time

Question

I am trying to handle vehicle reservation(using JSP servlets). My database contain startdate, starttime, returndate, returntime. Now I insert new date range and new time range for a new vehicle reservation. I need query or algorithm weather new input is acceptable. (The date range and time range should not clash with the data which are already available) My Data base fields and some of its values:

Startdate       starttime       returndate      returntime
2013-12-10      10:00:00        2013-12-12      15:00:00
2013-12-16      09:00:00        2013-12-16      12:00:00
2013-12-16      12:15:00        2013-12-16      16:00:00

I would suggest taking a look at [JodaTime](http://www.joda.org/joda-time/), for [example](http://stackoverflow.com/questions/20677541/date-range-in-date-range/20678485#20678485) — MadProgrammer, Dec 28 '13 at 22:08
What database platform is this? Are date/time actually separate fields? Please show your table schema. — OldProgrammer, Dec 28 '13 at 22:09

score 0 · Accepted Answer · answered Dec 28 '13 at 22:15

If your date and times are in separate fields, do something like:

select *
  from tb_vehicle v
 where not v.id in (
    select r.vehicle_id
      from tb_reservation r
     where ((r.start_date < :input_end_date) ||
            (r.start_date = :input_end_date and r.start_time < :input_end_time))
       and
           ((r.end_date > :input_start_date) ||
            (r.end_date = :input_end_date and r.end_time > :input_start_time))
    )

Well this query returns all vehicles which are not reserved during :input_start_date - :input_start_time and :input_end_date - :input_end_time.

If you can combine date and time columns the query will be easier:

select *
  from tb_vehicle v
 where not v.id in (
    select r.vehicle_id
      from tb_reservation r
     where (r.start_time_stamp < :input_end_time_stamp)           
       and (r.end_time_stamp > :input_start_time_stamp))

Thank you for your response. It is working 90 percent. One of the option does not check by the query which you had already sent to me. My Data Base contains the date ranges and time ranges of particular vehicle no are: Vrno Startdate Returndate starttime returntime VR01 2013/12/09 2012/12/10 13:00:00 16:00:00 VR02 2013/12/14 2012/12/14 08:00:00 12:00:00 But when I enter these values particular vehicle should not be available. But this option is not work. Startdate Returndate starttime returntime 2013/12/10 2012/12/12 11:00:00 15:00:00 — Chinthaka Wijayawardhana, Dec 31 '13 at 19:12

score 0 · Answer 2 · answered Dec 28 '13 at 23:58

The simplest solution is to store your dates as longs (milliseconds), then your comparisons for before/after/between are simple plus/minus operations. But that won't account for timezones, that's a whole different issue you might want to research and understand prior to implementing too much code.

score 0 · Answer 3 · answered Dec 29 '13 at 02:33

Database

If you are using a good database with sophisticated date-time handling, such as Postgres, the database server may be able to help you by performing queries. Perhaps you are not using such a database, as you mention that date and time are separate fields. In a sophisticated database those would be a single date-time type.

Time Zone

Your question fails to address the issue of time zones. Generally best to specify a time zone rather than rely on defaults. For my example code below, I arbitrarily chose Kolkata India (formerly Calcutta) time zone with an offset of 5:30 ahead of UTC/GMT.

Use Joda-Time

If you want to do the work in Java, you definitely should avoid using the bundled java.util.Date/Calendar classes. Either use the Joda-Time library, or in Java 8, the new java.time.* classes (inspired by Joda-Time).

Joda-Time offers classes with handy methods for your very purpose: Interval, Duration, and Period. Java 8's java.time.* offers similar.

The Interval class holds a span of time defined by a pair of start-stop DateTime objects. The class offers a overlaps method to tell you if one interval coincides with another.

Example code

// © 2013 Basil Bourque. This source code may be used freely forever by anyone taking full responsibility for doing so.
// import org.joda.time.*;
// import org.joda.time.format.*;

DateTimeZone timeZone = DateTimeZone.forID( "Asia/Kolkata" );

Interval i1 = new Interval( new DateTime( "2013-12-10T10:00:00", timeZone ), new DateTime( "2013-12-12T15:00:00", timeZone ) );
Interval i2 = new Interval( new DateTime( "2013-12-16T09:00:00", timeZone ), new DateTime( "2013-12-16T12:00:00", timeZone ) );
Interval i3 = new Interval( new DateTime( "2013-12-16T12:15:00", timeZone ), new DateTime( "2013-12-16T16:00:00", timeZone ) );

Interval test1 = new Interval( new DateTime( "2011-01-01T10:00:00", timeZone ), new DateTime( "2011-01-07T10:00:00", timeZone ) ); // Note the year: 2011.
Interval test2 = new Interval( new DateTime( "2013-12-10T11:00:00", timeZone ), new DateTime( "2013-12-10T13:00:00", timeZone ) );

Dump to console…

System.out.println( "i1: " + i1 );
System.out.println( "i2: " + i2 );
System.out.println( "i3: " + i3 );
System.out.println( "test1: " + test1 );
System.out.println( "test2: " + test2 );
System.out.println( "test1 overlaps i1: " + test1.overlaps( i1 ) );
System.out.println( "test2 overlaps i1: " + test2.overlaps( i1 ) );

When run…

i1: 2013-12-10T10:00:00.000+05:30/2013-12-12T15:00:00.000+05:30
i2: 2013-12-16T09:00:00.000+05:30/2013-12-16T12:00:00.000+05:30
i3: 2013-12-16T12:15:00.000+05:30/2013-12-16T16:00:00.000+05:30
test1: 2011-01-01T10:00:00.000+05:30/2011-01-07T10:00:00.000+05:30
test2: 2013-12-10T11:00:00.000+05:30/2013-12-10T13:00:00.000+05:30
test1 overlaps i1: false
test2 overlaps i1: true

Use UTC

While my example uses a specific time zone, generally it is best if you use UTC/GMT (no time zone offset) in your business logic and storage/database. Translate to a time zone only for presentation to the user.

Converting is easy by specifying the built-in time zone constant: DateTimeZone.UTC

DateTime dateTimeInUtc = new DateTime( DateTimeZone.UTC ); 
// - or -
DateTime nowInKolkata = new DateTime( DateTimeZone.forID( "Asia/Kolkata" );
DateTime dateTimeConvertedToUtc = nowInKolkata.toDateTime( DateTimeZone.UTC );