Footprints Data Model

Table
Table Name Time Zone Local Hour
Table Definition A bridge table that provides the UTC begin and End Hour datetimes for a local hour in an Olson timezone region. This table is used in Trend Reporting to give a high performance date calculation so that local hours and days can be used on graph axes and report heading with calculated totals. It is also used to load the summary tables using the local time dimension. High speed correct database-side time zone conversions are made by joining to this table.

Attribute
Attribute Name Attribute Definition Logical Datatype Is Required
Time Zone Local Hour Id The automatically generated primary key of the time_zone_local_hour table. LONG Yes
Time Zone Region Id An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source. LONG Yes
Begin Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the Begin Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect, DATE Yes
Begin Is Daylight Savings Flag indicating if daylight savings is in effect for the begin time. This helps identify the special hours that are missing or duplicated during daylight savings days. BOOLEAN Yes
End Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the End Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect, DATE Yes
End Is Daylight Savings Flag indicating if daylight savings is in effect for the end time. This helps identify the special hours that are missing or duplicated during daylight savings days. BOOLEAN Yes
Begin Date Hour UTC A date whole hour in the local timezone for the Begin Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect. DATE Yes
End Date Hour UTC A date whole hour in the local timezone for the End Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect. DATE Yes
Begin Date Hour Local S A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23) Yes
End Date Hour Local S A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23) Yes
Begin Date Hour UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23) Yes
End Date Hour UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23) Yes