Date for cf_last_resolved in history results should be UTC

RESOLVED FIXED

Status

()

RESOLVED FIXED
4 months ago
2 months ago

People

(Reporter: marco, Assigned: kohei)

Tracking

(Blocks: 1 bug)

Production

Firefox Tracking Flags

(Not tracked)

Details

Attachments

(1 attachment)

(Reporter)

Description

4 months ago
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?
(Reporter)

Comment 1

4 months ago
In the 'bug' endpoint, cf_last_resolved is always UTC.
(Reporter)

Updated

4 months ago
See Also: → bug 1509332
Posted 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.
(Reporter)

Comment 4

4 months ago
(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.
(Reporter)

Updated

3 months ago
See Also: → bug 1513952
(Reporter)

Updated

3 months ago
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
Last Resolved: 2 months ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.