RT_TIMEZONE and RT_TIMEZONE_OFFSET

<< Click to Display Table of Contents >>

Navigation:  Professional > Tables and Forms > Reference Value (RT) Tables >

RT_TIMEZONE and RT_TIMEZONE_OFFSET

RT_TIMEZONE and RT_TIMEZONE_OFFSET 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.

 

RT_TIMEZONE.TIMEZONE_CODE 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 DT_LOCATION allows EQuIS users as much detail and accuracy as possible in the event that the time zone varies by location.

 

RT_TIMEZONE_OFFSET 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, that utc_offset_minutes 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 versions 6.6+. 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.

 

15637_timezone_tables

 

Below is a SQL script to generate U.S. timezone records that 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.

 

rt_timezone_generate_data.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.