There doesn't appear to be a way to account for the time zone on an event registration page.
This is less of an issue for a local event, but is quite a problem for a global online event.
At a minimum I would expect there to be an option to set the time zone for the event and for the time zone to display on the event registration page. (Ideally you'd want the page to show the time in the user's time zone.)
Current behaviour
There is no option to set the time zone for an event. (I presume that the system time zone is used but I'm not sure.)
The time zone isn't displayed on the event registration page.
Proposed behaviour
There is an option to set the time zone when creating an event. This should default to the user's time zone.
The time zone is displayed on the event registration page.
The UI to set the timezone. We'd need a function to retrieve a list of allowed timezones (maybe all or a configurable list like currency).
The UI to display/convert the timezone. Initially we could just display the timezone value retrieved from the DB alongside the date/time. In the future we could add conversion functions to display in different timezones.
We'll need to consider what CiviCRM does when displaying a datetime (any conversion it already applies).
I just want to voice support for the theme here and in #441 (closed). FWIW, to my reading, this issue frames it more clearly/concretely than 441 -- e.g. it draws attention to a specific use-case. Note that temporal data is used in many different ways, e.g.
(1) logging/creation/modification times,
(2) advertising/publishing Events (sometimes online; sometimes offline; with multiple specific dates, like start vs end),
(3) scheduling for (semi)automated tasks -- like mail-blasts, renewals, etc
Just an example of how it's important to differentiate the use-cases...
For a civicrm_event with a physical gathering, it absolutely makes sense to store a varchar timezone because many events have a socially-normative TZ. (If you're in California and planning to attend a conference in England, then you need to plan your travels in UK time -- otherwise you might miss the conference.) Whenever anyone looks at that Event, they should perceive its time in socially-normative form (and the norm should be communicated clearly). You should probably store the start_date as a TIMESTAMP, but DATETIME is also reasonable, and it's broadly 6/half-dozen. (Of course, for an online event, you probably want some option to see in viewer-subjective TZ...)
For logging, the logged data may originate in many different TZs, and the reader's head will spin if they're jumping around among TZs. It's sensible to display all created_dates or modified_dates in one timezone (chosen in viewer-subjective fashion, based on some user/org/server/browser policy). You must store logs with a normalized format (eg TIMESTAMP), or else sorting/filtering would become insane.
The UI to display/convert the timezone. Initially we could just display the timezone value retrieved from the DB alongside the date/time. In the future we could add conversion functions to display in different timezones.
Big +1 for communicating TZ more clearly. The biggest frustration (IMHO) is the ambiguity that arises when a system/organization spans many TZs, and simply displaying TZ can remove a lot of ambiguity.
A UI-level conversion option might make sense for some fields (e.g. for inputting civicrm_mailing.scheduled_date).
I ran across a simplified version of this issue for a client recently as well. In their case, they just wanted the timezone to be displayed, it was assumed to be the (CMS) default timezone.
I discovered that there is almost support for this - you can configure the display of date/time values to include the timezone by editing the civicrm configuration at /civicrm/admin/setting/date?reset=1 and adding
"%T"
But, including "%T" isn't supported, so with a little patch to /CRM/Utils/Date.php I just set %T to convert to the 'Default time zone' of the CMS install.
Unfortunately, I did it in a Drupal-8 only way, and after struggling to get my head around it, I've concluded that there isn't an easy way to do this that is CMS agnostic, i.e. there isn't any supporting function already in CRM/Utils/System/your-cms-whatever.php
I'm tempted to add in a civi-specific configuration of "Default timezone" that would be applicable for all dates/times that don't have one, it could even default to the sql-configured one.
I suspect this doesn't add anything to the discussion but recently we ran in to the issue relating to the iCal that is provided when someone registers for an event. For a logged in User who has set their timezone this may be fixable, but someone signing up anon to an event that is set up in a different timezone to the user there is no way for the participant registration to know the timezone.
Even being able to make it really clear which timezone the event date/time was using would be a step forward.
There's really no good solution to the general issue that some date/time values are stored without a timezone. There are workarounds that are okay for things like logs and internal stuff, but the original civicrm assumption that there was a 'implicit timezone' from the server really just wasn't a great idea, in retrospect, and making it explicit and configurable is what my solution proposes. To be clear, it doesn't solve every version of this problem, or even do anything at all on it's own, it just allows an administrator to explicitly configure what that implicit timezone is, and then use it in places as appropriate for their purposes. The issue of the ical/timezone is a problem regardless of a user having a timezone, because the event time itself has no timezone (other than the implicit one which may be implied differently depending on way too many things). Different people looking at an event and getting that date/time converted to their timezone is a different problem.
Hi - while searching on this same issue, I came across this old Jira Page https://issues.civicrm.org/jira/browse/CRM-17618 :
'time zone field needed for event creation'
I can see that a fair bit of thougth has been given to this issue, but looks like there is still no solution.
@justinfreeman - you gave a good summary of it - wondering if you have come up with any workarounds for the scenario of online meeetings with participants in multiple time zones - something that everyone is surely having to do a lot more of now.
As @petednz notes, the ical feed is going to give Anons the wrong event start time if they are not in the same default time zone as the civi install. It also effects scheduled reminder emails for the event and the early bird discuount as well.
Since this documents "use cases" for why this is important. This was the case in 2017 - still the case today, 2021.
It's also important to note that the The Doctor When extension is not a solution here and in fact only partially addresses a specific part of the problem with CiviCRM timezone handling.
Agileware added a comment - 2017-08-25
Here are a few early morning thoughts on this one.
Example Use Cases
Use case 1: Physical Event - where people physically attend the event, traveling from different timezones
A non-profit using CiviCRM is running an event in Canberra (GMT+10)
System hosting CiviCRM has timezone set to Australia/Sydney (GMT+10)
Attendees for the event are located in Perth (GMT+8), Adelaide (GMT+10:30), Brisbane (GMT+10), Wellington (GMT+12)
The Event date is GMT+10
Attendees will travel to the event location and therefore need to see the event times in the local time zone (GMT+10)
Use case 2: Virtual Event - where people virtually attend, in their local timezone
A non-profit using CiviCRM is running an event in Canberra (GMT+10)
System hosting CiviCRM has timezone set to Australia/Sydney (GMT+10)
Attendees for the event are located in Perth (GMT+8), Adelaide (GMT+10:30), Brisbane (GMT+10), Wellington (GMT+12)
The Event date is GMT+10
The event is virtual, so attendees will be joining the event at a time in their local time zone.
Attendees need to be able to easily understand (convert) the event time zone (GMT+10) to their local time zone, so that they do not miss the event.
Use case 3: Physical Event - located in a remote location, people physically attend the event, traveling from different timezones
A non-profit using CiviCRM is running an event in Darwin (GMT+930)
System hosting CiviCRM has timezone set to Australia/Sydney (GMT+10)
Attendees for the event are located in Perth (GMT+8), Adelaide (GMT+10:30), Brisbane (GMT+10), Wellington (GMT+12), Darwin (GMT+930)
The Event date is GMT+930
Attendees will travel to the event location and therefore need to see the event times in the local time zone (GMT+930)
Recommendations
Event Start date/time and Event End date/time, provide a field timezone select
Recommend also changing the time field to a drop-down select in 15 minute units. Current field is clunky.
Event iCal link, include timezone value with date/time values
Event iCal feed, include timezone value with date/time values
Event Info and Event Registration pages, timezone should be clearly displayed with the Event Start and End dates
It would be good to either: 1) provide URL to convert to local time zone, 2) an option to convert to users local timezone or 3) just automatically convert (JS) to the users timezone when the event is viewed
Timezone is equally applicable to the other date fields which are available on the event. Such as:
Discount by signup date, has start and end date
Online Registration:
Registration Start Date Time
Registration End Date Time
Schedule Reminders
Fees, Price Sets
Scheduled Reminders
Since if the event is set to start on a specific date/time then all those dates should be in the same timezone. Otherwise you can have the situation where Schedule Reminders are sent at the wrong time. I think this brings into scope Schedule Reminders.
Price Sets
The other consideration is Price Sets. As the Event can have Fees based on a Price Set. These can have Active On date/time and Expire On date/time field. Both these fields need timezone handling. I think the simplest option here is to just specify the timezone required for each of these fields.
iCal
I think it's worth pointing out that the current implementation of iCal in CiviCRM does not include any timezone information, consequently any software importing the iCal ICS or feed will assume the local timezone, which is not always the case and causes incorrect information to be shown.
Time zones
Here's a few links to the various time zones maps:
Breaking down 1 I think we need 2 things from a storage point of view
convert the date time fields to timestamps (so we are storing the time in an authoritative way). We use to use Dr When to make this change a bit optional but could also do via an upgrade script
add another field 'timezone_presentation' to relevant tables - potentially this would be either a timezone descriptor from an option list or 'blank' for user time'.
It becomes the job of the form layer to help people to save the right timestamp (ie the user might enter a datetime + a timezone but it would be saved as a timestamp). I'm not quite sure where the division lies between the api and the form layer in presenting the date by timezone - I think the api might need to help out a bit since there will already be some timezone interpretation when retrieving from the DB
Yes, this is an excellent way to slice up the problem, but I disagree with your solutions.
First, I think the Dr. When extension is solving a different problem, even though it sounds the same.
Specifically, I think that event date/time fields should stay as date time fields, they just need the timezone qualifier that they don't have now. A timestamp has an implicit timezone (UTC), whereas a date/time field without a timezone qualifier doesn't. For scheduling of civimails, a timestamp makes sense. For setting event date/times, it doesn't, because a date that happens in a specific place should retain that timezone.
The 'timezone presentation' idea is muddying the issue - presentation is not or should not be a property for how the data is stored.
In terms of data entry, I don't think there's a single solution here, but there is a general principal I'd propose which is that all date/time entry and presentation should include a timezone, at least by default.
We already have a site setting for how date/times are presented, and using something like that, or extending it, is the tool for modifying the presentation layer.
Specifically, I think that event date/time fields should stay as date time fields, they just need the timezone qualifier that they don't have now. A timestamp has an implicit timezone (UTC), whereas a date/time field without a timezone qualifier doesn't. For scheduling of civimails, a timestamp makes sense. For setting event date/times, it doesn't, because a date that happens in a specific place should retain that timezone.
Thinking out loud here...
I agree and disagree. To be clear, we are comparing two ways to model the timing of an event, ie
(A) start_date DATETIME, event_tz VARCHAR
(B) start_date TIMESTAMP, event_tz VARCHAR
For an offline event, the event has a specific place and a specific TZ, and you can use either (A) or (B). For an online event, there isn't a specific place, and (B) is better.
Ex: Consider an org like civicrm.org which has online events worldwide with far-flung presenters+audiences. So one event organizer chooses CEST for a Europe-oriented event; another chooses PDT for an Americas-oriented event; another chooses AEST for an Oceania-oriented event. All good. But then a user comes and asks, "Show me all your online events so that I can choose the best time for me." (The Oceania event might be best if the participant is an American nightowl or a European earlybird.) You would want to show that listing in chronological order, with times adapted to the viewer's TZ.
If you allow the user to sort/filter events, then how do you construct the SQL query for them? I believe that's more intuitive and more performant under (B) than under (A). The trouble is that start_date [DATETIME] of event #123 is not comparable to the start_date [DATETIME] of event #456. Converting to TIMESTAMP makes them comparable.
OTOH, I think your point is good, though maybe I'd phrase it differently. "Far-flung online organization" is a particular niche. For more common cases, (A) is perfectly fine, and it may be less work to write a patch for (A) than for (B), and it's often preferable to view the event in event-TZ.
How about this compromise:
(C) start_date DATETIME, event_tz VARCHAR, start_ts TIMESTAMP (where start_ts is populated via trigger)
Characteristics of this compromise:
Easy+performant to display with event-TZ (start_date) or viewer-TZ (start_ts) or both -- in any framework (SQL, API, Views, etc).
Easy+performant to sort/filter on an apples-to-apples basis (TIMESTAMP / start_ts).
Provides continuity in the data-type of start_date.
Starts a game of whack-a-mole (determining which UI-elements should use event-TZ/DATETIME vs viewer-TZ/TIMESTAMP), but I think that whack-a-mole is a matter of essential-complexity. (You probably have a similar game with any schema.)
Suppose one wants to provide generic tweaks ("Mouse-over dates to show alternate TZs" or "Right-click to show formatting options"). It is easier to add this to the presentation-layer (eg Reports/Views/Searchkit/Afform/Profile/etc) if all values are available in a normalized TIMESTAMP rather than context-sensitive DATETIME.
TRIGGERs work on MySQL 5.6. (There is a slightly prettier technique in MySQL 5.7, but TRIGGERs will do.)
I think my first observation is that timezones are hard and it's easy to get confused. I'd like to reiterate my plug for option (B) on the basis that it's the least confusing. WRT to your listing issue - although in theory an on-line event doesn't have a specific place, in my mind and in most people's minds it does - it needs someone to start by saying something like "it's starting at date/time + timezone", and timezone is some agreed upon reference. We don't think in terms of abstract references (like UTC) very easily.
I think we can handle the generation of listings in timezone standardized order without too much pain, even if it has to use some php ...
(adixon) For scheduling of civimails, a timestamp makes sense. For setting event date/times, it doesn't, because a date that happens in a specific place should retain that timezone...
(adixon) ...reiterate my plug for option (B) on the basis that it's the least confusing...
Small clarification. We may have a crossed-wire or typo somewhere, or maybe there was a nuance I missed. I interpret these comments as supporting (A) start_date DATETIME+VARCHAR.
Re: Confusing -- I sort'a see this. It may be true that some site-builders find it confusing to have access to two forms of "Start Date". The problem is that presenting only one form is also confusing. It just manifests differently. (One manifests as, "Why are there two fields when I only expect one?" The other as, "Why does it use format-X in this spot when I needed format-Y?") I'd argue that this tension between nominal-time/normal-time is essential to the CiviEvent problem-domain, and providing the site-builder with both views of the information has the benefit of educating/empowering them.
I think we can handle the generation of listings in timezone standardized order without too much pain, even if it has to use some php ...
Agree and disagree :)
If one used (A) and wished to sort by standardized time, then yes - you could articulate that in PHP or SQL. Perhaps something like ORDER BY convert_tz(start_date, event_tz, @@SESSION.time_zone)). Writing that part isn't hard. But how would one support this across the various frontends used by site-builders (Reports, Views, Entities, SearchKit, APIv3, APIv4, etc)? You could easily spend a person-day on each medium. It's easier to add the extra column than to play whack-a-mole which each medium.
The issue of indexing is more subjective -- it depends on scale expectations. In my imagination, the typical organization (average-by-mode) probably has <50 events. For them... no, performance doesn't matter much. However, CiviEvent has a recurring-event feature that can project forward far into the future, and people periodically pop-up with questions about how to generate events programmatically, and we know there are nation-scale organizations that use Civi. So our capacity-planning should aim higher. My gut says "aim to support thousands of events". At that scale... indexing becomes interesting... esp if this data feeds into reports with joins/groupings/subqueries.
@totten I just read your comment & you raise indexing - but I can't tell what you are responding to.
However, I note the order by you specify above will bypass any index
`ORDER BY convert_tz(start_date, event_tz, @@SESSION.time_zone)`)
was the indexing comment related to that - ie pointing out that any attempt to sort / filter based on a conversion would be an unindexed query? Note that it's not just how many records are in the event table that affect performance but also in any tables you join in
note the order by you specify above will bypass any index
Right, exactly.
If the schema is (A) start_date DATETIME, event_tz VARCHAR, and if you're trying to find/sort/filter events by when they actually happen (or by your perceived time as a remote/online participant), then you have to do convert_tz() - which bypasses indexing.
If the schema is (C), then you can do simpler queries against either start_date DATETIME (nominal time as articulated by the organizer) or start_ts TIMESTAMP (standardized time) - and there should be no need for convert_tz() which bypasses indexing.
You're right, I did mean (A), and I'll concede your point about scalability as well. My concern about complexity was not just from an administrator/end-user perspective, equally from a developer/project-contributor point of view, as a reflection on how hard getting to this point has been.
From a storage point of view I'll be happy with either B or C and kinda prefer B for simplicity.
From a process and site-builder/administrator UI perspective, I think adopting a "implicit/default event timezone" and then converting all the existing event dates would be smart, and then we could provide the ability to configure event timezones and maybe other things (like displaying events in the visitors timezone?) in a new extension? I suspect you've already thought this one through ...