Data Modeling - Time

A common analytical use case is to segment data by time. For example, grocery stores may need to keep track of what time produce should be restocked to optimize for consumer purchasing patterns and freshness. For these datasets that will need time analysis, Gooddata recommends adding a separate Time Dimension dataset in conjunction with the relevant date dimension (see below).

In the above image, we see that the Stock Time dimension has a primary key on Stock Time, attributes that split out the Hour, Minute, and Second, as well as a Stock Time in Seconds fact. Please note that the Stock Time Epoch fact is kept out of the Time Dimension since Epoch values typically take into account both Date and Time. Within the time dimension, you can add different attribute splits that will aid your ability to further segment your insights. When deciding on each attribute, please keep in mind the following:

• Adding attributes for any custom intervals (ie. 5 minutes, quarter hour, half hour, etc.)
• Adding labels for any custom display formats (ie. 12-hour clock vs. 24-hour clock)
• Adding labels for sorting so that hours, minutes, and seconds are displayed without leading zeros

Populating the Time Dimension

We often see that Time data will typically come in timestamp (yyyy-mm-dd hh:mm:ss) format. However, when loading the Time dimension, it is best practice to populate with time (hh:mm:ss) and have the date pulled out as a separate dimension, as shown above. This way, the platform’s performance is maintained even as the range of dates increases. A CSV file is prepared below with the data used to populate the above Stock Time dimension.

The Time dimension can be consistently populated for all times in a day (ie. 00:00:00, 00:00:01, … 23:59:59). In the LDM shown above, this time value would populate the Primary Key field Stock Time. The Stock Time in Seconds field will have values ranging from 0 - 86400, and can be useful for making comparisons and performing arithmetic on Time. For more instruction about time dimensions, and on mapping data to the time dimension through SQL, please check here.

Calculations with Time

As mentioned earlier, you may need to perform arithmetic operations on your time attributes. A convenient way to calculate using time values through MAQL is by first converting the values to facts like Stock Time in Seconds or Stock Time Epoch. Epoch time is a numerical representation of a datetime value (seconds since January 1, 1970), and is stored at second level granularity (fractions of seconds are also maintained).

Example:

Find the average time between restocks per Product:

SELECT AVG((SELECT SUM(Stock Time Epoch)) - (SELECT SUM(Stock Time Epoch) FOR PREVIOUS(Stock Time))) BY Product ID