#at(time: <?>) returning nil
somazx opened this issue · 8 comments
Nice work on this gem - I love how it uses triggers/stored procedures and json columns.
I am struggling with the at(time: 1.hours.ago)
instance method not working as I expected.
object.at(time: 1.hour.ago)
returns nil, but I've been making changes to the object multiple times in the past 8 hours.
I'm wondering if it isn't a timezone issue?
1.hour.ago is ActiveSupport::TimeWithZone
instance with time zone set to MDT.
And here is the log_data for the record:
created_at: Mon, 28 Jun 2021 16:13:41 MDT -06:00,
updated_at: Thu, 08 Jul 2021 22:07:43 MDT -06:00,
log_data:
#<Logidze::History:0x000055a6cc488758
@data=
{"h"=>
[{"c"=>
{"id"=>168,
"title"=>"Competeny with custom fields",
"bad_text"=>"PfffF",
"form_type"=>"competency",
"good_text"=>"Cool",
"company_id"=>nil,
"created_at"=>"2021-06-28T22:13:41.342691",
"deleted_at"=>nil,
"updated_at"=>"2021-07-09T00:02:35.032324"},
"v"=>1,
"ts"=>1625814155032},
{"c"=>{"bad_text"=>"No", "good_text"=>"Yes", "updated_at"=>"2021-07-09 00:05:10.996913"}, "v"=>2, "ts"=>1625789110997},
{"c"=>{"title"=>"Competency with custom fields", "updated_at"=>"2021-07-09 00:07:47.454674"}, "v"=>3, "ts"=>1625789267455},
{"c"=>{"title"=>"Ninjas!", "updated_at"=>"2021-07-09 00:11:00.010293"}, "v"=>4, "ts"=>1625789460010},
{"c"=>{"title"=>"Foobar", "updated_at"=>"2021-07-09 00:58:50.865794"}, "v"=>5, "ts"=>1625792330866},
{"c"=>{"updated_at"=>"2021-07-09 03:53:26.390638"}, "v"=>6, "ts"=>1625802806391},
{"c"=>{"updated_at"=>"2021-07-09 04:07:43.097926"}, "v"=>7, "ts"=>1625803663098}],
"v"=>7}>>
Cheers!
Or, even more straight forward - the object above, with the same log_data:
object.at(time: object.created_at)
=> nil
object.at(time: object.updated_at)
=> nil
I rolled back all the migrations and reverted everything. Then I re-did the install/setup, and now it works.
This time I used the --backfill generator flag instead of the manual SQL update.
The SQL update:
logidze_snapshot(to_jsonb(t))
Generator:
I notice the generator used logidze_snapshot(to_jsonb(t), 'updated_at')
Would that be the issue? It looks like the statement_timestamp() functions would be used unless specifying a ts_column (updated_at) to logidze_snapshot. updated_at is a timestamp without time zone, but statement_timestamp returns a datetime with timezone ...
I dunno - this is making my brain melt lol =)
Hm, the ts for the first version (1625814155032) is greater than the last one (1625803663098); and it doesn't match the update_at
field:
select (extract(epoch from '2021-07-09T00:02:35.032324'::timestamp with time zone) * 1000)::bigint
#=> 1625788955032
Finally, the actual value is:
Time.zone.at(1625814155.032)
=> Fri, 09 Jul 2021 01:02:35 MDT -06:00
Any ideas where this datetime could came from?
I believe it came from my copying and pasting the example SQL in the readme to backfill my model after I forgot to pass the --backfill option to the generator:
UPDATE <my_table> as t
SET log_data = logidze_snapshot(to_jsonb(t))
Without specifying updated_at
column as 2nd argument, the logidze_snapshot function falls back to statement_timestamp()
.
Ok, thanks.
That explains the initial timestamp value.
And since the difference between timestamp and updated_at
for the first entry is about ~7h, I'd like to bring back the time zone hypothesis (MDT is 6h behind UTC, pretty close; say, you've run the migration in an hour after the record was created).
What is your setting for config.active_record.default_timezone
?
config.active_record.default_timezone
is set to MDT.
I came up with my own versioning solution for the problem I was considering logidze for - largely for legacy reasons.
config.active_record.default_timezone is set to MDT.
Oh, that’s the answer!
You backfilled data using PG current time (which is UTC), but further updates used updated_at
, which is MDT.
And that’s why re-backfilling with updated_at
fixed that.
We must add mention this caveat in the docs.