Dates with no time or timezone component in Java/MySQL

Question

I need to be able to store a date (year/month/day) with no time component. It's an abstract concept of a date, such as a birthday - I need to represent a date in the year and not a particular instant in time.

I am using Java to parse the date from some input text, and need to store in a MySQL database. No matter what timezone the database, application, or any client is in, they should all see the same year/month/day.

My application will run on a machine with a different system timezone from the database server, and I don't have control over either. Does anyone have an elegant solution for ensuring I store the date correctly?

I can think of these solutions, neither of which seems very nice:

• Query my MySQL connection for its timezone and parse the input date in that timezone
• Process the date entirely as a string yyyy-MM-dd

Answer 1

java.time

This was indeed a problem with java.util date-time API until JSR-310 was incorporated in Java SE 8. The java.util.Date object is not a real date-time object like the modern date-time types; rather, it represents the number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT (or UTC). When you print an object of java.util.Date, its toString method returns the date-time in the JVM's timezone, calculated from this milliseconds value.

The problem that you have described was addressed with the modern date-time API^* where we have LocalDate that represents just a date. In the following sentence, the Oracle tutorial describes its purpose nicely:

For example, you might use a LocalDate object to represent a birth date, because most people observe their birthday on the same day, whether they are in their birth city or across the globe on the other side of the international date line.

A couple of pages later, it describes it again as follows:

A LocalDate represents a year-month-day in the ISO calendar and is useful for representing a date without a time. You might use a LocalDate to track a significant event, such as a birth date or wedding date.

Which datatype should I use for LocalDate?

It maps with DATE ANSI SQL type. The mapping of ANSI SQL types with java.time types have been depicted as follows in this Oracle's article:

ANSI SQL	Java SE 8
DATE	LocalDate
TIME	LocalTime
TIMESTAMP	LocalDateTime
TIME WITH TIMEZONE	OffsetTime
TIMESTAMP WITH TIMEZONE	OffsetDateTime

How to use it in JDBC?

Given below is a sample code to insert a LocalDate into columnfoo (which is of DATE type):

LocalDate localDate = LocalDate.now();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, localDate);
st.executeUpdate();
st.close();

Given below is a sample code to retrieve a LocalDate from columnfoo:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE <some condition>");
while (rs.next()) {
    // Assuming the column index of columnfoo is 1
    LocalDate localDate = rs.getObject(1, LocalDate.class));
    System.out.println(localDate);
}
rs.close();
st.close();

Learn more about the modern date-time API^* from Trail: Date Time.

---

^{* For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.}

Answer 2

You could zero out all time/timezone stuff:

public static Date truncateDate(Date date)
    {
        GregorianCalendar cal = getGregorianCalendar();
        cal.set(Calendar.ZONE_OFFSET, 0); // UTC
        cal.set(Calendar.DST_OFFSET, 0); // We don't want DST to get in the way.

        cal.setTime(date);
        cal.set(Calendar.MILLISECOND, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.HOUR, 0);
        cal.set(Calendar.AM_PM, Calendar.AM);

        return cal.getTime();
    }

Answer 3

I concluded that the best way in my current application (a simple utility using jdbc directly) was to insert directly as a string. For a bigger Hibernate app I might bother to write my own user type. Can't believe someone hasn't already solved this problem in some publicly available code though...

Answer 4

Couldn't you just use the MySQL DATE type in your table and then essentially use the formatted string in your insert statement? I'd think something like this would avoid any time zone adjustments.

INSERT INTO time_table(dt) VALUES('2008-12-31')

Answer 5

Since you specify that your server and db are in different timezones, it seems to me that you have a problem with interpretation. If you zero out the date, or store as MM-dd-YYYY, which amounts to the same thing, are you storing the server date or the db date? If it's 11:00 PM on the server and 1 AM the next day on the db, which date is "right"? When you look at the dates a year from now will you remember which machines timezone the dates are dependant on?

These considerations may be a bit of overkill. But why not just store the dates in GMT (zeroing out the seconds if you like)?

Answer 6

The class java.sql.Date exists exactly for this reason, unfortunately it is very inconvenient to use, as it simply extends java.util.Date and you manually have to set the time part to zero.