On Tue, Sep 27, 2022 at 2:08 PM 'Charlie Pham' via sequel-talk < sequel-talk@googlegroups.com> wrote:
> Hello, > > I have a model that has a field `created_at` that is of type timestamp > without timezone in Postgres. All of the relevant timezone settings are set > to `:local`. We are in PST (-8) and PDT (-7). > > I am running into an issue that I believe stems from `pg_sequel` that > results in an ambiguous (during the hour where daylight savings ends) time > value in Ruby flip-flopping between the -7 and -8 timezones. > > Observe the behaviour here: > *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0700 * > Model[150890643].changed_at => 2022-02-21 08:22:37.146317 -0800 > *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800* > Model.first.changed_at => 2021-03-23 08:36:22.655691 -0700 > *Model**[132639966].changed_at > => 2021-11-07 01:35:44.443726 -0700* > > It appears that whether the -7 or -8 appears depends on the last > encountered value (see the previous lines). Repeating any of the bold lines > sequentially yields the same result, but running any of the non-bolded > lines immediately changes the bold lines' outputs to match the timezone of > the non-bolded lines. > > I believe the source of this is pg_sequel since patching the vanilla > `fetch_rows_set_cols` and `yield_hash_rows` results in consistent behaviour > (always -8) > *Model[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800* > Model[150890643].changed_at => 2022-02-21 08:22:37.146317 -0800 > *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800* > Model.first.changed_at => 2021-03-23 08:36:22.655691 -0700 > *Model**[132639966].changed_at => 2021-11-07 01:35:44.443726 -0800* > > I tried looking at the source code for handling timestamp columns in the > c code but can't figure it out. > > Best, > Charlie > Charlie, Are the underlying columns timestamp or timestamptz (timestamp with timezone)? If they are timestamp and not timestamptz, they are stored in local time, and local time uses daylight savings time, then the underlying values are ambiguous. Why exactly they flip-flop, I'm not sure, but in any case, the result would not be considered a bug as the underlying values are ambiguous. Is this issue specific to sequel_pg? If you run sequel without sequel_pg (NO_SEQUEL_PG in the environment will disable the use of sequel_pg), do you get consistent results? I'm not seeing anything specific in sequel_pg that would cause this, but maybe it is using a Ruby C-API that does this (rb_time_timespec_new maybe?). If you could submit a minimal self contained example showing this issue, that would significantly speed up debugging. Something like: DB.create_table(:foo){Integer :id; Time :t} ds = DB[:foo] ds.insert(1, '*2021-11-07 01:35:44.443726*') ds.insert(2, ' 2022-02-21 08:22:37.146317') p ds.where(id: 1).get(:t) p ds.where(id: 2).get(:t) p ds.where(id: 1).get(:t) That example doesn't show the issue in my environment, though: 2021-11-07 01:35:44 -0700 2022-02-21 08:22:37 -0800 2021-11-07 01:35:44 -0700 Does it show the issue in your environment? If not, hopefully you can modify it so that it does show the issue. 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 sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CADGZSScx71dETDt1zXkavLsz9%3DbUcZPuaweoPL5cutNyaZC-ng%40mail.gmail.com.