How can date fields be configured to offset the time zone for data loads?
This feature was introduced in Insight v6.2.1 and documented in Studio's on-line Help system.
This article explains how to use the Timezone offset feature of Insight and how the configuration of this feature can impact the resulting data set returned during a data load.
NOTE: This feature can be enabled at the data source and field level. When configured at the data source level, the offset applies to ALL date-time fields, therefore, you should carefully review your business requirements and confirm that is required. Fields that store date-of-birth should not be altered and can be set at the field level after loading the tables in the data source and selecting the field, then setting the exceptions there ("No adjustment"). As a best practice, offsets should only be configured for those date-time fields that require it, not at the data source level.
1. Insight server is in CA, Pacific Time Zone
2. Data in the source database is Eastern Time Zone.
3. Sample source database:
In our Insight project, we have a data source for the above table. The date field is configured for Eastern time zone. This has no processing impact yet.
To load the data into a record with the adjusted time, the record’s date field’s property must have Convert to Server Time enabled (checked). When the data load runs, the Insight server’s time is used as the base time zone to determine the offset. Since our Insight server is in CA, Pacific is used as the base zone.
A data load runs using the default date filter:
A data load using a time range of 13:00 to 14:00 will retrieve data from the source with that range (Eastern), however, the data loaded into the record will be adjusted. That means an entry of 13:00 in the data source will be written into the record’s date field as 10:00 AM, the time difference between the Insight server and the time zone configured for this date field in the data source. Below is the record’s data load with time adjusted:
Date filters can optionally be configured to use special offset functions, FromDateWithTimezone(dateField) and ToDateWithTimezone(dateField). Each of these takes a date field as a parameter.
This changes the behavior of the data load by first adjusting the time zone to the specified time zone configured in the data source’s date field (Eastern), then loading the data. The data written into the record is done identical to the prior Example. Note that a different data set is retrieved into this record compared to Example 1 and the time updated to reflect the offset:
Level of Complexity
|Insight||6.2.1 and newer|