1425055 - subsession_start_date not compatible with presto's `from_iso8601_timestamp`

Status: RESOLVED WONTFIX

(Data Platform and Tools :: General, defect, P3)

Description

[Anthony Miyaguchi [:amiyaguchi]]

The subsession start date (and creation date) will break the from_iso8601_timestamp function. The subsession start date contains a time zone designator "[+|-]hh" outside of the [-12, 12] range.[1]

[1] https://sql.telemetry.mozilla.org/queries/49895/source

Comment 1

[Georg Fritzsche [:gfritzsche]]

Timezones can be outside of the [-12, 12] range AFAIK:
https://en.wikipedia.org/wiki/Time_zone#List_of_UTC_offsets

Do you suspect a client issue here? Are there actual invalid timezones submitted?
Or do we need a presto fix?
Or dataset normalization as a workaround?

Comment 2

[Anthony Miyaguchi [:amiyaguchi]]

There are timezones out of those UTC offsets (+17, -19, etc). The workaround during query time is to use `date_parse(substr(___))`, but something in main_summary might be worthwhile to avoid the hassle involved with different date/time formats.

Comment 3

[Georg Fritzsche [:gfritzsche]]

These are probably timezones clients are legitly reporting (from a Telemetry perspective), so i would suggest that we fix them up for derived datasets if needed.
This would be good to document though for people that are interested in the real offsets clients see.

Comment 4

[Ryan Harter [:harter]]

Looks like the plan is to clean the data flowing into main_summary, so moving this bug to that component.