1. Date/time components and aggregation
As with numerical and character data, sometimes we need to extract components of a date/time, or truncate the value, to aggregate the data in a meaningful way.
2. Common date/time fields
Functions exist to extract individual components of date/time data. These components are called fields. The fields are defined in the Postgres documentation. Many are based on the ISO 8601 standard. Let's look at some common fields starting with the largest unit of time.
First, we can get the century or decade that a timestamp belongs in. January 1st, 2019 is in century 21 and decade 201. Date/time field definitions can be complicated and sometimes counterintuitive. It's always a good idea to read the documentation before using unfamiliar fields.
Next, we can get the year, month, and day fields that make up a date. We can also get the hour, minute, and second fields that make up a time. Week is the week number in the year, based on the ISO 8601 definition. D-O-W is day of week. The week starts with Sunday, which has a value of 0, and ends on Saturday with a value of 6.
3. Extracting fields
To extract these fields from a date or timestamp, you can use the date_part or extract functions. These functions expect a timestamp, but they automatically convert dates to timestamps. These two functions give the same output. They just have different syntax. date_part uses a comma to separate arguments, while extract uses the "from" keyword. The name of the field should be surrounded by single quotes when using the date_part function. With the extract function, the field name can be unquoted.
Finally, the extract function, field name, and FROM keyword are typically written in all uppercase, while the date_part function is written in lowercase like other functions. The extract function actually calls the date_part function.
You can see this in the output of the example query: the result of both the call to date_part and the call to extract are labeled with date_part in the output. The result of 1 indicates the first month of January.
4. Extract to summarize by field
Extracting fields from dates is useful when looking at how data varies by one unit of time across a larger unit of time. For example, how do sales vary by month across years? Using sales from 2010-2016, are sales in January usually higher than those in March?
5. Truncating dates
Instead of extracting single fields, you can also truncate dates and timestamps to a specified level of precision. Remember that dates and timestamps are ordered from left to right, largest units to smallest.
You can use the date_trunc function, which is short for date truncate, to specify how much of a timestamp to keep, as you might with a numeric value. Valid field types include all of those we discussed except day of week. Date_trunc replaces fields smaller than, or less significant than, the one specified with zero, or one, as appropriate. Month and day are set to 1, while time fields are set to 0.
Here, the year and month remain, and the rest of the fields are set to 0 or 1. The timezone remains unchanged.
6. Truncate to keep larger units
Truncating dates is useful when you want to count, average, or sum data associated with timestamps or dates by larger units of time.
For example, starting from individual timestamped sales transactions, what is the monthly trend in sales from June 2017 to January 2019?
7. Time to practice extracting and aggregating dates
Okay, time to start practicing using these new functions.