1. Date/time types in BigQuery
Many datasets in data warehouses contain information and events tied to a specific date or time, making the ability to work with date and time data critical.
2. Why date and times matter
Dates are important for many reasons, but three reasons tend to be at the top of the list. First, dates allow us to filter long datasets. They also allow us to extract parts of dates, such as a day of the week or month, to use in our analysis. Finally, we can use dates to partition our data for faster queries in BigQuery. This course won't cover this, but it is a common partitioning strategy.
3. Dates
A date is a single day. This includes the day, month, and year. As we can see here, in this query, we are constructing a date of May 19th, 2010, or the day BigQuery was launched.
4. Timestamps
A TIMESTAMP is an absolute point in time, regardless of what timezone the timestamp was created in. A timestamp contains the date and the time. The timestamp always defaults to Coordinated Universal Time or UTC unless one is defined.
5. Datetime and Time
DATETIME and TIME are data types that are used less frequently. TIME represents only time without a date. DATETIME is comparable to a TIMESTAMP since it contains a date and a time. These data types are not absolute since they do not require a time zone. This course will focus on the DATE and TIMESTAMP data types.
6. Date and timestamp parts
The date and timestamp functions we will look at use date or timestamp parts, allowing us to find specific intervals from the date or timestamp we are analyzing. As we can see here, there are several date parts for dates, such as days, weeks, months, year, and time. For timestamps, there are fewer such as hours, minutes, and seconds.
7. ADD, SUBTRACT, and DIFF
There are several functions to work with date and time data. The first are functions to add, subtract, or find the difference between a date or timestamp. To add or subtract, we can use the "date-add", "date-sub", "timestamp-add", and "timestamp-sub" functions. In this query, we are adding five days to our timestamp. Notice the keyword "interval" followed by an integer, which defines the interval between the date and our date part. Date and "timestamp-diff" allow us to find the difference between dates or times. For example, we can see the number of days between two dates in this example.
8. EXTRACT
EXTRACT uses the same date parts but extracts that specific date or timestamp part from the date. Here, we are extracting the day of the week from the launch date; in this case, four equates to a Wednesday using the American convention.
9. FORMAT
Formatting dates is helpful to make data more legible if we share that data with business stakeholders. Here are two examples that format the date in month, day, and year formatting and one with the day of the week and date spelled out. There are many different formatting options so please reference the citation for the complete list.
10. Current date/timestamp
Finally, these functions return the current timestamp or current date. This can be used with the functions we reviewed to find the difference from the current date or timestamp.
11. Cheat sheet
As a review, here are the core concepts from this video.
12. Let's practice!
Let's test out our new date/time functions with our e-commerce data!