Everything on the Server is in UTC

This is by far the most important single rule to understand. The Hornbill ESP server does the following things with Date/Time values

• Stores (and retrieves) Date/Time values to/(from) the database
• Performs date/time value checks, for example to know when a specific time is reached.
• Performs date/time calculations to determine future/past date/times, time elapsed between dates etc.
• Event times (reminders/schedules etc) are always calculated in UTC time, but in some cases may require/allow the API user to specify the dat/time value in local time, and provide and accompanying timezone specification that can identify the local time being represented.

The ESP Server service manage date/time values in the database using specific column types, each with a distinct method of handling and storing these values:

• DATETIME: This column type always represents time in UTC without any adjustments for time zones. All date/time values stored in DATETIME format are standardized to UTC, ensuring consistency across the platform.

• VARCHAR(2): Date/time values in this column type are generally stored in the ISO8601 format (YYYY-MM-DD HH:MM:SS). However, to enhance readability when viewing raw database data, the ‘T’ and ‘Z’ separators, which are optional in the ISO8601 standard, are omitted. This makes it easier for humans to interpret the data. It’s important to note that VARCHAR values can vary depending on the application or function; they might not always be in UTC but could instead represent local time relative to a specific time zone. In such cases, the documentation for the specific database column will specify the format and time zone in which the date/time value is stored.

INTEGER: For efficiency, some date/time values are stored as Epoch time. This is an integer representation of time, counted in seconds since January 1, 1970, at 00:00:00 UTC. Negative values indicate times before this reference point, while positive values indicate times after it.

The API and the xs:dateTime type

When defining a date/time value for an input or output parameter of an API we follow the basic international standard for date/time values set out in the ISO8601 specification. This specification covers a wide range of options with date/time formatting which can include Date, Time, Millisecond time, and timezone offsets. . The XML Schema specification for declaring a value a date/time value is xs:dateTime In Hornbill ESP we have adopted a well-defined subset of this specification, specifically this means…

• Internal Representation and Storage: internal representation and storage of date/time values only has a resolution of seconds, we do not store or represent date/Time values in milliseconds.
• Timezone Offsets: While the ISO8601 format supports the provision of a timezone offset as part of the date/time string representation, we have specifically excluded this from our supported format. This is primarily because we do not internally represent/store date/time values which includes a TZ offset property, but also its a design decision to remove ambiguity and any expectation that we would store/persist any such additional information.
• ‘T’ Separators: In the ISO8601 format, if you provide a fully-qualified Date part (YYYY-MM-DD) then the ‘T’ separator is optional, we have opted neither to require it, or allow it. The exclusion of the ‘T’ separator makes the date/time value easier to read for humans in raw format without removing any capability from the date/time representation
• ‘Z’ Separator: because all date/time values are implicitly representing a UTC time, the ‘Z’ separator is optional. If specified or not, does not matter, it has no meaning being present or not.
• Always UTC Unless…: any value specified as a xs:dateTime value will always represent a time in UTC, unless Explicitly Stated Otherwise on a case-by-case basis

Date/time values are represented in string form which can have a length of 10-20 characters, Each part above is always represented in a specific fixed character position in the date/time string, with the time parts to the right of the string being incrementally optional in reverse order.

Supported Date/Time (string) Representation:

This is an example of a fully-qualified Date/Time value represented as a string: 2018-03-19 12:45:10Z

Example Date/Time formatted string values

Working with Timezones

Generally speaking, users of the system will expect to see date/time values represented in their local time. Because we always work with date/time values in UTC time, it is the responsibility of the front end applications and presentations of date/time values to represent date/timed to the users local timezone and locale formatting as required. This is typical for any system designed to operate globally in multiple timezones at the same time. By following this simple rule, there is no ambiguity around how we should be handling date/time values in any given situation.

However, there are many functions and services provided by the Hornbill ESP platform to help working with date.time values, this includes in some cases working with local times. In these cases, date/time values are still represented in UTC time, but, the API’s and operations will additionally include the option for you to specify a timezone to work in.

Take the example of using an API to determine a future date/time based on the users current time, for example, to schedule a report to run. The user would expect the time to run to be specified in their local time. The API would expect the time to be specified either in UTC, but could instead require the date/time value to be specified in local time, and to have a timezone provided, so that the server can determine the exact UTC time for internal representation and storage. Each API parameter that takes a date/time value will specify the exact requirements, of not specified then you should always assume UTC, even if you are also required to provide a specific timezone as well.