On Wed, Dec 2, 2020 at 10:01 AM J. Lewis Muir <[email protected]> wrote:
> On Monday, November 30, 2020 at 5:58:04 PM UTC-6 Jeremy Evans wrote:
>
>> In spite of the warning, does Sequel submit the correct SQL to the
>> database (time in America/Chicago time zone)?
>>
>
> Yes, it appears that Sequel submits the correct SQL to the DB for the
> insert. However, and maybe this is what you were expecting, Sequel does
> not return the correct value from the select.
>
> For example, I set the application machine's time zone to America/Chicago
> (currently -06:00) and the DB server machine's time zone
> to America/Los_Angeles (currently -08:00) and restarted the DB server just
> in case that's required, and then I ran the following on the application
> machine:
>
> ----
> DB.extension(:named_timezones)
> Sequel.datetime_class = Time
> DB.create_table!(:mysql2_time_zone_test_events) do
> primary_key :id
> datetime :start_time_dt
> timestamp :start_time_ts
> end
> events = DB[:mysql2_time_zone_test_events]
> Sequel.application_timezone = :utc
> DB.timezone = 'America/Los_Angeles'
> start_time = Time.parse('2020-12-01 08:00:00 -06:00').utc
> puts("insert_sql=#{events.insert_sql(start_time_dt: start_time)}")
> id = events.insert(start_time_dt: start_time)
> result = events.where(id: id).first
> puts("expected=#{start_time}")
> puts("actual=#{result[:start_time_dt].utc}")
> ----
>
> which prints the following:
>
> ----
> insert_sql=INSERT INTO `mysql2_time_zone_test_events` (`start_time_dt`)
> VALUES ('2020-12-01 06:00:00')
> expected=2020-12-01 14:00:00 UTC
> actual=2020-12-01 12:00:00 UTC
> ----
>
Unfortunately, there is no way for Sequel to fix this. When using the
mysql2 driver, it handles the timezone conversion and Sequel never sees the
original string sent by the database server, so it cannot handle the
conversion correctly.
I can fix the warning in Sequel, but unfortunately, that's all I can do.
If you want mysql2 to support named timezones for :database_timezone,
you'll have to file an issue with them or send them a pull request (
https://github.com/brianmario/mysql2/issues). Starting in Ruby 2.6 it
should be possible for mysql2 to use Ruby's timezone support to implement
the named timezone support.
Note that you can use the mysql adapter and it handles the conversion
correctly, since Sequel handles the type conversion in that case instead of
the driver. However, you'll have to patch the mysql gem to run on Ruby
2.4+. Here's an example of such a patch:
https://raw.githubusercontent.com/openbsd/ports/master/databases/ruby-mysql/patches/patch-ext_mysql_api_mysql_c
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSePYvDS7_TR1rB193D_N92xFdvVe3ayYXDf8oJXc1tQ9g%40mail.gmail.com.
