Footprints Data Model

Table
Name time_dim
Comment
A table with a generic timestamp. The timestamp is broken up into the specific
pieces that the reports need to slice the information according to the user
specifications.

Column
Name Datatype Comment Is PK Is FK
date_hour_local datetime
A date time in UTC that cooresponds to a whole hour date in the local timezone
for the 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,
Yes No
day_of_week int Integer Day of Week. No No
day_of_month int Integer Day of Month. No No
day_of_quarter int Integer Day of Quarter. No No
day_of_year int Integer Day of Year No No
week_of_month int
Integer Week of month based on the ISO 8601 system used for calculating the
week of the year. 

The first week of the month corresponds to the first week in the month of the
Gregorian calendar that contains 4 days starting on a Monday.  

No No
week_of_quarter int
Integer Week of quarter based on the ISO 8601 system used for calculating the
week of the year. 

The first week of the quarter corresponds to the first week in the first
quarter month of the Gregorian calendar that contains 4 days starting on a
Monday.
No No
td_hour int Integer value for the hour portion of the date hour local. No No
td_day int Integer value for the day portion of the date hour local. No No
td_week int
Integer value for the week of the year based on the ISO 8601 system used for
calculating the week of the year. 

The first week of the year corresponds to the first week in the year of the
Gregorian calendar that contains 4 days starting on a Monday.  Weeks are then
incremented each Monday, until the next years count starts
No No
td_month int Integer value for the month portion of the date hour local. No No
td_quarter int
Integer value for the quarter of the year. This is based on divding 12 months
into 4 quarters of three consecutive months.
No No
td_year int Integer value for the year portion of the date hour local. No No
day_date datetime Today's date with time truncated. No No
start_of_week_date datetime The start of the week that contains the current date hour local time. No No
start_of_month_date datetime The start of the month that contains the current date hour local time. No No
start_of_quarter_date datetime The start of the quarter that contains the current date hour local time. No No
start_of_year_date datetime The start of the year that contains the current date hour local time. No No
end_of_hour_date datetime The next hour increment of the current date hour local time. No No
end_of_day_date datetime The next day increment of the current date hour local time. No No
end_of_week_date datetime The next week increment of the current date hour local time. No No
end_of_month_date datetime The next month increment of the current date hour local time. No No
end_of_quarter_date datetime The next quarter increment of the current date hour local time. No No
end_of_year_date datetime The next year increment of the current date hour local time. No No