On the surface time zones seem to be fairly straightforward to deal with, but as we’ve learned, there are a few pitfalls to look out for. In this short post, I will describe how ServiceNow works with dates and time zones and how SnowMirror users can deal with time zones in the replicated data. A typical SnowMirror FAQ is: “I see different times in the mirror database than in our ServiceNow instance! What’s wrong?“.
First of all, good news from the underlying technology of both ServiceNow and SnowMirror. Java has a very good time zone support including DST handling. Time zone in the Java world represents a time zone offset and also figures out daylight savings. So basically all time-related data types in the ServiceNow platform are stored in the Coordinated Universal Time (UTC) format which does not contain any timezone information but are displayed to users in their local time zone according to user preferences. This is very handy. Users in different time zones can view the times valid for their locations. This pattern applies even for the ServiceNow ODBC driver which converts all the time stamps into the time zone of a user account accessing the ServiceNow instance over ODBC. More info about timezone support in the ServiceNow Wiki.
And now what happens if the data is being replicated by SnowMirror into a local database? SnowMirror is using a SOAP API called Direct Web Services. These out-of-the-box web services return the dates and timestamps in a way they are stored in the ServiceNow underlying database. That means all of them are in the UTC format. So the default SnowMirror setting is to store the dates in UTC too (i.e. SnowMirror is a real mirror). However imagine you are located in PST time zone and you need your reports to be displayed in PST. There are several ways how to achieve it.
1. Implement Timezone Support into Reports
This approach means that the data in the mirror database are stored in UTC and the reporting or BI tool correctly deals with time zones. This is a universal solution as you can show the report in any other time zones as well. It only depends on the settings of the report viewer. The con is that it requires some additional effort when building the reports and not all the projects require it.
2. Time Zone Conversion
It is possible to configure SnowMirror to convert all the dates (timestamps) into a target, system-wide time zone. SnowMirror is still receiving the data in UTC and it uses Java standard libraries to convert it into the configured time zone. It even reflects the DST offset for the time zone. The advantage is the simplicity of this way and the drawback is that the data is locked in the chosen time zone and it’s hard to make reports for a different one.
To use this option just go to Settings -> ServiceNow -> Show Advanced Settings -> Mirror Data Time Zone. Please note that you have to refresh all the existing data because the conversion is happening during the data download.
3. Session Sensitive Data Types
When creating the tables SnowMirror is using very basic data types for each of the supported database vendors. The default data types for dates and time stamps usually do not support any time zone handling. However, there is advanced property (Settings -> Advanced Settings) called snowMirror.mirrorTable.storeDateTimeWithLocalTimezone. If you enable the property (disabled by default) then SnowMirror will start creating different date and time stamp data types. Most databases support data types that are sensitive on a time zone in the current user session. So different users can see the dates in different time zones.
- Oracle – timestamp with local time zone
- PostgreSQL – timestamp with time zone
- MySQL – timestamp
- Embedded H2 – datetime
- SQL Server – datetimeoffset – limited, not really working as the other databases
Bonus – DST Example
Many users ask about daylight savings time. Let’s make a quick example.
- ServcieNow user’s time zone is Europe/Berlin (Which is UTC+1 in winter and UTC+2 in summer)
- Incident created in winter time – 2015-02-02 14:00 is stored in ServiceNow as 2015-02-02 13:00
- Incident created in summer time – 2015-07-07 14:00 is stored in ServiceNow as 2015-07-07 12:00
- Regardless the current season you always see both incidents as 14:00. I.e. you will not see the winter incident during summer season as 15:00
- By default SnowMirror stores both incidents in UTC (E.g. 2015-02-02 13:00 and 2015-07-07 12:00)
- If you configure the Mirror Data Time Zone then both incidents are stored as viewed in ServiceNow UI (E.g. 2015-02-02 14:00 and 2015-07-07 14:00)
