• Post Reply Bookmark Topic Watch Topic
  • New Topic

MySQL - Date/Time and Timestamp Issues  RSS feed

Scott Selikoff
Posts: 4087
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!