Data Analysis Using SQL and Excel

Chapter 5. It's a Matter of Time

Along with geography, time is a critical dimension describing customers and business. This chapter introduces dates and times as tools for understanding customers. This is a broad topic. The next two chapters extend these ideas by introducing survival analysis, the branch of statistics also known as time‐to‐event analysis.

This chapter approaches time from several different vantage points. There is the perspective of what is actually stored in the columns representing dates and times, the years, months, days, hours, and minutes themselves. There is the perspective of when things happen, along with ways to visualize changes over time and year‐over‐year variation. Another perspective is duration, the difference between two dates, and even how durations change over time.

The two datasets used for examples —purchases and subscribers —have date stamps accurate to the day, rather than time stamps accurate to the minute, second, or fraction of a second. This is not an accident. Typically for business purposes, the date component is the most important part, so this chapter focuses on whole dates. The ideas can readily be extended from dates to times with hours, minutes, and seconds.

Times and dates are complex data types, comprised of six different components and an optional seventh. Years, months, days, hours, minutes, and seconds are the six. In addition, time zone information may or may not be present. Fortunately, databases handle dates and times consistently, ...