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

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 2

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 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

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 5

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.