Class: Sequel::JDBC::Database

Inherits:
Object
  • Object
show all
Defined in:
common/db/sequel_mysql_timezone_workaround.rb

Instance Method Summary collapse

Instance Method Details

#timestamp_convert(r, i) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'common/db/sequel_mysql_timezone_workaround.rb', line 38

def timestamp_convert(r, i)
  if v = r.getTimestamp(i)
    # When serverTimezone=UTC, timestamps coming out of MySQL will return a correct
    # epoch time when `.getTime()` is called, but will otherwise be expressed in the
    # JVM's default timezone.  For example, if your timestamp was midnight UTC,
    # `timestamp.getHour()` might return 20 if you were in New York (and four hours
    # behind UTC).
    #
    # Sequel is configured to use the UTC timezone too, but
    # `to_application_timestamp` is expecting to get
    # year/month/day/hour/mins/seconds in UTC values, not in local time values.
    #
    # To make these things line up, get a Calendar instance corresponding to `v` in
    # the UTC timezone and then read off the UTC values.
    #
    # Fun note: you might think you could just set serverTimezone to match your
    # JVM's timezone and avoid this whole mess, and that's also the default if you
    # don't specify serverTimezone at all.  That gets into trouble around the DST
    # switch, though: your database ends up storing a UTC timestamp around 2am on
    # the second Sunday in March, which is valid for UTC but not valid for your
    # local timezone.  The MySQL driver tries to load it into a timezoned value, and
    # you get the dreaded:
    #
    #    Sequel::DatabaseError: Java::JavaSql::SQLException: HOUR_OF_DAY: 2 -> 3

    cal = java.util.Calendar.getInstance(java.util.TimeZone.getTimeZone("UTC"))
    cal.setTimeInMillis(v.get_time)

    to_application_timestamp([cal.get(java.util.Calendar::YEAR),
                              cal.get(java.util.Calendar::MONTH) + 1,
                              cal.get(java.util.Calendar::DATE),
                              cal.get(java.util.Calendar::HOUR_OF_DAY),
                              cal.get(java.util.Calendar::MINUTE),
                              cal.get(java.util.Calendar::SECOND),
                              cal.get(java.util.Calendar::MILLISECOND) * 1_000_000, # nanoseconds
                             ])
  end
end