On Fri, Feb 26, 2021 at 7:25 AM Michael Davis <[email protected]> wrote:
> 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 guess we'll just have to agree to disagree about that. From my review of the data you provided, the timezones returned appear to be either MST (Mountain Standard Time, -06:00) or MDT (Mountain Daylight Time, -07:00), depending on the datetime being converted. Again, that's normal Ruby behavior. > 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 > > The conversion_procs method does not exist for tinytds, so you should have gotten a NoMethodError for db.conversion_procs.clear if using the tinytds adapter. mssql and access aren't Sequel adapters. Maybe you are referring to ado/mssql and ado/access? The ado adapter wouldn't be affected by it because of how the timestamps are handled internally by it. Note that even if I changed things so that the ado adapter did not do any conversion, you would not get a string back, you would get a WIN32OLE object (WIN32OLE_TYPE:Field), not a string. After some testing, I found that I can modify the ado adapter code in a way that should provide backwards compatibility by default, but allow db.conversion_procs.clear to work. I'll probably make that change. > 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? > You probably want to use database-specific timestamp formatting functions if you don't like the result of casting to strings. You'll have to look at the documentation for SQL Server and Access to determine which functions will give you the output you want. 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/CADGZSSenDGd0Jyb%2BSXbSj7LUTdVHKTk%3DiqgSm1o61_AsHekoWg%40mail.gmail.com.
