Change Timezone Handling in GSheets

thorrak / tiltbridge

Tilt Hydrometer to WiFi Bridge

http://www.tiltbridge.com/

Other

61 stars 27 forks source link

Change Timezone Handling in GSheets #163

Open lbussy opened 3 years ago

lbussy commented 3 years ago

The GSheet sender function currently tries to calculate the local time based on an arbitrary offset the user enters (if they remember to do so. This results in a few unintended effects:

Changes entered mid-brew will render oddly depending on the offset change
DST is not taken into effect automatically
Timezones which are not in one-hour offsets are not able to be set

And of course, we are using a computer and we should be able to remove the need for a user to maintain this.

Ideas:

When GSheets are set up (since there are manual steps anyway), instruct the user to set the local timezone.
Store all data in GMT
Calculate the local time based on the timesheet's settings

It seems like it should be trivial (and a bit of a documentation thing) but I have zero experience changing these GSheets so I'm just making notes for now in case someone has the ability to do this. The controller side is easy enough and would need to be coordinated with the GSheet change.

pletch commented 3 years ago

The Brewstatus send handler currently uses this TZoffset value as well.

With respect to handling via Google sheets, the new beer sheet picks up the time zone to be the same as configured in the master template sheet. The user could make a local copy of the master template pointed at on the Tilt Cloud App for Tiltbridge settings sheet, update the timezone and locale setting to their preference, and then modify their local copy of the Tilt Cloud App for Tiltbridge to point to the local master template. Of course, they would then have to be reminded to recopy and modify the master template any time a new version is released.

SillyHats commented 3 years ago

Maybe this could help keep the local time updated automatically https://timezonedb.com/api

I saw it was implemented for a similar reason as the discussion here on https://github.com/Qrome/marquee-scroller

lbussy commented 3 years ago

We're currently trying to not care about the time - since the controller doesn't need to know and external systems (including a user desktop) already know what time it is. I think John had some ideas for this within the sheets.

SillyHats commented 3 years ago

If local time isn't important that help keep things simpler. It seemed like one of the goals was to eliminate the need for manual TZoffset adjustment and the associated hiccups. I am also a fan of computers not requiring manual DST adjustments since, as you mentioned above, we are using a computer.

https://timezonedb.com/references/get-time-zone Get local time, GMT offset, DST of a place by latitude & longitude, zone name, abbreviation, city name, or IP address.

SillyHats commented 3 years ago

I saw this and thought it might be another option for handling TZoffset automatically https://github.com/esp8266/Arduino/blob/master/cores/esp8266/TZ.h