On Wed, Dec 2, 2020 at 12:35 PM Jeremy Evans <[email protected]> wrote:
> 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. > Here's a fix for the warning: diff --git a/lib/sequel/adapters/mysql2.rb b/lib/sequel/adapters/mysql2.rb index bbcbae892..8d9b53282 100644 --- a/lib/sequel/adapters/mysql2.rb +++ b/lib/sequel/adapters/mysql2.rb @@ -128,12 +128,19 @@ module Sequel end end + case database_timezone = timezone + when :utc, :local + # nothing + else + database_timezone = nil + end + r = log_connection_yield((log_sql = opts[:log_sql]) ? sql + log_sql : sql, conn, args) do if stmt - conn.query_options.merge!(:cache_rows=>true, :database_timezone => timezone, :application_timezone => Sequel.application_timezone, :stream=>stream, :cast_booleans=>convert_tinyint_to_bool) + conn.query_options.merge!(:cache_rows=>true, :database_timezone => database_timezone, :application_timezone => Sequel.application_timezone, :stream=>stream, :cast_booleans=>convert_tinyint_to_bool) stmt.execute(*args) else - conn.query(sql, :database_timezone => timezone, :application_timezone => Sequel.application_timezone, :stream=>stream) + conn.query(sql, :database_timezone => database_timezone, :application_timezone => Sequel.application_timezone, :stream=>stream) end end if opts[:type] == :select After consideration, I don't think it's a good idea to hide the warning. The behavior isn't correct, and the warning alerts you to that. Plus if mysql2 does fix the issue later and allow other timezones, I'll have to revert the change. 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/CADGZSSfz2O%3D7ps0dmNDtO9fU42mYo_-B8umHDagb1XH_6%3DjXMQ%40mail.gmail.com.
