Thanks. Some surprises regarding daylight savings time but I really appreciate your suggestions to cast to a string or alter DB.conversation_procs, I had not considered either and I can work with these. This is a Northwind database and I am testing various databases with some tooling I am working on, otherwise, I would definitely change the datatype to a Date.
Thank you, Michael On Thu, Feb 25, 2021 at 9:15 AM Jeremy Evans <[email protected]> wrote: > On Thu, Feb 25, 2021 at 7:11 AM Michael Davis <[email protected]> > wrote: > >> I insert dates into a database, the column type is DateTime, and dates >> being inserted are strings with just date values (no time values and no >> timezone). I confirm in the database that the date values are correct and >> have no timezones. When I select the date fields all dates have 00:00:00 >> for the time as expected but some dates come back in CST and other dates >> come back in MST. This happens in mssql, tinytds, access, and sqlite. > > > When debugging this in sqlite, I see Time.localtime() being called with >> the correct date and time values and no timezone, however each Time value >> comes back randomly with CST or MST for the timezone. I was expecting >> dates all to comeback as MST which is my local timezone. >> >> Here are my questions: >> >> 1. is this a bug in Time.localtime? >> >> No. > >> >> 1. why does it sometimes pick CST and other times pick MST? The >> timezones seem to be consistent across databases and across runs so this >> does not appear to be random. >> >> This isn't a different time zone, this is the same time zone with > daylight savings time handling. This is normal Time behavior when parsing > times when the local timezone uses daylight savings time. > >> >> 1. can I control what happens when DateTime or Time values are read >> in as a string and converted to a Time value? >> >> Whether you can depends on the adapter. tinytds for example does its own > conversion, outside of Sequel. > >> >> 1. can I set the timezone to something specific? >> >> You can use the named_timezones extension to select a specific timezone. > Maybe you can find an appropriate timezone that doesn't use daylight > savings time, if you want all timezone offsets to be the same. > >> >> 1. can I disable the DateTime conversion all together? >> >> You could always cast the column to String, that should work on every > adapter. Without that, behavior is dependent on the adapter. > > >> Setting Sequel.default_timezone = :local does not change the outcome. >> > > This is expected as local is the default if Sequel.datetime_class is Time > (which is also the default). > > I prefer to just circumvent the DateTime conversions and let the values >> come back as Strings. Is that possible? I could not see an easy way to do >> then debugging through the issue. >> > > As mentioned above, you can cast the value to String. If you don't want > to do that, here's an example for the sqlite adapter: > > DB.create_table(:t) do > column :t, :DateTime > end > > DB[:t].insert(Time.now) > > DB[:t].get(:t) > # => 2021-02-25 08:10:49.241774 -0800 > > DB.conversion_procs.delete('datetime') > > DB[:t].get(:t) > # => "2021-02-25 08:10:49.241774" > > Note that the values in the database are actually dates and not > datetimes/timestamps. You should probably change the column type from > DateTime to date to reflect that. Then you would get Date objects back. > If you don't want to change the column type, one workaround for the sqlite > adpater is to make the datetime conversion the same as the date conversion. > > DB.conversion_procs['datetime'] = DB.conversion_procs['date'] > > DB[:t].get(:t) > # => #<Date: 2021-02-25 ((2459271j,0s,0n),+0s,2299161j)> > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/SAOvPRK8GEY/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sequel-talk/CADGZSSckC-xt5MMT-Gcm8ynYWBtcKLz8B0nhWQOac0H5rE64Ug%40mail.gmail.com > <https://groups.google.com/d/msgid/sequel-talk/CADGZSSckC-xt5MMT-Gcm8ynYWBtcKLz8B0nhWQOac0H5rE64Ug%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- 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/CALxMgNPHQ3XuDjr5fctfxR9kfySZi8gG%2BFvmxe-%3DR7ENnz9RAg%40mail.gmail.com.
