Woes with ColdFusion, Java & Oracle's Timestamp with Timezone (tswtz)

here's a note i came across the other day from a project i worked on last year - man , this tswtz stuff was ugly - this project never went anywhere, so there was no resolution to this issue, but if i ever came across this again i would just store everything in the DB as regular datetimes in UTC. Done. anyway, this may be worth a read if you are struggling with it...

In the application, to preserve data integrity and ensure ALL date/times are for known timezones, the recommendation was made by the DBAs and the decision made by the architectural team (myself included) that ALL date/times in the database would use the data type timestamp with timezone (tswtz) - a unique and oracle specific data type.

The main technical problem that's been encountered with this decision is that the jdbc drivers handle the tswtz data type as a string. additionally, the data type cannot be validated in application code as a datetime or a timestamp as it too is just a string in java (or coldfusion). there may be some classes built to handle this data type but i dont know of any specifically for coldfusion.

In general, this means we need to build a custom validation routine to parse the strings and ensure they are properly formatted - not particularly complex, although the timezone offsets available to the system are driven by the JVM, so that portion of the validation should be dynamic. However this poses a problem, which JVM do you look at, the databases JVM or the app servers JVM? that turned out to be dang ugly since they didnt match and started giving different results. In general, however, the validation routine shouldnt be overly complex to build. but...

As it turns out, the jdbc drivers dont really handle the string data. for example, if the precision on the tswtz column is "0" (meaning decmals for seconds are not stored) the driver gives back the timestamp with an orphanded "." on the end of the seconds. if you turn around and try to use the value in a sql statement, in the original form in which the driver delivers it, the SQL statement blows up as not formatted properly for tswtz. so, in addition to validating data, we will need formatters routines to marshall the data as it comes out of the database. this poses new issues in that if the data displayed isn't what the drivers think is actually in the database, well row selection in sql may be iffy...

regardless, i set out to do a bunch of test around this issue to see what i could do to handle these issues. i wasnt even sure at this point what i needed to do as much as i began to experiment with what i could do...

Here were some of the ideas i explored. (basically by trying to handle the issues in the model, in the view or in the controller or some combination of the three.)

  • Modify the code generation tool to be smart about the forms for DB tables that have these tswtz columns and generate a set of custom form controls to manage displaying and formatting the data. drawbacks - most controller routines are looking for a single form element per column for a SQL statement - certainly this can be addressed but it adds complexity to handling form data generically.
  • Rather than re-format the display, just hijack the SQL in the data transport layer to "clean-up" the formatting on the way into and out of the database. basically layer the applications DAOs to automatically handle the formatting on the way in and the way out and make it transparent to the application code