aspose file tools*
The moose likes JDBC 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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL - Date/Time and Timestamp Issues" Watch "MySQL - Date/Time and Timestamp Issues" New topic
Author

MySQL - Date/Time and Timestamp Issues

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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 ]

My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL - Date/Time and Timestamp Issues