java - Adding hours, minutes, seconds to SQL Date

Question

Good Day!

I have a problem with SQL Dates. I think the main problem is on the perspective of time. In my program, I have a start and end date, say for example 2014-01-08 and 2014-01-10. In the datebase, I used datetime so the dates are stored as 2014-01-08 00:00:00 and 2014-01-10 00:00:00 respectively.

If you are a user and today is the end date, you would assume that the deadline is up to midnight. However, that is not the case because the hours, minutes, and seconds are set to 0.

I would like to ask, what is the best way to add the hours, minutes, and seconds so that the end date is up to midnight.

PS. I am using Java Sql Date.

INSERT INTO survey_schedule (start_date, end_date) VALUES (startDate, endDate);

startDate and endDate are java.sql.date types

Answer 1

As mentioned that the date stored as 2013-01-10 00:00:00 is to be converted to 2013-01-10 23:59:59.999 and then take that as end date.

MySql

DATE_ADD()

When you query your date time field, you can advance your endtime by one day as follows

DATE_ADD('your_datetime_field', INTERVAL 1 DAY)

OR

Java code

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// rs is ResultSet reference
long dt = rs.getTimestamp("your_datetime_field").getTime();
// pick calendar instance and set time
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(dt);
// this will print `2013-01-10 00:00:00`
System.out.println(sdf.format(calendar.getTime()));
// advance the date by 1 day
calendar.add(Calendar.Date, 1);
// this is print `2013-01-11 00:00:00`
System.out.println(sdf.format(calendar.getTime()));

Now, you can compare with this Calendar object as well.

Hope this helps.