Your explanation about daylight savings would make since if all the dates converted in the same way meaning when I convert them to localtime, I get the same date back. One date comes back MST with the right time as expected and another comes back CST with right time but they are off by an hour off when I convert them to localtime.
I implemented db.conversion_procs.clear where db is my Sequel database connection and here are the results: - sqlite did exactly what I needed and it helped with another anomaly with floats - mssql, tinytds, and access did not change and I got the same results of a string with the same timezones as before where some dates are accurate and others are one hour off when converting to localtime I also implemented cast to string and here is the new sql (SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, CAST(BirthDate AS varchar(255)) AS 'BirthDate', CAST(HireDate AS varchar(255)) AS 'HireDate' FROM Employees) and it does not work any better, the inserted dates are "1952-02-19 14:32:25.342": - works great for sqlite but not needed since clear conversion_procs did the trick - for mssql and tinytds I get: "Feb 19 1952 2:32PM" - missing seconds and milliseconds, can I convert the CAST() to CONVERT(varchar(255), BirthDate, 126)? - for access (without the milliseconds) I get: "2/19/1952 2:32:25 PM" which should be workable however including milliseconds causes an exception: OLE error code:80040E07 in Microsoft Access Database Engine Data type mismatch in criteria expression. It doesn't look like either option is any better. Any other other suggestions I can try? I have a work around that I can live with as long as I don't try changing the datetimes to localtime(). Thanks, Michael On Thursday, February 25, 2021 at 9:15:00 AM UTC-7 Jeremy Evans 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 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/191b8e60-6191-4494-858c-26c0ae25e7a9n%40googlegroups.com.
