How do I get a bot to set a timestamp in a specified timezone (accounting for daylight saving time)?

[nhcohen]

1. If I understand correctly, when the NOW() function is called by a bot, it returns the same value as UTCNOW(), because the bot is executed on the server and there is no user device to use as the time source. Is this correct?
2. If so, how do I get a bot (triggered by a data change) to set a field to the wall-clock time in a specified time zone? (I am using the termtimezone in the sense of the IANA tz database: a geographical area, a standard-time offset from UTC, and a set of rules for when daylight saving time starts and ends in a given year. Thus "Eastern Standard Time" is not a time zone, but "America/New_York" is.) All my users are in the same time zone, and I would like the timestamps displayed to the user to reflect the time they see on the wall clock. Calling UTCNOW() and adding a hard-coded timezone offset will not work, because the offset from UTC changes when daylight saving time starts and ends.

[dbaum]

---

@nhcohen wrote:

• If I understand correctly, when the NOW() function is called by a bot, it returns the same value as UTCNOW(), because the bot is executed on the server and there is no user device to use as the time source. Is this correct?

---

No--or, at least, not always. An automation triggered by a data change receives the time from the triggering user's device and applies that in expressions like NOW(). A scheduled automation indeed always uses UTC.

---

@nhcohen wrote:

how do I get a bot (triggered by a data change) to set a field to the wall-clock time in a specified time zone?

---

It might be simplest to always capture and store values using UTC, and then modify them when rendered for app users--e.g., create a virtual column that adds the device's current offset to the stored value and display for users the virtual column rather than the data source column.

It's probably also possible to use a Call a script task to get the current time from a designated time zone. I assume there's some API that provides that. 

[nhcohen]

In this case, the data change is not performed directly by the user, but by another bot:

user form submission  ⇒ add row to table T1 ⇒ trigger bot B1 ⇒ add row to table T2 ⇒ trigger bot B2

It seems that the original user context is not preserved through this chain, because when bot B2 calls NOW(), it is receiving a UTC datetime.

[Suvrutt_Gurjar]

Apart from valuable tip by @dbaum and @nhcohen latest post on his observation on UTC timestamp source, there is additional query in the post about day light saving.

I believe you will need to handle the daylight saving related logic yourself in the app. I think most clocking systems that we use do not take care of this angle of day light saving time changes. I believe that is so because the day light saving is not uniformly applied across different time zones and even dates of day light saving start and end differ in different time zones. In my geography for example we do not have daylight savings concept.  I mean the clocking systems do change and reset as per daylight saving time at that instant but when applied in app logic,  a time system will be unable to add or deduct hours for a date in future or past based on expected daylight saving correction.

Essentially , I believe any daylight savings correction logic we will need to apply at app creator end.  I have seen this daylight savings related questions  and associated issues getting posted in community around October end /November and again in March timeframe every year. I will share some relevant posts.

Solved: Daylight Savings messing up All Day event - Google Cloud Community

Wrong time in google calendar - Google Cloud Community

[dbaum]

---

@dbaum wrote:

adds the device's current offset to the stored value

---

FWIW, my assumption is that this technique will indeed account for DST adjustments since the device’s time already accounts for that. 

[Suvrutt_Gurjar]

Hi @dbaum , thanks for your insights. However, I am unsure about that. We can test that but I believe the typical error case is as follows 

This year (2022) the daylight saving starts  on 6th November in US. So if a user in that geography  is trying to set a date and time  for an event dated 10th November and the user is entering data on 3rd November, I believe the device will not take care of the daylight saving timing. I think device does not or cannot auto correct in future because the device has no way of knowing if the user is entering day light saving corrected time or not. So if the user enters 10 AM and the device will not adjust it to 9 AM or 11 AM as the case may be after daylight saving timing adjusts.

[dbaum]

Yes, it’s critical to think through, account for, and test all the relevant nuances. You’re correct that my technique may not be applicable to scenarios different from what I interpreted from the OP regarding an automation logging a time stamp in UTC:

---

@nhcohen wrote:

I would like the timestamps displayed to the user to reflect the time they see on the wall clock

---

[nhcohen]

---

@Suvrutt_Gurjar wrote:

I think most clocking systems that we use do not take care of this angle of day light saving time changes. I believe that is so because the day light saving is not uniformly applied across different time zones and even dates of day light saving start and end differ in different time zones.

---

That is the point of the IANA tz database. Once upon a time, politicians would vote to change a local daylight saving time rule at the last minute, and software developers would drop everything and frantically rush to ship updates to their users. Now the good folks at the Internet Corporation for Assigned Names and Numbers (ICANN) track all these changes and maintain a database of current and historical timezone data, including historical daylight-saving-time transition dates. Modern software uses libraries that base their timezone computations on this database. Here is a partial list of system libararies that provide daylight-saving-time-aware time services based on the IANA tz database, including libraries for C, C++, Go, Java, Perl, PHP, Python, Ruby, and Rust. Surely these services must be available on the AppSheet server.

It would be nice if AppSheet were to provide a function returning the current time in an arbitrary user-specified IANA time zone. (Note that the "time zones" supported by AppSheet for use in scheduled events are not time zones at all in the IANA sense, but offsets from UTC corresponding to standard time. Thus there is no way to specify a bot that will always run at 3:00am New York time, regardless of whether daylight saving time is currently in effect in New York.)

[Suvrutt_Gurjar]

Thank  you very much @nhcohen for insights into daylight changes aware time data bases. It is indeed a great option to be aware of while working on time based systems.

My suggestion to implement app logic from current options available in AppSheet.

---

@nhcohen wrote:

It would be nice if AppSheet were to provide a function returning the current time in an arbitrary user-specified IANA time zone.

---

Yes, in general it will be a very good thing if AppSheet implements it. I think the use case for implementation will depend how many app creators  want it and how easy  or complex it will be for AppSheet developers to implement it.

You may want to raise a feature idea request, even though not sure nowadays how those are given priority.