How to work with dates and dates and times, if your timezone isn't Pacific Time

Hi,

This started as I was trying to bring events from Google Calendar into Coda into a table that will have manually created events as well.

The Zapier integration is cool, it works. But then I got very confused, couldn’t get things to make sense with viewing the dates

This is what I’ve figured out. When I add something via Zapier, in the table it stores the string from Google Cal ‘as is’, e.g. a time on Google Cal that has ‘2018-09-16T15:00:00+12:00’ is saved as just that, but displayed for pacific time. If you go double click to edit, it ‘reveals’ the original text (2018-09-16T15:00:00+12:00), once you exit the field it masks it back to the equivalent Pacific Time (in my case, where I’m UTC+12 it subtracts 19 hours).

Its’ hard to read through a table and make sense of that, especially once other people that aren’t as invested in it as I am start to try and work with the information.

If it was just google, I could store the date in a hidden field and use formula to adjust (although have to work out what to do when my timezone shifts to daylight saving and becomes UTC+13?).

But what about if we enter a date manually? For example our PM wants to put a milestone or project date in. From what I can tell when I enter the date Coda assumes I mean pacific time (the calendar picker shows yesterday highlighted to be today too btw). So when I put in the 14th … it thinks I mean the 14th, well that’s good, kinda. But now I’m entering dates as Pacific Time, the dates from google are showing as PT, but they’re really being stored as something else. Does this matter?

Well yes, when we go switch to a calendar view, the calendar view is magically adding 19 hours, to display in my local timezone. That’s ok for the google dates, but the ones I entered manually are now having 19 hours added to them. That’s a bit counter intuitive. I guess if we were doing dates and times we could have one field to enter and calculate from that? (btw I did find I can manually type out a string as above (2018-09-16T15:00:00+12:00) and then it will do the same conversion, but who wants to do that). Where it also is confusing is if we decide to just use a date field and not date time. I thought that might at least remove the time component. But no, when I use the picker to enter for e.g. the 14th September, once we go to the calendar this shows up as 14th September at 07:00PM (added +19 hours again). I didn’t mean 14th at 7pm, i meant jusg the 14th. Again confusing for mere mortals.

I’ve tried various combinations but overall this is not really workable. We want to move our feature tracking over but the PMs here like to use dates and milestones. We’re in NZ, our vendor is in Australia (a 2 hour time difference), it just feels difficult to make this all work

I’m aware that we can probably do something workable with functions and calculted fields, but we would have to do quite a bit to support NZ and Australia and if we were bringing events in from Google, maybe we hvae to put them in a different field too.

Overall it is frustrating not to just be able to pick up dates and work with them. If you’re only based on the west coast of the US maybe it’s ok for you. Or maybe you have this worked out much better than I do

If so would love to have some tips on how to:

  1. Allow users to enter dates or dates and times, as they understand them, in their local timzone
  2. Show these dates in tables, in the user’s local timezone, not adjusted
    … no ‘hey remember this is in pacific time so just mentally add 19 hours’ or ‘enter the date here in local time in this field’ … but if you want to use the calendar remember to use the ‘other’ field which has the 19 hours taken off so the calendar doesn’t go do this for you again
  3. Bring dates through from google calendar, again have them show in a manner that is understandable and workable for a user

Do you think there will be support for working with timezones and to have a consistent approach across the different views?

3 Likes

Thanks for the feedback here @Jonathan_Cowperthwaite. I just posted in this thread regarding a potential workaround as we develop a better solution in the future to handle timezones.

1 Like