The RT_TIMEZONE and RT_TIMEZONE_OFFSET tables allow users to record and maintain time changes (e.g., daylight savings time). Data stored in these tables can be used to compare non-EQuIS Live data to EQuIS Live data.
The RT_TIMEZONE.TIMEZONE_CODE field is used to define the time zone of the region where data were collected. Time zone data are stored at the record level in: DT_LOCATION.TIMEZONE_CODE (e.g., for sample dates, water level measurement dates, location parameter measurement dates, etc.) and RT_COMPANY.TIMEZONE_CODE (e.g., for laboratory analysis dates, prep dates, etc.). Storing time zone in the DT_LOCATION table allows EQuIS users as much detail and accuracy as possible in the event that the time zone varies by location.
The RT_TIMEZONE_OFFSET table was designed with no END_DATE field in the table. The table is intended to work such that if the measurement date is greater than the OFFSET_START_DATE, then the UTC_OFFSET_MINUTES field is used. To end Daylight Savings Time (DST), create a new record in RT_TIMEZONE_OFFSET. For example, RT_TIMEZONE_OFFSET would contain two records per year (per time zone); one when DST starts and the second when DST ends. For the record at the start of DST, RT_TIMEZONE_OFFSET.UTC_OFFSET_MINUTES = the difference between the time zone and UTC - 60 minutes (e.g., 300 for Central time), and for the record at the end of DST, RT_TIMEZONE_OFFSET.UTC_OFFSET_MINUTES = the difference between the time zone and UTC in minutes (e.g., 360 for Central time).
The EQuIS database function named "equis.date_to_utc" is included in EQuIS Professional. This function calculates the date/time as it would be in UTC from a specified time zone. This function can be used in custom reports for data outside the EQuIS Live Schema and requires that DT_LOCATION.TIMEZONE_CODE (or RT_COMPANY.TIMEZONE_CODE) and RT_TIMEZONE_OFFSET.OFFSET_START_DATE are populated and included in the report output.
Below are SQL scripts to generate U.S. time zone records. These can be used to either populate the tables directly in SQL Server Management Studio (or similar tool) or the data can be copied into an EDD to be loaded into the database via EDP.
Version 1 (start time of midnight, to capture default/undefine times):
Version 2 (start time of 2 a.m., per U.S. Daylight Savings Time official start):
rt_timezone_generate_data_2am.txt
Right-click the text above and select Save link as. Once downloaded, unblock the file and change the file extension from .txt to .sql.