Footprints Data Model

Table
Name time_zone_local_hour
Comment
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.

Column
Name Datatype Comment Is PK Is FK
time_zone_local_hour_id bigint The automatically generated primary key of the time_zone_local_hour table. Yes No
begin_is_daylight_savings bit
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.
No No
begin_date_hour_local datetime
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,
No No
time_zone_region_id bigint
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.
No Yes
begin_date_hour_utc datetime
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.
No No
end_date_hour_utc datetime
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.
No No
end_date_hour_local datetime
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,
No No
begin_date_hour_local_s varchar(23)
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.
No No
end_date_hour_local_s varchar(23)
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.
No No
begin_date_hour_utc_s varchar(23)
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.
No No
end_date_hour_utc_s varchar(23)
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.
No No
end_is_daylight_savings bit
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.
No No