ON THIS PAGE:
Dealing with time zones is critical to many operations, and becomes even more complicated when daylight saving and arithmetic operations are involved. In this article, we explore the technical variables involved and explain how P2 Server handles time zones.
Background
In many cases, the time zone and daylight saving observations are influenced by local and international politics, so they don’t always occur in a predictable manner. Therefore a time zone offset is not enough to identify a time zone unambiguously and determine the correct time modification rules, as the offset itself can change for a particular location. For example, in Western Australia, daylight saving was observed between 2006 and 2009 as a 3-year trial, but was not subsequently adopted.
To perform arithmetic operations on dates correctly, both historical time zone and daylight saving information must be taken into consideration.
A User’s Perspective
When specifying the time zone to use in P2 Server Management, there are 2 main things users are concerned with.
UTC
Applications often solve time zone problems by using UTC dates everywhere. This approach works well as long as no arithmetic operations are performed, as performing arithmetic operations on UTC dates often ends with an incorrect result. For example, getting the start of the day in UTC does not make much sense from a user’s point of view. Or adding up the total production for a day in UTC will not take into account that we have 23 or 25 hours in the day if a daylight saving change occurred during that period.
IANA Time Zone Database
The IANA (also called tz, tzdata, or Olson) database is currently the best available solution to Windows time zone issues. The IANA database contains historical data about time zone changes, often dating back to the beginning of 20th century, and it also provides this information broken down to major cities. This database covers the time zone information of several hundreds of regions from all over the world, and is also regularly updated.
Oracle uses slightly different time zone names and abbreviations than IANA, but the overlap is still around 80-90% which makes converting between Oracle and IANA time zones relatively simple, in most cases.
A Technical Perspective
Let's provide a bit more background on how time zones work in software applications.
Windows Time Zones
The Windows operating system divides the world into time zones based on UTC offset “categories”. Users have to pick the time zone which matches their offset the best. Windows is also aware of daylight savings. All time zone and daylight saving information is stored in the registry.
Until Windows Vista, no historical time zone or daylight saving information was stored, and even from Vista onwards, only very recent changes have been recorded.
There are 2 main issues with how Windows handles time zones:
- It’s not granular enough. Sometimes, even within small countries, time zone information can vary from region to region, or even from city to city. Windows‘s information is not accurate enough to capture all of the regional differences.
- Limited historical data is available, which means that the operating system is unaware of time zone or daylight saving changes that happened 15 or 20 years ago.
.NET Time Zone Handling
P2 Server uses .NET for its API interface. The .NET framework has some known issues with its DateTime and DateTimeOffset types.
- The DateTime type is not able to clearly express any time zone other than UTC and the computer’s local time zone.
- While the DateTimeOffset type does contain offset information, that’s not enough to clearly map it back to a time zone.
However, .NET’s main issue is that it relies on Windows for time zone information and, as explained previously, that’s not comprehensive and accurate.
Microsoft SQL Server
The main date type in Microsoft SQL Server used to be datetime, but in newer versions datetime2 and datetimeoffset are the recommended date types.
Datetime2 is a more precise version of datetime, but it also suffers from the same problem as datetime since it doesn’t carry any additional time zone information.
Datetimeoffset is slightly better, as it is also quite precise plus it contains a UTC offset as well. However, as mentioned earlier, an offset is not enough to identify a time zone unambiguously. Using datetimeoffset still appears to be the better option though, as it contains more information than other date types. If datetimeoffset is used only for storing UTC dates, then at least it is clear for all consumers that they have to convert those dates to other time zones to execute arithmetic operations or to display them to users.
P2 Server uses Microsoft SQL Server as a back-end database, with DateTimeOffset columns for storing dates, however the Data Layer component makes sure that only UTC dates (with zero offset) are written to the database.
Oracle
Oracle provides 4 different types for storing dates:
- DATE: Stores a date without time portion. It doesn’t contain any additional time zone or offset information.
- TIMESTAMP: Stores a date with time. It doesn’t contain any additional time zone or offset information.
- TIMESTAMP WITH TIMEZONE (or TIMESTAMPTZ): Stores a date with time and either time zone name or offset information.
- TIMESTAMP WITH LOCAL TIMEZONE (or TIMESTAMPLTZ): Stores a date with time but without name or offset information. However, when users retrieve such values, they will be converted to the time zone of the user’s local session.
Noda Time
P2 Server uses a 3rd party library called Noda Time, which is currently the only .NET component which uses the IANA database correctly. It has a built-in copy of the most recent version, but it can also load information from external files during runtime to keep the consumer code up-to-date.
Noda Time uses 3 different types for expressing dates:
- Instant: Expresses the number of ticks since Unix epoch time (January 1, 1970, 00:00:00). It doesn’t support any operations other than adding or subtracting ticks. It simply just expresses a very accurate point of time without any additional time zone or calendar information.
- LocalDateTime: Expresses a date without any associated time zone. For example, July 16, 2014 13:25:57. This type supports arithmetic operations such as adding a day or a month.
- ZonedDateTime: Expresses a date with an associated time zone. For example, July 16, 2014 13:25:57 Asia/Singapore. But it only supports very few basic arithmetic operations, such as adding hours but not days or months.
Noda Time also offers support for dealing with ambiguous times (when an hour happens twice due to a daylight saving change) and skipped times (when an hour is skipped due to a daylight saving change), and can be considered a complete replacement for .NET’s date time and time zone types.
Understanding Ambiguous Times
When you have daylight saving (DST) in a time zone, then 2 hours out of every year can become very confusing. For example, on 29th March 2015, daylight saving started at 2am in Hungary. This means that the clock jumped from 1:59:00 am to 3:00:00 am, and 1 hour was effectively skipped. The opposite happens on 25th October at 3 am when the clock changes from 2:59:59 am to 2:00:00 am, thus making 1 hour ambiguous because it is repeated after Daylight Saving has finished.
In the first case, let’s say our time is 1:30 am and we add 1 hour to it. It becomes unclear what that really means, since 2:30 am does not exist. Instead, the lenient resolver returns the later time of 3 am, which is a valid time.
In the second case, if our time is 1:30 am and we add 1 hour, it’s not clear which 2:30 am we mean. The first occurrence (when DST was still in effect) or the second occurrence (after DST had finished)? The lenient resolver will always return the second, later occurrence.
JavaScript
Dealing with time zones and UTC in JavaScript can be even more difficult than in Windows. According to the language specification of JavaScript, browsers are required to provide only the latest known time zone information and nothing more.
P2 Server extends JavaScript’s date time support by using Moment.js and Moment Timezone. It uses the same IANA database as Noda Time which makes passing dates from one to the other very simple and easy.
Handling Date and Time
Let’s dip into the technical side of P2 Server to see how it handles dates, times, and time zones.
Data Broker Requests
The Data Broker component of P2 Server expects a “processing time zone” to be defined in requests. ProcessingTimeZone is a required parameter of Data Broker GET requests. So the consuming application that submits the request (e.g. Explorer) must specify what time zone they want to use for processing.
All calculation functions and adaptors will act according to that time zone.
During the execution of tag fetches and calculations, this processing time zone is used to convert dates into a format suitable for executing arithmetic operations (i.e. between Noda Time Instants and LocalDateTime formats), and then back again when the operation has finished. The processing time zone parameter ensures that calculations return the correct value for that time zone.
Adaptors
All adaptors require the “Time Zone” parameter to be configured on each datasource. The parameter tells the adaptor the time zone of the source data, so that the adaptor knows how to convert date times between the source data and P2 Server (UTC and/or ProcessingTimeZone). The parameter must either contain an IANA time zone name or a fixed UTC offset.
For datasources that do not natively support time zones, this configuration can also convert the request and responses to the correct zone or offset.
To get really technical about it:
The SQL Server Adaptor uses the Time Zone parameter to convert the DateTime and ZonedDateTime parameters of dataset queries from Instant to the configured time zone. It also uses this parameter to convert DateTime values, which are retrieved from the database, back to Instants. For DateTimeOffset values, the adaptor simply uses the offset of the data in SQL Server to get back an Instant.
The Oracle Adaptor also uses the Time Zone parameter for converting DateTime and ZonedDateTime parameters of dataset queries from Instant to the configured time zone. But this adaptor also requires an additional parameter called “Use Oracle Time Zone Information”.
- If Use Oracle Time Zone Information is not selected, dates and times will be assumed to be stored in the Oracle database in the configured time zone, which makes executing and processing queries faster. The price of this performance benefit, though, is that time zone information is ignored in the case of TIMESTAMPTZ AND TIMESTAMPLTZ values. In this mode, Oracle will return all values as .NET DateTimes and the adaptor will use the Time Zone parameter to convert these back to Instants.
- If Use Oracle Time Zone Information is selected, time zone information will be requested from the Oracle database, with the adaptor using Oracle specific .NET data types. In this mode, DATE and TIMESTAMP values will be converted to Instants based on the Time Zone parameter, while TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE values will be converted according to their source, Oracle time zones, and offsets.