Problem with Ruby Sequel retrieval of datetimes with daylights saving time

Question

The Ruby Sequel gem allows retrieval of datetimes with either no timezone conversion, conversion to local timezone or conversion to timezone. In all three cases we get unusable results at the conversion to daylights savings in a situation where timezone settings on the windows client OS clashes with timezone set in Ruby. For example:

require 'sequel'
require_relative './ojdbc8.jar'

username = 'username'
password = 'password'
host = 'host'
port = 1521
sid = 'ORCL'

connection_string = "jdbc:oracle:thin:#{username}/#{password}@#{host}:#{port}:#{sid}"
puts "connection_string: #{connection_string}"
ENV['TZ'] = 'Etc/GMT-10'
# ENV['TZ'] = '+10:00'

db = Sequel.connect(connection_string)
db.drop_table :d
db.create_table :d do
    primary_key :id
    DateTime :DATETIME, unique: true, null: false
    Integer :HOUR, null: false
end

# Using Time with TZ set to GMT+10
d = db[:d]
(1..4).each do|hour|
    t = Time.new(2022, 10, 2, hour, 0, 0)
    puts "time: #{t}"
    d.insert(DATETIME: t, HOUR: hour)
end

sql = "SELECT * FROM d"

Sequel.default_timezone = :utc
dataset = db[sql]
puts "UTC timezone"
dataset.each do |row|
    puts "row: #{row}"
end

Sequel.default_timezone = :local
dataset = db[sql]
puts "Local timezone..."
dataset.each do |row|
    puts "row: #{row}"
end

Sequel.default_timezone = nil
dataset = db[sql]
puts "nil timezone (no conversion)..."
dataset.each do |row|
    puts "row: #{row}"
end

Running the above with Windows Timezone of (UTC+10) Canberra, Melbourne, Sydney and Adjust for Daylights Savings set to true will result in the below:

time: 2022-10-02 01:00:00 +1000
time: 2022-10-02 02:00:00 +1000
time: 2022-10-02 03:00:00 +1000
time: 2022-10-02 04:00:00 +1000
UTC timezone
row: {:id=>1, :datetime=>2022-10-02 01:00:00 UTC, :hour=>1}
row: {:id=>2, :datetime=>2022-10-02 03:00:00 UTC, :hour=>2}
row: {:id=>3, :datetime=>2022-10-02 03:00:00 UTC, :hour=>3}
row: {:id=>4, :datetime=>2022-10-02 04:00:00 UTC, :hour=>4}
Local timezone...
row: {:id=>1, :datetime=>2022-10-02 01:00:00 +1000, :hour=>1}
row: {:id=>2, :datetime=>2022-10-02 03:00:00 +1000, :hour=>2}
row: {:id=>3, :datetime=>2022-10-02 03:00:00 +1000, :hour=>3}
row: {:id=>4, :datetime=>2022-10-02 04:00:00 +1000, :hour=>4}
nil timezone (no conversion)...
row: {:id=>1, :datetime=>2022-10-02 01:00:00 +1000, :hour=>1}
row: {:id=>2, :datetime=>2022-10-02 03:00:00 +1000, :hour=>2}
row: {:id=>3, :datetime=>2022-10-02 03:00:00 +1000, :hour=>3}
row: {:id=>4, :datetime=>2022-10-02 04:00:00 +1000, :hour=>4}

Note that in all cases, the Time object returned for hours 2 and 3 is the same. Hour 2 is incorrect. Given that the TZ environment variable is set explicitly in the code, it would be desirable if the :local option for Sequel.default_timezone = :local used that TZ timezone instead using other information from windows. Note that if TZ variable is set to something not nil then Time on its own will honour that setting, over-riding the Windows setting.

The actual DATETIME column in the database has no timezone information.

I have also tried various options using the named_timezones extension but have the same problem as above. This has included using the DateTime class rather than the Time class.

The only solution that we have found has been to use a TO_CHAR() function around the DATETIME column to cast it to a string.

Has anyone else solved this problem?

---

The above problem occurs when using JRuby 9.4.0.0 and a JDBC connection to Oracle. I have used very similar code with MRI and SQLite and the problem as described does not occur. With MRI, default_timezone = nil or :utc works fine and sort of works if :local and named_timezone works fine with both Datetime and Time classes.