Closed Bug 1509308 Opened 2 years ago Closed 2 years ago

Date for cf_last_resolved in history results should be UTC

Categories

(bugzilla.mozilla.org :: API, defect)

Production
defect
Not set
normal

Tracking

()

RESOLVED FIXED

People

(Reporter: marco, Assigned: kohei.yoshino)

References

(Blocks 1 open bug)

Details

Attachments

(1 file)

45 bytes, text/x-github-pull-request
Details | Review
See for example in https://bugzilla.mozilla.org/rest/bug/1508617/history and https://bugzilla.mozilla.org/rest/bug/1162860/history.

While the `when` field is in UTC, the `removed` or `added` can be in different timezones (in the examples, GMT the first, US/Pacific the second).

Would it be possible to always have UTC dates?
In the 'bug' endpoint, cf_last_resolved is always UTC.
See Also: → 1509332
Attached file GitHub Pull Request
Assignee: nobody → kohei.yoshino
Status: NEW → ASSIGNED
Nah, my PR just fixes the format, not the timezone issue :( The older PST/PDT timestamps logged before the UTC migration (Bug 232193) have to be converted with a script.
(In reply to Kohei Yoshino [:kohei] (Bugzilla UX) (FxSiteCompat) from comment #3)
> Nah, my PR just fixes the format, not the timezone issue :( The older
> PST/PDT timestamps logged before the UTC migration (Bug 232193) have to be
> converted with a script.

Unfortunately it looks like the timestamps in the history are not always PST/PDT, but they can also be in other timezones (maybe it's the timezone the user had set at the time?).
Then it’s harder to fix. Dig into the audit_log table to check the user’s timezone setting whenever each timestamp is logged, then convert it to UTC accordingly.
Attachment #9027033 - Attachment is obsolete: true
I just checked the audit_log table in my local VM database but user preference changes were not logged. Then it’s impossible to solve the issue, eh?
Assignee: kohei.yoshino → nobody
Status: ASSIGNED → NEW
Comment on attachment 9027033 [details] [review]
GitHub Pull Request

It turns out that the `bug_when` and `added` columns in the `bugs_activity` table share the same timestamp [1], but the latter wasn’t converted during the UTC migration because it’s not a datetime column. So we can just copy `bug_when` to `added`, and copy the last `added` timestamp to `removed`. Added a conversion script to my PR.

[1] https://github.com/mozilla-bteam/bmo/blob/master/extensions/LastResolved/Extension.pm#L86-L87
Attachment #9027033 - Attachment is obsolete: false
Assignee: nobody → kohei.yoshino
Status: NEW → ASSIGNED
Merged to master. Leaving open as reminder to run the fixup script after deployment.
See Also: → 1513952
Blocks: 1513955

So who can run the script? It’s located at extensions/LastResolved/bin/bug-1509308.pl. Should be tested on stage first.

Flags: needinfo?(dylan)

We ran this

Flags: needinfo?(dylan)
Status: ASSIGNED → RESOLVED
Closed: 2 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.