[UX/Data] Fields - Datetime - UTC Time Zone Input & Output Workarounds

Ideas & Features
1

While storing everything in UTC on the server is appreciated and logical, always adjusting datetime field input and output (display) to the user’s local device time zone is not so logical, and in fact quite problematic, particularly because the user’s local device time zone is client-side and therefore justifiably and understandably not available to Fibery formulas or automations.

While the final solution is going to be complex, the immediate need, with a temporary patch is to allow a datetime field to act just like any other field where a client-side change such as travelling between timezones, does not result in a difference between what the user inputs or sees and what is on the server. In other words, a datetime field that is not automatically adjusted to the user’s local device timezone, but instead is treated as raw UTC for both input and display, perhaps through an advanced option when defining the field, just like @njyo mentioned in:

The real-life business problem

An event coordinator in time zone A (UTC+3) creates an event that takes place in time zone B (UTC-5):

  1. She enters “18:00” meaning “18:00” at the event location (UTC-5).

  2. The system interprets the value in her current device time zone (UTC+3), and converts it to UTC based on her time zone i.e. 15:00 UTC, and stores it. Of course the correct value to store would be 23:00 UTC, which corresponds to 18:00 in the event’s time zone (UTC-5).

  3. She continues seeing the event time as “18:00” on her device as long as she does not travel out of her own time zone, thinking everything is fine.

  4. Another stakeholder in time zone C (UTC+1) views the event and sees “16:00”, which is based on the incorrect underlying UTC value of 15:00.

For input: current workaround

The nightmare procedural solution

Require event coordinators to manually calculate the difference between their current UTC offset and the event’s UTC offset before every single entry, convert the intended event time into their own device time zone and input that adjusted value.

Imagine this procedure whenever a user travels or their device time zone changes for any reason, including Daylight Saving Time (DST).

Both solutions below are currently not possible without output hacks (see “For output” section below), since the corresponding output is currently still be forced into the user’s local device time zone.

The all-UTC input procedural solution

All event times are entered in UTC, which still needs to be calculated by the user, but at least the user would be making a simpler calculation than in the previous solution

The ideal, temporary entity-timezone input procedural solution

Even better a clean procedural entity-time zone rule workaround with automation: all event times are entered in the event’s time zone, and an automation converts that input into the properly offset UTC by compensating for the event’s time zone offset, e.g. in our example above the user would enter “18:00” and the automation would calculate 18:00 - (-5) hours = 23:00, and store that as the UTC value.

For output: current workaround

After implementing the “nightmare procedural solution” for input, the server will at least have the correct UTC value stored.

However, stakeholders in different time zones will still see the event time adjusted to their own device time zone, which is not ideal for an event that takes place in a specific location.

Ideal formula workaround

The logical workaround to show the time in the event’s time own zone is to create a formula field that calculates the event’s local time by compensating for the stored UTC offset (events have a time zone field that relates to a time zones table) and outputs that adjusted datetime value, e.g. in our example above if the value were correctly stored as 23:00 UTC, the formula would get the event’s time zone offset of -5, and calculate 23:00 - 5 hours = 18:00, and would use that to output 18:00 as a datetime value.

However, since the output of a datetime field is always automatically adjusted to the user’s device time zone, this formula’s result would be meaningless unless viewed in UTC.

Current ToText() hack with significant limitations

The current hack is to wrap the formula in ToText(), to avoi the conversion into the user’s local device time zone, but this means the output is no longer a datetime value, and therefore cannot be used as a datetime in views, filters, other formulas, or automations.

Minimal feature to enable solutions above

Allow a datetime field to be marked as UTC-only, meaning:

  • Input is treated as in UTC, and stored on the server without any conversion based on the user’s device time zone.

  • Output displays the stored UTC value without conversion to the viewer’s device time zone.

This would immediately allow the ideal workaround for both input and output from above.

1 Like