File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes MySQL - Date/Time and Timestamp Issues Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of The Software Craftsman this week in the Agile forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MySQL - Date/Time and Timestamp Issues" Watch "MySQL - Date/Time and Timestamp Issues" New topic

MySQL - Date/Time and Timestamp Issues

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3740

Everyone who's played around with JDBC and Date/Time values, knows mastering this can be a pain. For the most part you can do everything you want in Java using the getTime() field for milli-seconds past the epoch. Oh, you can use the Calendar utility methods for more complicated things, but getTime() often solves your basic needs and is useful in transferring information to/from a Date field.

In databases, MySQL in this case, I sometimes forget how much of a pain Date/Time can really be. For whatever reason (crazy...), databases seem to do much better if you split the date and time into separate fields, but in this case I wanted it as one field. I tried to use TIMESTAMP data type to store both a date and time, and found an oddity in MySQL: you cannot set this to null (if there is only one timestamp field). I tried setting it to null, and it started storing the current date/time. I tried adding a "DEFAULT NULL" tag to the create table statement, but that failed to execute.

Next in my quest, I used the more appropriate DATETIME data type but discovered if you call ps.setDate(...) in JDBC, it truncates the time, and only stores the date information. So that was no good. In the end, I used the DATETIME data type but used the ps.setTimestamp(...) setter in JDBC.

All of this is quite irritating to me...

First, why is MySQL overriding my null value (when I explicitly used ps.setNull(...)!). I understand what TIMESTAMP was meant for (not really what I was using it for), but if databases had better control of date/time values in the first place, TIMESTAMP wouldn't even be neccessary. Overall though, I don't like idea of the driver/database doing something I specifically told it not to do, its dangerous.

Second, why do I need to call ps.setTimestamp(...) for a DATETIME value? Shouldn't ps.setDate(...) work? Again, I'm not completely surprised, just annoyed at remembering how ridiculous database date/time programming is.
[ June 19, 2007: Message edited by: Scott Selikoff ]

[OCA 8 Book] [Blog]
Have you tried LearnNowOnline?
subject: MySQL - Date/Time and Timestamp Issues