Java sql.date 秒,向SQL Date添加小时，分钟，秒

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

解决方案

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

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.