1509308 - Date for cf_last_resolved in history results should be UTC

Status: RESOLVED FIXED

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

Description

[Marco Castelluccio [:marco]]

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?

Comment 1

[Marco Castelluccio [:marco]]

In the 'bug' endpoint, cf_last_resolved is always UTC.

Comment 2

[Kohei Yoshino [:kohei]]

Attachment: GitHub Pull Request

Comment 3

[Kohei Yoshino [:kohei]]

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.

Comment 4

[Marco Castelluccio [:marco]]

(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?).

Comment 5

[Kohei Yoshino [:kohei]]

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.

Comment 6

[Kohei Yoshino [:kohei]]

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?

Comment 7

[Kohei Yoshino [:kohei]]

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

Comment 8

[David Lawrence [:dkl]]

Merged to master. Leaving open as reminder to run the fixup script after deployment.

Comment 9

[Kohei Yoshino [:kohei]]

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

Comment 10

[Dylan Hardison [:dylan] (he/him)]

We ran this

Comment 11

[Kohei Yoshino [:kohei]]

Ah okay! Both https://bugzilla.mozilla.org/rest/bug/1508617/history and https://bugzilla.mozilla.org/rest/bug/1162860/history in comment 0 look good now. Closing then.