I believe the issue is on the datetime creation end rather than the parsing end. In Python, there are two types of datetime objects: naive and aware. For context: https://docs.python.org/3/library/datetime.html#aware-and-naive-objects. The main problem is that we're storing naive datetimes in our database, i.e. datetimes without a timezone.
When we use unix timestamps, we're in the clear because a unix timestamp is consistent across timezones, by definition. However, when we store events in a SQL database, we do the following:
I believe the culprit is: datetime.datetime.fromtimestamp(event.timestamp), which generates a naive timestamp using the user's timezone. The moment we do this, we lose any relative time information we have because this datetime doesn't have a timezone. I think we should pass fromtimestamp a tz=timezone.utc argument, and when we the date time back out it out converting it back to a unix timestamp using datetime_as_float should work correctly without any changes.
@sashank plz see the version tags in the runs. Both runs were started around 3:20PM PST, which is 22:20PM UTC)
with this change,
however, changing EPOCH = datetime.datetime.utcfromtimestamp(0) to EPOCH = datetime.datetime.fromtimestamp(0) gave:
What I could do is to add a condition to check if the dt has timezone in datetime_as_float, and then adjust the EPOCH to either local or utc accordingly.
However, the TIMESTAMP type in sqlite (https://dagster.phacility.com/D2295?vs=on&id=10760#change-Sa1P0Q52npBW) doesn't seem to have timezone info with it, even though we inserted the datetime with timezone in utc_datetime_from_timestamp(event.timestamp) (#56 of this diff).
The reason is, I believe, sqlite doesn't have a DATETIME type and it only has TIMESTAMP type and a DATETIME func (which allows you to tell it which timezone you want to convert the TIMESTAMP to). So it will convert all datetime obj in python through sqlalchemy to timestamp (i.e. stored everything without timezone). Meaning, although we give python datetime with timezone to sqlite, it will lose the timezone info in its backend.
So, unfortunately, with sqlite we couldn't tell the timezone to make this backwards compatible.