Hi all. I’m having trouble getting my head around timezones. I have the following fields:
“Start Date” (date picker)
“Time” (drop-down of times in format 09:00 etc (24H)
“Timezone TXT” (drop-down of timezones in format Australia/Perth etc
I have a form view where I need people to choose a location and a date / time. But the time they choose needs to in the timezone of the location - not the person’s or the database’s current timezone.
So for example, in the form, if they choose “Australia/Perth” as their location, and “2024-10-03” as the date (via the date picker) and “12:00:00” as the time, the field data should be 2024-10-03 12:00:00 Australia/Perth.
Originally, I had “include time” in my Date field, but I found that the form automatically assumes my local timezone (which is also shown on the form).
If I don’t “include time”, and use a separate drop-down field for that, the date seems to be UTC (also visible on the form).
So I thought I could not include the time in the date picker, and have a separate field with a drop-down menu for time. I could then create a separate formula field called “Start Date / Time” with the following formula: concat(field('Start Date'),' ',field('Time')) which I assumed would give me a “timezone-free” datetime in ISO format.
I then used another formula field to add the timezone like this: todate_tz(field('Start Date / Time'), 'YYYY-MM-DD HH24:MI', field('Timezone TXT')).
But to my surprise, it still calculated the time in relation to my current timezone. So the calculated time was 2024-10-03 14:00.
I’m obviously missing something here. How do I get the field to be the time entered in the form (2024-10-03 12:00:00) with the correct timezone (Australia/Perth)?
At the moment, Baserow can’t display different timezones for dates in the same field. A date field uses the same timezone for all values. By default, this will be the browser’s timezone unless you specify one in the settings. However, it’s not possible to show one date in the Australia/Perth timezone and another in a different timezone within the same field.
If I understand correctly, the best way to show what a user entered might be to store the input as text. Once a value is converted into a date, you must apply a single timezone—either the browser’s or one chosen from the settings, as shown below.
That said, converting dates to a single timezone doesn’t change the actual values. For instance, 2024-10-03 17:38 Australia/Perth is the same as 2024-10-03 11:38 Europe/Rome.
Thanks so much for your reply, @davide. I really appreciate it, and I see what you mean. In my case, I need to actually send out calendar invites based on the time and timezone. To explain myself a little more clearly:
The application I’m building is actually a booking platform for people that want a photographer for their wedding. It’s actually for my wife and the current one is built in Zoho Creator and is painful to maintain (deluge scripts, bad support etc).
So the idea is the client books the package via the form, and picks a date and a location (which has a timezone associated with it). They pick the date and time based on the location. So if they pick 04/10/24 12:00, and Australia/Perth as the location, then the time they pick is in the timezone of Australia/Perth.
I’ll then build in an automation (via n8n or something) to send an email with a calendar invite to the client (and photographer etc).
From what I gather, this is an issue because that column will store the date and time in the timezone specified for that column.
So in order to get all the times into the right timezone, I guess I need to do something like this?
Set the “Start Date” field to be in UTC
I already have a table called “Locations” with the filed “timezone”, so I could add another field called “UTC Offset” (for example Australia/Perth would be “8”).
Create a new formula field (eg “UTC Date” with a calculation like "if location = Australia/Perth, "Start Date + “UTC Offset”.
The new field should then have all the correct dates and times in UTC format. The issue I see here is things like daylight savings. Someone would have to manually adjust times when daylight savings kick in - and this could get pretty messy.
Can you think of a better way to handle this? I feel like I might be over-complicating it?
I already have a table called “Locations” with the filed “timezone”, so I could add another field called “UTC Offset” (for example Australia/Perth would be “8”).
Create a new formula field (eg “UTC Date” with a calculation like "if location = Australia/Perth, "Start Date + “UTC Offset”.
In this way, you will end up with dates that resemble the ones users entered in their form but are in a different timezone, resulting in completely different dates in the end.
Unfortunately, a date field requires a single timezone to function properly.
My approach to addressing your request would depend on the specific problem you are trying to solve.
If the issue is sharing an invite at the right date and time, you simply need to input the correct date and time in your date field using the todate_tz formula, as previously discussed.
As you can see, in my Baserow table, I’m enforcing the Australia/Perth timezone.
However, when I send an invite for that date and time, my calendar will show the event in my system’s timezone, which is Europe/Rome. For simplicity, I used our ability to sync calendars with external calendar apps here, but an integration with N8N should work as well.
Thanks again for taking the time to test and explain this. It’s much appreciated. I’ve played around with things based on your suggestions and I think it’s working now.
As you suggested, I created a date picker field, then a single select time field and a timezone field. I then created the following formula fields:
Field: Start Date Local Time
concat(field(‘Start Date’),’ ',field(‘Time’))
Field: Start Date Sydney Time
todate_tz(field(‘Start Date Local Time’), ‘YYYY-MM-DD HH24:MI’, field(‘Timezone TXT’))
I set the timezone for this field to be Australia/Sydney (for testing).
The second field seems to be working in that it is showing the times converted to the Sydney timezone eg:
Thank you again for your help. This is not only an amazing product, but the support is top notch. More than worth the small cost of the premium subscription.
