On Thu, Nov 26, 2020 at 6:38 PM J. Lewis Muir <[email protected]> wrote:
> Hello! > > How can I correctly and portably read and write DATETIME and TIMESTAMP > values that at the application layer have a UTC offset, when the database > is MariaDB with the system time zone set to SYSTEM, and the time zone of > the machine that MariaDB runs on uses DST? > > I'm using the mysql2 adapter, but I could change that if need be. > > I was thinking that I could set the MariaDB session time zone > > https://mariadb.com/kb/en/time-zones/#session-time-zone > > I've seen Sequel::Timezones > > https://sequel.jeremyevans.net/rdoc/classes/Sequel/Timezones.html > > but to my reading those won't do that. But I could be wrong. > > Is there a portable way to set the session time zone with Sequel? > > In my case, I'd like to just set it to a UTC offset. For example, I'd > like Sequel to do the equivalent of the following in a MariaDB session if I > want to set the UTC offset to -6:00: > > SET time_zone = '-6:00'; > > The problem I'm trying to solve is that I can't change the DB schema, the > time zone of the DB is set to SYSTEM, and I (most likely) can't change that > either, and the time zone of the machine the DB server runs on uses DST, so > the UTC offset changes twice a year. I want to be able to read and write > DATETIME and TIMESTAMP values correctly in the DB at any time. So, for > example, if I write a Time object with a time zone with Sequel to the DB, I > want that to always work, and I want to always be able to retrieve that > correctly later. > > I think that setting the session time zone will make things work with one > exception (noted below). But if there's a better way, I'd love to hear it! > > For example, say I want to write the value > > 2020-03-08T01:59:59 -0600 > > as a DATETIME. I would do the following: > > 1. Use Sequel to set the session time zone to an offset of my choosing, in > this case, let's say -3:00. > 2. Convert the value to the -3:00 offset. > 3. Use Sequel to write the converted value to the DB. > > To read the value later, I just set the session time zone to an offset of > my choosing and then, after reading it with Sequel, convert it to the final > offset that I want. > > The one exception that I mentioned earlier is that in this scheme there's > no way to distinguish between the two possible times in the one hour window > of a DST "fall back." For example, at > > 2020-11-01T01:59:59 -0500 > > the next second is > > 2020-11-01T01:00:00 -0600 > > But the DB does not store the offset, so without it, I have no way to tell > the difference between, e.g., > > 2020-11-01T01:23:00 -0500 > > and > > 2020-11-01T01:23:00 -0600 > > I'm willing to live with this if there's no better way. In some cases, > there are things I know about the data that would allow me to deduce which > time zone offset it must be. But sadly, not always. > For UTC times in the application and local time in the database, where the database uses a different timezone than the local machine, you probably want to use the named_timezones extension: DB.extension :named_timezones Sequel.datetime_class = Time # unless you want DateTime Sequel.application_timezone = :utc DB.timezone = 'America/Chicago' t = Time.now # => 2020-11-26 19:54:18.473257155 -0800 DB.get(Sequel.cast(t, Time)) # SELECT CAST('2020-11-26 21:54:18.473257-0600' AS timestamptz) AS "v" LIMIT 1 # => 2020-11-27 03:54:18.473257 UTC This machine is running on Pacific Standard time (hence the -800), but literalizes times into the database at -600, and values retrieved from the database are converted to UTC. This is also running on PostgreSQL with the timestamptz extension, and not on MySQL, hopefully the results with MySQL/MariaDB are similar. 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/CADGZSSeQxvOsSGGWkD0QACZ-nrk82wSQSS3fQ7mQW%3DD2kG%3DwMA%40mail.gmail.com.
