Footprints Data Model

Table
Name prev_timing
Comment
A table with a generic timestamp. The timestamp is used to give the timestamps
of previous and second previous timestamps according to a step as specified in
the choices the user has for comparison reports.

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,

This ties the previous and second previous dates to the date hour local of the
time dimesnion table.
Yes No
prev_day datetime
The date for the start of the previous day for the start of the day that
contains the current date hour local time.
No No
prev_day_of_week datetime
The date for the start of the day one week back from the current date hour
local time.
No No
prev_day_of_month datetime
The date for the start of the day one month back from the current date hour
local time.
No No
prev_day_of_quarter datetime
The date for the start of the day one quarter back from the current date hour
local time.
No No
prev_day_of_year datetime
The date for the start of the day one year back from the current date hour
local time.
No No
prev_week datetime
The date for the start of the week one week back from the start of the week
that contains the current date hour local time.
No No
prev_week_of_month datetime
The date for the start of the week whose number in the previous month
corresponds to the week number in the current month of the current date hour
local time.
No No
prev_week_of_quarter datetime
The date for the start of the week whose number in the previous quarter
corresponds to the week number in the current quarter of the current date hour
local time.
No No
prev_week_of_year datetime
The date for the start of the week whose number in the previous year
corresponds to the week number in the current year of the current date hour
local time.
No No
prev_month datetime
The start of the month one month before the month of the current date hour
local time.
No No
prev_month_of_quarter datetime
The start of the month three months before the month of the current date hour
local time.
No No
prev_month_of_year datetime
The start of the month twelve months before the month of the current date hour
local time.
No No
prev_quarter datetime
The start of the quarter three months before the quarter of the current date
hour local time.
No No
prev_quarter_of_year datetime
The start of the quarter twelve months before the quarter of the current date
hour local time.
No No
prev_year datetime
The start of the year one year before the year of the current date hour local
time.
No No
second_prev_day datetime
The date for the start of the second previous day for the start of the day that
contains the current date hour local time.
No No
second_prev_day_of_week datetime
The date for the start of the day two weeks back from the current date hour
local time.
No No
second_prev_day_of_month datetime
The date for the start of the day two months back from the current date hour
local time.
No No
second_prev_day_of_quarter datetime
The date for the start of the day two quarters back from the current date hour
local time.
No No
second_prev_day_of_year datetime
The date for the start of the day two years back from the current date hour
local time.
No No
second_prev_week datetime
The date for the start of the week two weeks back from the start of the week
that contains the current date hour local time.
No No
second_prev_week_of_month datetime
The date for the start of the week whose number in the second previous month
corresponds to the week number in the current month of the current date hour
local time.
No No
second_prev_week_of_quarter datetime
The date for the start of the week whose number in the second previous quarter
corresponds to the week number in the current quarter of the current date hour
local time.
No No
second_prev_week_of_year datetime
The date for the start of the week whose number in the second previous year
corresponds to the week number in the current year of the current date hour
local time.
No No
second_prev_month datetime
The start of the month two months before the month of the current date hour
local time.
No No
second_prev_month_of_quarter datetime
The start of the month six months before the month of the current date hour
local time.
No No
second_prev_month_of_year datetime
The start of the month 24 months before the month of the current date hour
local time.
No No
second_prev_quarter datetime
The start of the quarter six months before the quarter of the current date hour
local time.
No No
second_prev_quarter_of_year datetime
The start of the quarter 24 months before the quarter of the current date hour
local time.
No No
second_prev_year datetime
The start of the year two years before the year of the current date hour local
time.
No No