1

I have created an empty Clojure application where I read and write a date field to and from a database.

The type of the column in MYSQL appears as DATETIME and in the application, I use the JDBC driver and clj-time to operate with dates.

I have realized that whenever I operate over the field, the following happens:

; When inserting

(insert-record db {:date-field (t/now)})

; (t/now), in the application, returns the date in UTC.
; In the database column, the date appears shifted (+2 hours) 
; to match my local timezone

So, it leaves the app as 2018-04-11T04:58:38.137Z

Appears in the DB as 2018-04-11 06:58:38

; When reading a field

(read-record db {:id 123}))))

; This returns a record that contains a java.sql.Date.
; The date comes back to the application shifted (-2 hours), 
; assuming that was stored in my local time zone and trying to 
; convert the record to UTC.

Value in the DB: 2018-04-03 00:00:00

Value received in the app: 2018-04-02 22:00:00

According to the Daylight saving time and time zone best practices question, you want "whenever you are referring to an exact moment in time, persist the time according to a unified standard that is not affected by daylight savings", so UTC.

This would happen to work like this on my production server, as it has configured UTC as timezone, but to make the experience more consistent, I would like to get the same behavior in DEV.

Also, as the correctness of the records is important, I find necessary to seal this behaviour and make sure that, regardless of the timezone configured in the machine, the records are persisted in UTC and therefore, retrieved without applying any conversion (unless I need it, where I would manually convert to the user timezone).

The whole idea seems reasonable, but I haven't found much information. I wonder if I'm looking at the issue from the wrong perspective or missing something bigger.

  • Who's responsible for the shift?. HugSQL? the JDBC driver?.

  • How can I modify the code so inserting fields the UTC application date is stored as UTC in the DB.

  • How can I modify the code so no conversion is made when reading fields?

  • If the changes had to be applied to a bigger codebase, can this configuration be made so it doesn't affect other queries?

EDIT

@Mark Rotteveel linked this question. Apparently, there is a calendar parameter in the setTimestamp function that allows you to specify the timezone. Can I use it in my context?.

Is it reasonable to say that it should be a way to explicitly set the timezone at the application level that is kept by HugSQL or JDBC?. I was thinking, if this usually relies on the JVM timezone, wouldn't a lot of applications break if the timezone of the server changes by mistake?.

Jacob
  • 1,886
  • 2
  • 25
  • 40
  • 1
    What is the timezone of the database server, and what is the timezone of the machine running the application. The JDBC specification requires that times are stored as the local time of the machine running the JVM. See [Is java.sql.Timestamp timezone specific?](https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific) – Mark Rotteveel Apr 11 '18 at 07:43
  • Thanks Mark!. In DEV both app and db are in CEST (+2 over UTC these days). In PROD both app and db are under UTC. Let me edit the post taking your answer into account. – Jacob Apr 11 '18 at 10:30
  • Are you using clj-time's protocol extensions for clojure.java.jdbc? https://clj-time.github.io/clj-time/doc/clj-time.jdbc.html – Curtis Summers Apr 11 '18 at 12:16
  • Good point @CurtisSummers, I'm using them indeed. Otherwise, I think the insert would be failing. That namespace appears imported in my application. – Jacob Apr 11 '18 at 12:53

0 Answers0