How to deal with timezone issues in a globalized system?

[kyle-ip]

This article is embellished by ChatGPT.

Managing dates and times in computer systems might seem simple, but it's surprisingly tricky. Take the "Y2K problem" for instance - It's a classic case of not thinking ahead in terms of time. These challenges often come from the initial, limited designs for storing dates and times or from underestimating how long a product will be used. This leads to using data structures that just don't hold up over time.

Then there's the issue of thinking globally, especially as products cross time zones. Without a standard approach to time management, international products face messy, confusing time conversions. It's crucial for interface documentation to be clear on time zone details and how to handle them. When services are spread across continents, adjusting for different server time zones can mean major system changes.

Sadly, many products don't start with a global perspective, so development teams often miss these points early on, leading to shortsighted designs.

While overdoing it is usually a no-no in system development, date and time management is where a bit of extra caution pays off. Setting up a solid system for dates and times from the start doesn't cost much, especially compared to the headache of upgrading and migrating data later if your first attempt isn't up to scratch. So, investing in a good date and time system upfront is smart planning, helping you avoid big problems down the road.

Effective Methods for Expressing Date and Time

Strings: Preferred Approach for Transmitting Date and Time

In microservices and frontend-backend interactions, it's best to use string formats for date and time. Strings are clear, readable, and make debugging easier. They also have minimal overhead, which is usually acceptable. However, for interfaces handling a lot of time data, the Unix Timestamp format is efficient for large datasets.

It's important to follow established standards like ISO 8601 (see ISO 8601 on Wikipedia) for uniformity and interoperability. This standard offers various formats:

Commonly used formats compliant with ISO 8601 include:

• Date only: 2022-02-09
• UTC date and time: 2022-02-09T12:36:42Z
• Date and time in a specific timezone: 2022-02-09T20:36:42+08:00
• Higher precision time: 2022-02-09T12:36:42.123456789Z

Be aware that MySQL's date and time format (e.g., 2022-02-09 12:36:42) isn't ISO 8601 compliant. This can cause compliance and interoperability issues in systems using multiple data sources or services. Therefore, prefer ISO 8601 formats for consistent handling of date and time in various computing environments.

Storing Date and Time in MySQL: DateTime

In database management, handling date and time data varies across systems, with MySQL posing unique challenges. Its DateTime data type doesn't include timezone information. When retrieved, MySQL presents this data without any timezone conversion. This lack of timezone in stored DateTime data can cause inconsistencies or errors in applications operating across multiple time zones.

MySQL also uses a "session timezone" for each database connection, affecting functions like NOW(). However, this setting doesn't change stored DateTime data. It only impacts how current time data is generated and perceived within a session, not altering stored DateTime values.

Given these characteristics, developers need a strategic approach for date and time handling in MySQL, especially for applications requiring accurate time representation across regions and time zones. This often involves adding logic to account for time zone differences, ensuring consistent and accurate time-related data processing and presentation.

For example:

SET time_zone =  +00:00 ;
UPDATE tab SET datetime_colume = '2020-01-01 00:00:00';
SET time_zone =  +08:00 ;  -- Another session timezone
SELECT datetime_colume FROM tab;
-- The return value is still '2020-01-01 00:00:00', which is consistent with the data written and independent of the session time

---------
SET time_zone =  +00:00 ;
SELECT NOW();   -- Assuming a return of '2022-01-01 00:00:00'
UPDATE tab SET datetime_colume = NOW();   -- Stored as '2022-01-01 00:00:00'
SET time_zone =  +08:00 ;  -- Change session timezone
SELECT NOW();   -- '2022-01-01 08:00:00' Changed according to time zone
SELECT datetime_colume FROM tab;   -- '2022-01-01 00:00:00' has been written and will not change

Calculation of Date and Time

When working with date and time in programming, native DateTime data types are often used. These types usually align with the ISO 8601 standard, making parsing and formatting standardized.

For relative time zones, programming languages typically use the operating system's timezone database for conversions. The accuracy of this database relies on regular updates, often needing an internet connection.

Unix Timestamp

The Unix Timestamp is versatile for storage, computation, and transmission. However, it's limited in representing anniversary dates as it measures time in seconds since the Unix Epoch (1970-01-01T00:00:00Z). It encodes an absolute point in time, omitting timezone data, which is useful in various applications if the correct numerical type is chosen to avoid time range limitations.

• A signed int32 can represent dates up to 2038, similar to MySQL's TIMESTAMP type, reflecting a Y2K-like issue.
• A signed int64, with 9 decimal digits as in Golang's UnixNano(), extends the range from 1678 to 2262.
• Floating-point numbers are generally not used for Unix Timestamps due to variable precision.

Choosing the right numerical type for Unix Timestamps is crucial to avoid issues similar to the Y2K problem. This highlights the importance of forward-thinking in data type selection, especially where long-term date and time accuracy is crucial.

Design from a product perspective

We can divide all date-time objects in the product according to the following table:

	Date + Time	Time Only	Date Only
No indication of time zone, no need to convert according to the user's time zone	1. local time point	3. local repeatability time	5. Anniversary, holiday, ...
Indicate the time zone, which needs to be converted according to the user's time zone.	2. global time point (globally unique determined)	4. global repeatability time

Each of the five scenarios is explained below.

1. Local Determined Time Point

This format, which includes "year-month-day-hour-minute-second" without timezone information, is context-specific and not universally precise. It assumes an implicit understanding of the relevant timezone based on the business scenario, representing an absolute time within that context.

For non-internationalized products or scenarios with a clearly understood timezone (like flight departure and hotel check-in times), this format simplifies time representation. It avoids the need for timezone adjustments relative to the viewer’s location, expressing time based on the local timezone of the event or location in question.

2. Globally Determined Time Point

This method includes "year-month-day-hour-minute-second-time zone," pinpointing a specific moment in history. It's completely objective, unaffected by the user's or server's geographical location or other external factors.

In interfaces, time is often shown relative to the viewer’s timezone. For example, the 2022 Winter Olympics opening ceremony is set for 20:00 on February 4, 2022, in the +0800 timezone. Viewers in different timezones, like in the UK, would see this time adjusted to their local time (e.g., 12:00 noon GMT on February 4, 2022).

3&4. Repetitive Time

For recurring events, only the time is specified, with the date component omitted. This can be done with a specific timezone or based on a commonly understood context.

Take weekly meetings at a set time with a designated timezone, like every Wednesday at 8:00+0800. If the meeting is international, participants understand the time in their respective timezones. The product interface should display this time adjusted to the viewer's local timezone. However, for events like weekly flights departing at 8:00, where the departure location's timezone is assumed, there's no need for such adjustments in the display.

5. Anniversary Dates

Anniversary dates, like birthdays and holidays, are represented without timezone information, signifying their status as fixed calendar dates, not precise moments in time. For example, Xiao Wu's birthday on March 11th is celebrated on that calendar date, irrespective of location. Similarly, holidays such as Christmas on December 25th are universally celebrated on the same calendar date, regardless of timezones.

In product interfaces, anniversary dates don’t need adjustments for the viewer’s timezone. This reflects the common perception of anniversaries as calendar-based, not time-specific events. Omitting timezone information caters to this common practice of recognizing anniversaries by date, not exact time. This approach simplifies handling such dates in computing systems, ensuring consistency across various regions and user locations.

Anniversary Dates vs. Absolute Time with Low Precision

When you need to represent a specific time like "Beijing time, March 22, 2022," it's not just a date you're dealing with, but an "absolute time with low precision."

Often, what seems like a need for a date is actually a need for "absolute time with low precision."

Consider an example in a global company's employee information management system, where HR is based in Beijing. Suppose an employee in the United States and another in Japan both resigned on March 22, 2022. The same HR department in Beijing processes these resignations. In this case, "March 22, 2022" in Beijing time is an absolute time with low precision, relevant for HR's processing, regardless of the local time zones of the departing employees.

	U.S. Employee (-5:00 Time Zone)	Japanese Employee (+9:00 Time Zone)
Employee's Understanding of Last Working Day	2022-03-22
Employee's Understanding of Effective Resignation Time in the System	2022-03-23 00:00:00 (Local Time)
Beijing HR's Understanding of Effective Resignation Time in the System	2022-03-23 13:00:00 +0800	2022-03-22 23:00:00 +0800
Absolute Time for Server to Execute Resignation-Related System Actions (in UTC)	2022-03-23 05:00:00 UTC	2022-03-22 15:00:00 UTC

In the context of product globalization, the way we perceive "the date an event occurs" often lacks time precision. Traditionally, for simplicity, we might set all times to 00:00:00, ignoring time precision. However, with globalized products, it becomes crucial to complete the time and increase precision.

This completion and precision enhancement should be tailored to the product's specific form and clearly defined.

For instance, in the resignation scenario, the definition of resignation time must be supplemented as per the company's policy. This could mean using the local time of 23:59:59 on the resignation day or the end of the workday, like 17:00:00.

Similarly, in cross-team scenarios, such as when an employee's reporting line shifts from an American to a Japanese leader, to avoid ambiguity, it's common to agree on a specific effective timezone. Often, this is the time at the company's headquarters. Such a standardization ensures clarity and consistency across different regions and teams.

A brief description of how they are handled:

[user input datetime] -> 

[improve precision] -> 

[machine process precise time] -> 

[reduce precision] -> 

[display user datetime]

Strategies for Date and Time Management

Determined Timezone DateTime

In backend systems, it's standard to represent all time objects in UTC. This uniformity is crucial for processes like storing, computing, and transmitting time data.

When time data is stored in databases, timezone information is usually discarded. It's vital to ensure that the timezone being discarded is UTC, as it's universally understood and unambiguous. This method eliminates confusion in time fields within the database.

For times generated internally, such as CreatedAt and UpdatedAt, they should be converted to UTC before saving. With functions like MySQL's NOW(), ensure the MySQL session's timezone setting is correctly configured.

The frontend or Backend for Frontend (BFF) layer is critical in:

1. Handling Low Precision Time Issues: In scenarios like employee transfers set at the 'day' level, the system should complete the effective time based on the user session's timezone, unless it's an international case. For cross-border situations, the approach depends on the customer's policies and product flexibility, like using 00:00:00 of the company headquarters' timezone.
2. Timezone Conversion: The conversion doesn't always align with the user's session timezone. For example, reservation times for transportation and hotels should be based on the timezone of the reservation location.

Clarity and precision in handling date and time in product design are vital, especially to avoid vagueness in globalized software products, ensuring accurate and user-friendly management of time data.

A few points on not being overly rigid or argumentative:

1. Utilizing a Specific Absolute Timezone: If historical data in the database is stored in Beijing time (+0800), it's practical to standardize on the +0800 timezone for all backend interfaces. This decision provides clarity and consistency without the strict need for UTC. The crucial aspect is to select and consistently use an absolute timezone to prevent ambiguities.
2. Time Conversion at the Gateway Layer: Implementing timezone conversions at the gateway layer of the backend interface is an effective strategy. This approach shouldn't be rigidly confined to Backend for Frontend (BFF) or similar classifications. The main goal is to keep the complexity of timezone conversion away from the deeper layers of the backend. By managing conversions at the gateway, the backend system maintains a simpler, more streamlined architecture. This simplification aids in easier development and maintenance.

These strategies highlight the importance of flexibility and practicality in time management within backend systems. Instead of strictly adhering to rigid standards or complicated architectures, the emphasis should be on clear, consistent, and efficient solutions. Such approaches cater to the system's requirements while reducing the potential for confusion or errors in time handling.

Timezone-Free Date

For scenarios like anniversaries (category 5), a specific approach to date handling is recommended, which omits timezone information.

In these cases, dates are managed simply, without extra processing. This approach is well-suited for anniversaries, where the date is the main focus, and time or timezone is irrelevant. Thus, the date object is directly stored in the database as it is.

This method is ideal for actual anniversary dates, like a contact's birthday. It's important to apply this method judiciously. The reason for caution is to avoid issues from misapplying timezone-free date handling in situations where time precision matters. By using this method strictly for true anniversaries, systems can effectively manage these dates as they are intended – as markers of a day, not a specific moment in time.

Special Handling of Timezones

Timezone Uncertainty

In computing, timezones are often represented as absolute time offsets, like "East 8 Zone" for 8 hours ahead of Coordinated Universal Time (UTC). This method is objective but becomes complex with city or region-specific timezones, such as Asia/Shanghai or EST (Eastern Standard Time). These geographically tied timezones can change due to local policies or daylight saving time.

For historical times, geographical timezones work well to pinpoint objective timezones, as past times aren't subject to redefinition. However, future times present a challenge: geographical timezones might not reliably indicate future objective timezones. This uncertainty means that future events scheduled in non-absolute timezones could shift, requiring thoughtful handling in product design and planning.

This complexity underscores the need for agility and foresight in managing time-related data, particularly for global products that must accommodate diverse and potentially shifting timezones.

Handling Daylight Saving Time

Daylight saving time (DST) adjusts clocks forward by an hour during summer months in some countries, leading to different absolute timezones in the same region across winter and summer. DST aims to extend daylight during working hours, essentially redefining standard time to align with daylight.

A clear example is in the USA: On March 14, 2021, clocks jumped from 1:59:59 AM directly to 3:00:00 AM, effectively skipping 2:10:00 AM. To manage this, standards like RFC5545 suggest that events set for such non-existent times should occur at the equivalent next available time, e.g., 3:10:00 AM. Conversely, on November 7, 2021, when clocks fell back from 1:59:59 AM to 1:00:00 AM, times like 1:10:00 AM happened twice. RFC5545 advises considering the first occurrence of such times to resolve this double occurrence.

These DST adjustments present unique challenges in computing, particularly in scheduling and event management. Systems must be adept at recognizing and adapting to these time changes to maintain accurate scheduling and time representation across regions. Developers and product designers must understand and implement guidelines like RFC5545 to effectively navigate the complexities of timezones and daylight saving time.

Wrap Up

Implementing the following strategies can significantly enhance the management of date and time across various application components, ensuring accuracy, relevance to users, and ease of maintenance:

1. Utilization of Absolute DateTime: For most applications, using 'Absolute DateTime' as the primary technical implementation suffices. This method offers a consistent, objective reference point, simplifying date and time handling by avoiding complexities of variable timezones.
2. UTC Standardization in Backend Systems: Backend operations, including database storage and API definitions, should consistently use UTC time. This uniformity ensures the system's time handling is unaffected by the user's or server's timezone, fostering consistency and reducing time-related errors.
3. Frontend Time Adjustments: In frontend development, time input and display should be dynamically adjusted according to the business scenario's requirements. This means adapting to various user timezones and modifying the time representation's precision to fit the application's context, creating a user-focused experience.
4. Handling Dates Without Time: For dates without a time component, it's essential to distinguish between 'Anniversary' dates and 'Absolute Time with Low Precision.' Often, dates are the latter, requiring handling through a 'Determined Timezone DateTime.' This method accounts for timezones, offering more precise time representation, especially in situations where exact timing is paramount.

Further Reading

• Wikipedia: ISO8601 - Criteria for expressing various time objects in strings

• RFC3339 - Generic advice on time and date implementation on the Internet

• RFC5545 - iCalendar The specification of the Internet calendar application

• Microsoft365: Behavior and format options of the Date and Time field

• Time Change 2021 in the United States

Recommendations for technical realization:

1. Stackoverflow: Daylight saving time and time zone best practices
2. Stackoverflow: How to store repeating dates keeping in mind daylight saving time
3. Medium: How to Deal with Date and Time across Time Zones