What is the best way to increment a Java.sql.date / time?

Question

Hello

I have an issue where I have a MySQL database storing dates and times in separate columns. However, in Java code I need to increment the resulting timestamp for a date and time from the database by minutes, hours or days and then update the respective columns in the database.

I'm currently using Java.sql.date and java.sql.time types in Java. Can anyone suggest the best method for this?

I can achieve the above by the following:

public static String addToDateTime(String timestampIn, String increment) {

    // Decompose timestamp.
    int year = Integer.parseInt(timestampIn.substring(0, 4));
    int month = Integer.parseInt(timestampIn.substring(5, 7));
    int day = Integer.parseInt(timestampIn.substring(8, 10));
    int hours = Integer.parseInt(timestampIn.substring(11, 13));
    int mins = Integer.parseInt(timestampIn.substring(14, 16));
    int secs = Integer.parseInt(timestampIn.substring(17, 19));

    Calendar calendar = new GregorianCalendar(year, month - 1, day, hours, mins, secs);

    // Increment timestamp.
    if (increment.equals("HOURLY")) {
        calendar.add(Calendar.HOUR, 1);
    }
    else if (increment.equals("DAILY")) {
        calendar.add(Calendar.HOUR, 24);
    }
    else  if (increment.equals("WEEKLY")) {
        calendar.add(Calendar.HOUR, 168);
    }
    else if (increment.equals("DO NOT POLL")) {

        // Do nothing.

    }

    // Compose new timestamp.
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String timestampOut = sdf.format(calendar.getTime());
    return timestampOut;
}

But would prefer something less primitive.

Thanks

Mr Morgan

Answer 1

You can use Joda time. Then, you can use DateTime's plusHours, plusDays, and plusWeeks. For parsing, there is DateTimeFormat and DateTimeFormatter. Something like:

DateTimeFormatter fmt = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss");
DateTime dt = fmt.parseDateTime(timestampin);

if (increment.equals("HOURLY")) {
    dt = dt.plusHours(1);
}
else if (increment.equals("DAILY")) {
    dt = dt.plusDays(1);
}
else  if (increment.equals("WEEKLY")) {
    dt = dt.plusWeeks(1);
}

String timestampOut = fmt.print(dt);

You can probably use something like:

DateTime dt = new DateMidnight(sqlDate).plus(Period.millis(sqlTime.getTime()));

That assumes the sql.Time represents the number of milliseconds since midnight.

Answer 2

Here's an optimization, assuming that the input is java.util.Date wherein you can just pass java.sql.Date and java.sql.Time in since they are just its subclasses.

public static String addToDateTime(Date date, Increment increment) throws ParseException {
    Calendar calendar = calendar.getInstance();
    calendar.clear();
    calendar.setTime(date);

    switch (increment) {
        case HOURLY: calendar.add(Calendar.HOUR, 1); break;
        case DAILY: calendar.add(Calendar.DATE, 1); break;
        case WEEKLY: calendar.add(Calendar.WEEK_OF_YEAR, 1); break;
        case DO_NOT_POLL: break;
    }

    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(calendar.getTime());
}

public enum Increment {
    HOURLY, DAILY, WEEKLY, DO_NOT_POLL;
}

which can be used as

String newTimestamp = addToDateTime(timestampIn, Increment.WEEKLY);

To learn more about the useful enum, check the Sun tutorial on the subject. However, as per Java 7 you should be able to switch on String.

---

I however strongly agree the JodaTime advice, here's an example:

public static String addToDateTime(Date date, Increment increment) {
    DateTime dt = new DateTime(date);

    switch (increment) {
        case HOURLY: dt = dt.plusHours(1); break;
        case DAILY: dt = dt.plusDays(1); break;
        case WEEKLY: dt = dt.plusWeeks(1); break;
        case DO_NOT_POLL: break;
    }

    return df.print(dt);
}

public enum Increment {
    HOURLY, DAILY, WEEKLY, DO_NOT_POLL;
}

The difference is admittedly not shocking, but it has more advantages as well.