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

No Such Thing As ‘End Of Day’

The problem with your question and all the answers is the definition of the end of day. You cannot pin down the end of day, as there are infinite fractions of a second between the last second of the day and the first moment of the new day.

You may think you can compensate for that. One answer talked about java.sql.Date eliminating minutes and seconds, which is true - but you likely need the time as part of your values. Another answer talked about using three decimal places of a fraction of a second. But different databases save a different number of decimal places, so you are building in a fragile assumption. The new java.time.* classes in Java 8 use nanosecond time resolution, which means your app may have instances that represent moments happening after your three-digit decimal place limit.

Use New Day

The proper way to represent a span of time is to record:

• Moment when span starts
• Moment after span ends

You are missing the "after" part of that second item.

The stop time, the deadline in your case, should be the first moment of the new day. The valid period, before the expiration/deadline, includes all the infinitely fine moments happening up until that new day. By marking the Stopping point as the new day, you can write the logic of your app & queries as…

Is the moment in question GREATER THAN OR EQUAL to the Start AND is it LESS THAN the Stop?

Notice that the second part of that does not contain OR EQUAL TO. Now you are accounting for all fractions of a second between the last second of day and the first moment of the new day.

To illustrate, here is a diagram of how to define a week. This is taken from my answer to another question, Get the week start and end date given a current date and week start.

Joda-Time

In Java work, avoid using java.util.Date & Calendar classes. Instead use Joda-Time or the new java.time.* classes in Java 8 (inspired by Joda-Time).

First Moment

Do not assume the new day starts at 00:00:00. Some time zones start Daylight Saving Time (DST) at the stroke of midnight, moving the hands of the clock to something like 01:00. Joda-Time includes a method for this very purpose of determining the first moment of the new day: withTimeAtStartOfDay. That method is smart, handling anomalies such as DST beginning at midnight. The midnight-related methods in Joda-Time have been deprecated or no longer recommended.

DateTime tomorrow = new DateTime().plusDays( 1 ).withTimeAtStartOfDay();

Time Zone

Actually, a better practice is to always specify a time zone rather than rely on default. So I would use code more like this…

DateTime tomorrow = new DateTime( DateTimeZone.forId( "Europe/Paris" ) ).plusDays( 1 ).withTimeAtStartOfDay();

Or, if using UTC/GMT (no time zone offset)…

DateTime tomorrow = new DateTime( DateTimeZone.UTC ).plusDays( 1 ).withTimeAtStartOfDay();

DateTime ↔ Date

You can easily convert between Joda-Time and java.util.Date as needed for exchanging data with other classes. Pass a Date to Joda-Time constructor. Going the other way, call the toDate() method in Joda-Time.

To…

DateTime myDateTime = new DateTime ( myDate );

and fro…

java.util.Date myDate = myDateTime.toDate();

Answer 2

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.

Answer 3

The entire reason for java.sql.Date to exist is that it does not allow hours minutes and seconds (As defined by the ANSI SQL Date type, and pretty much universally ignored by RDMBS implementors).

It is simply impossible to put the time in the database as long as you use java.sql.Date. If you want time, you need to switch to java.sql.Timestamp.

If you're asking how to calculate the actual value of 'end of day', it's simplest to just go to the next day and subtract 1 millisecond.

Calendar cal = Calendar.getInstance();
cal.setTime(endDate);
cal.add(Calendar.DATE, 1);
java.sql.Timestamp endTime = new java.sql.Timestamp(cal.getTimeInMillis() -1L);

Answer 4

This is the code for timestamp datatype

Date d=new Date();
Timestamp t=new Timestamp(d.getTime());try{
PreparedStatement pt=con.prepareStatement("INSERT INTO survey_schedule (start_date) VALUES (?);");
pt.setTimestamp(1, start_date);

pt.executeUpdate();}
catch(Exception e)
{
    System.out.println(e);
}

This is an example that will store the date and time in the mysql database