0

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.

Gabriel
  • 1,679
  • 3
  • 16
  • 37
  • Does "date" have a utc offset? at the very end, ddmmyyyy hh:mm:ssss -06:00 (this last -6)? Or are they all guaranteed to be in the same zone if not? – Aaron Jan 13 '17 at 20:50

1 Answers1

0

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

Dave Whittaker
  • 3,102
  • 13
  • 14
  • Basically when I insert a squeryl object with a date field into Oracle I want to make sure the year month day and hour fields of the corresponding date column is set to UTC. I know that `date columns in Oracle do not track timezone, but I have documented the field as should be in UTC. My concern is for when this data is copied across databases or where the TZ of the read session differs from the TZ of the insert session. – Gabriel Jan 17 '17 at 15:55
  • So If the JVM TimeZone.defaultTimeZone is not UTC I would need to use TEF to convert to Timestamp object with an intentionally "wrong" time offset so that it arrives in Oracle as UTC? – Gabriel Jan 17 '17 at 21:05
  • I just found http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getTimestamp(int,%20java.util.Calendar) I can provide a TEF that hard codes the calendar to UTC I think. Lets see how far that gets me. Thanks for all you help. – Gabriel Jan 17 '17 at 21:18
  • A java.util.Date (if that's what you are using) doesn't include a timestamp. It's encoded as milliseconds since the epoch. Where I'd expect you are more likely to have to do conversion is on the date that is ready back. – Dave Whittaker Jan 17 '17 at 22:04
  • I suppose I would need to have some way to call (setTimestamp(int, ts, calendar))[https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setTimestamp(int,%20java.sql.Timestamp,%20java.util.Calendar)] on the preparedstatement in (DatabaseAdapter)[https://github.com/squeryl/squeryl/blob/0.9.7/src/main/scala/org/squeryl/internals/DatabaseAdapter.scala] – Gabriel Jan 17 '17 at 22:07
  • Would it make sense to add an API to TypedExpressionFactory to supply a function to run on the preparedStatement? Something like `def apply: (paramIndex: Int, ps: PreparedStatement)=>Unit = {/* type specific modification function of preparedStatement */} – Gabriel Jan 17 '17 at 22:24
  • `def applyToPSFn: (paramIndex: Int, ps: PreparedStatement) => Unit = {/* type specific modification function of preparedStatement */}` I probably should have done all this on the mailing list I guess. – Gabriel Jan 17 '17 at 22:31