Data timezone is always UTC

[BNNorman]

I know Mysql stores data as UTC but this can mask the actual time of day that something happens. In the Uk UTC is broadly the same as GMT so it's not an issue. However if this system were to be used globally the chart time scales would be affected.

Perhaps we need to include time-zone as an option on the URL for API calls - and maybe even browser time-zone for the map display?

I will raise the point with Robin and maybe add an extra tz field for devices.

Apparently, before doing an sql select issuing set time_zone='time zone' forces mysql to convert dates to the requested time zone.

I think this merits discussion.

[sbrl]

Ah, interesting point. We should definitely agree on something to do here.

Personally, I'm of the opinion that we should keep using UTC in the API, and change the representation in the user's browser to their current timezone, which we can fetch via Javascript.

[BNNorman]

There's DST to throw into the mix as well. Takes some thinking about.

[BNNorman]

Perhaps the charts X-axis should say Time (UTC) instead of just Time. The dbLoader would need to know if the time includes DST and correct it - that'll be one for me then. Still needs discussion, maybe this coming thursday.

[robinharris]

Can we clarify terminology - it is hard enough without adding confusion. I only refer to DST in the context of the USA time zones. I know it has been used in the UK to refer to BST sometimes. Just to keep things clear can we use UTC == GMT (either works for me) and BST. BST is GMT+1 (or UTC+1). Then drop DST?

[sbrl]

@BNNorman: It's actually much simpler than that. We don't actually need to care about daylight savings times, because we can do it all through the browser.

In JS, there's a method called .toLocaleString(), which translates a date into the user's local time zone, automatically. To this end, we can run the data points through this before rendering.

@robinharris:

• UTC - Universal Coordinated Time. We want to (and do, IIRC), store data in this format.
• GMT - Greenwhich Mean Time - not the same as UTC, but close enough for our purposes. See this article
• BST - British Summer Time. Definitely not the same as UTC or GMT. Basically +1 hours, and we use it in the summer time. We want data to display in this I suspect.
• DST - Daylight Savings Time. Not a time zone - actually an abbreviation.

[BNNorman]

Sorry, should have used lower case to signify dst isn't a time zone - but it is a flag to show that date-times are 1 hour ahead of UTC.

I did find this... When you insert a TIMESTAMP value into a table, MySQL converts it from your connection’s time zone to UTC for storage. When you query a TIMESTAMP value, MySQL converts the UTC value back to your connection’s time zone. Notice that this conversion does not take place for other temporal data types such as DATETIME.

The original code for dbLoader didn't pass in the offset. It used string formatting to ignore it. We actually use the timestamp column type.

I wonder what effect this will have. It may mean all data plotted is plotted as BST even when it was GMT as we transition to BST. What fun. Time to experiment.

[sbrl]

Ooooh, ewww. That would be a nasty bug if so. If possible, we want all communications everywhere to be in UTC, and only convert when displaying it to the user. That should avoid confusion between devices & servers.

Looks like I might have to investigate setting the timezone of the connection. I didn't know about that - thanks!

[BNNorman]

Mysql has a TZ_CONVERT(timestamp,timezone) function but it means storing the timezone in the database. We could bury our group heads in the sand and see what happens when we get into BST. I suspect there would be a 1 hour gap in the data.

Then at the other end of the year readings that appear to be duplicates. Imagine before the switch back to GMT levels are low, then the day after they are high. I think the chart would show levels oscillating between low and high within the same hour. Chuckle.

[sbrl]

It should store the timezone information as part of a DATETIME.

Either way I really think we should store data in the DB in UTC if we aren't already.

[BNNorman]

No it doesn't - I tried it.

It stores the same value as a timestamp which is converted to utc according to @@session.time_zone. timestamps are converted back, again also depending on @session.time_zone but datetime types are not.

With timestamps you can store values in the server's local time zone and read them back in a different timezone.

Yes we are storing in UTC. We will see what happens to the charts when we enter and leave BST.

I wonder what someone abroad looking at the charts will see. Will it appear to be their local time or ours?

For now I think we should close this and revisit if it is a problem.

[sbrl]

Ah, I see.

Sounds good to me! In that case, they should see it in UTC also.

Looks like we've still got some work to do with respect to date and time handling though. Ideally, it would be rather nice if we could store as a DATETIME without any conversion to a timestamp, to avoid loosing the timezone tag - just in case.