How do I ensure a date is stored as UTC?

Question

I have a date column in my Oracle 11g RDBMS called changed_utc.

How do I make sure that the the changedUtc field from my Squeryl schema is stored as UTC instead of the local timezone of the database?

As much as I would like to I cannot change the column type to timestamp with timezone.

I very much like having my log timestamps in EST so I would rather not change the JVM default time zone to UTC either.

Answer 1

If the column type doesn't include the timezone, then the value won't be stored with the timezone of the database, it will be stored with no timezone. When it is read back, because it has no timezone, it's likely that it will be interpreted as being in the time zone of the server reading the value.

If what you want is to ensure that the date is always converted to UTC before it's written, and always interpreted as UTC when it is read, the easiest thing (assuming you are using Squeryl 0.9.6+) is to override the Date TEF (typed expression factory) in your class that extends PrimitiveTypeMode. See the example here: http://squeryl.org/0.9.6.html