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. Thank you! Lewis -- 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/06e7014e-1bdf-4396-88b0-27b093b27c42n%40googlegroups.com.
