Time
Time is the continued sequence of existence and events that occurs in succession from the past, through the present, into the future… In day-to-day life, the clock is consulted for periods less than a day, whereas the calendar is consulted for periods longer than a day. - Wikipedia
A "Date Timestamp" and "Time Dimension" are used for syncing dates and times across systems. The Time Dimension is the most popular dimension or table in the "Enterprise Data Warehouse (EDW)". It also exists in different forms in many enterprise systems. "Date Timestamps" are created for new, changed or deleted records/transactions by applications with databases (ERP, CRM, HCM, MES and more) and during EDW and Data Lake sourcing, transforming, loading and target database integrated processing.
Date Timestamp
Systems have internal clocks for date timestamping. Time Zones are also considered when integrating central data. Below are a few sample "Date Timestamps":
- Thurs 12/31/2009 1:35 p.m.
- Thurs 31.12.2009 13:35 (same time as the above, different format)
- 2005-10-30 T 10:45 UTC (international ISO-time order (big endian) with time zone)
- 2007-11-09 T 11:20 UTC (same format as the above, hence easy to compare)
- Sat Jul 23 02:16:57 2005
- 2009-10-31T01:48:52Z (ISO 8601)
- Many more
Time, Calendar Time and Date Timestamp Discussions
Time Dimension (wide-range)
This Wide-range Time Dimension could work on a limited number of fact areas; however, it would be very large and inefficient for user queries. The alternative is to dimensionally decompose the Time Dimension. We use five levels or dimensions. EDW professionals are good with this area and know time tables can/may exist in many places and levels of the analytics preparation and display process.
The Enterprise agrees on the Central "Time Interval" "Names" and the needed "Ranges" (important for Calendar Time). We are in the 2020s decade of the 21st century and in the 3rd millennium. The last two changed in 2000.
Time at a Nanosecond-level (ns) (one billionth of a second) is not common. The Speed of Light travels one foot in one ns (small increment).
ChatGPT - As of last knowledge update in September 2021, there were no widely known popular enterprise business software or IoT devices that created database records by the nanosecond. Most databases and systems typically operate at the millisecond level for recording timestamps due to practical constraints and the resolution of standard system clocks.
The iPhone Stopwatch typically runs at intervals of 1/100th of a second (0.01 seconds).
Analysis
Time Dimension (wide-range)
As a single dimension, the resulting rows would be 3.2 Trillion for a 100 year dimension.
Separating Time Dimensions (Five Levels)
The table below shows that when designing EDW date and time dimensions that it is better to have five levels for a clean design. These five tables should be centrally used across all fact tables (how time is synced).
In many cases, data is also stored above day for week, month, quarter and year for aggregated data (higher than day time dimension integrations and analytic aggregations for analysis and speed).
Enterprise Time Dimensions
Five Time Dimensions (Tables)
Day Dimension (1)
This is the most important dimension for enterprises which typically run on day and above. It is also called the "Date Dimension". It is also likely the most used table in EDW Select Statements / Queries (high-use table).
The Day Dimension handles attributes (columns) for Day, Week, Month, Quarter, Semi Annual, Annual, Decade, Century and Millennia. It also has columns for Company Calendars, Holidays and more. It can have hundreds of columns.
Hour, Minute, Second and Millisecond Dimensions (4)
These are dimensions with numerics running for the length of the interval (e.g., hour is 1 to 24). Higher-level time dimensions can be used with low-level time dimensions for fine-grain time drill-down. Each level can have its own attributes and time bands or other text or numeric values to categorize data. For example, adding "1/100th of a second (0.01 seconds)" text and numeric columns to the Millisecond Dimension.
