Time zone madness, daylight saving, and Google Calendar events

1

I am building out a Google Calendar (GCal) sync for my team, where events from tables in Coda are updated to Google Calendar events, so that my team can subscribe and have on their phones, etc. My struggle is with time zones.

The formula for generating GCal events from Coda does not include a field for the time zone. Instead, the time of the event from Coda is assumed to be the time of the Doc (for me, Berlin CET). And the time zone of the generated event in GCal is Berlin. This is a problem.

To help, I’m using the Time Zone Magic pack (TZM) to provide a table of Time Zones – gratefully, I don’t have to code it all myself. I have tried to use TZM’s provided formula to change between time zones, but using it in a table of ~1000 rows the formula never finishes running, and coda slows to unusable for my team. This is a show-stopper, so I’m trying to deal with translating between time zones myself. I don’t have the dates for daylight saving time changes in the table, so I’m missing critical information to do the changes myself. From TZM, I have the current offset, the usual offset, and that’s it. I have no way to query whether a datetime occurs in one of the weird periods…

The event I’m thinking of is taking place in July 2025, when the time zones happen to be 7 hours apart. But right now, March 2025, the time zones are 6 hours apart. So if I say 9am in US Central, the time zones are right now 6 hours apart, the generated time is 3pm. The event gets added to GCal at 3pm, in CET (the Doc’s time zone). But the event will actually occur at 4pm CET in July – the GCal event is WRONG. This is unacceptable.

The two problems, simply stated:

  1. I am missing the ability to set time zones for GCal event from Coda via the pack.
  2. I don’t have a way of converting my datetimes between time zones en masse.

Thus, I cannot correctly express datetimes in GCal events. They’re wrong, and I don’t know which ones are wrong. Reliable solution of problem 2. would completely solve the problem. Solution of problem 1. partially solves it, but leaves time-zone switching within my docs still broken for events viewed around daylight saving time changes.

Has anyone dealt with the time zone problem in Coda? How?

2

I have a team in 7 different time zones, so I feel this. This week is driving me crazy with times zones and our rotating shift calendar.

I have used different approaches for this, none perfect. I’m just waking up, so apologies if this isn’t at all helpful or is not clear:

First, I find it easier if the table is set with UTC as the time zone.

Then I have a helper table with UTC offsets for each time zone.

I also have a user table that includes each user’s time zone and current utc ofset.

In the events table, I have a checkbox column that is checked off when daylight saving time begins and ends so that the formula columns can adjust the time as needed.

All of these adjustments have to happen in coda before creating the event time in g cal or it doesn’t seem to work.

1 Like
3

I appreciate your notes. I’ve spent some hours today again working on this problem, and wanted to share my updates with the Coda world.

I have given up on using Time Zone Magic to convert my times. I was really hoping it would solve my problem because it seems to be aware of daylight saving time for future events. Alas, it will not populate a few hundred rows in a table.

So, now back to square one it seems. How to make sure my datetimes are correct. I have calendar event rows in Coda with date, time, time zone. I need to be able to convert any datetime between time zones even in the face of daylight savings. I think I could add daylight savings transition days to my table (augment the one from TimeZoneMagic), and then write the formulas myself for testing which is which.

But I struggle with not being able to write reusable “functions” like in other languages (someone tell me I’m wrong?). I don’t want some nasty formula for converting between datetimes everywhere, and if there’s a bug then I have to remember everywhere I used it…

Yeah, I am shelving this for a while, I’m maximally frustrated.

Before I go, though, I do note that the Google Calendar API does allow time zone strings, so hypothetically Coda could provide the field for us, nominally solving this problem.

4

Have you read this post? It may help

I agree that coda could simply fix this and not make us deal with all these workarounds. The same could be said for a number of weird issues with coda. Some of the most straightforward and common needs seem to require a tremendous amount of creativity to implement in coda.

5

I enjoyed the read, thank you. I found it interesting that behind the scenes they’re using a timezone aware moment library. But it’s disappointing that I don’t have native conversion tools in-Doc. I can set a doc’s timezone, my datetimes are in that timezone, but that seems to be about as far as I can go. Bummer.

6

I guess I’m not done for now. I played with ToDatetime(str), and observe:

Screenshot 2025-03-19 at 22.14.24
Screenshot 2025-03-19 at 22.14.241176Ă—230 25.5 KB

Conversion from string to Datetime can do some timezone aware things. But being able to convert from string with offsets still doesn’t solve the problem of daylight saving, which has become the bane of my existence.

7

:wave: @silviana_amethyst

I don’t have a way of converting my datetimes between time zones en masse.

not sure if you’ve come across/utilized CurrentTimezone() formula which should help you with issue above.

image
image638Ă—236 5.63 KB

Cheers!
Mel

P.s.
Re: the daylight savings issue - the onus falls on the event creator regardless of if setting event was with coda pack or not. So everything what @AJM said to combat issue rings true :slight_smile:

image
image724Ă—256 19.8 KB