1. Date parsing and formatting
Temporal data is commonly encountered when working with databases. In this lesson, we will focus on cleaning dates via parsing and formatting.
2. Parsing dates with the DATE() function
Returning to our restaurant inspection dataset, we see several columns with date values. Two such columns are inspection_date and grade_date.
These are TEXT columns. Actions such as checking that a value is in a specific date range or calculating the time interval between dates are not possible using this representation.
PostgreSQL provides a DATE() function capable of converting a column of date strings into DATE values.
Given a column name containing string dates or a string literal representing a date, the DATE() function converts all non-NULL values into DATE values.
As an example, using DATE() with the string literal '2019-12-01' as an argument returns the PostgreSQL DATE representing this string date.
3. Parsing dates with the DATE() function
Converting the column values for inspection_date and grade_date into DATE values can be accomplished with a query such as the one displayed here.
These DATE values use the default display format for dates. This format begins with the year and uses dashes to separate date components. The DATE() function expects column values in a set of pre-defined formats. However, you may encounter date strings in alternative formats.
4. Parsing dates with the TO_DATE() function
The TO_DATE() function is useful in such situations. Like the DATE() function, the TO_DATE() function accepts a date string or column of date strings as an argument. This function also requires a format string specifying how to interpret the values in the date string.
For example, attempting to parse the string 'Wednesday, June 10th, 2014' using the DATE() function results in an error. This occurs because PostgreSQL does not expect (by default) date strings in this format.
Using TO_DATE() and the appropriate format string (shown here) enables proper parsing of this date string into a DATE. For the DATE() and TO_DATE() functions, when using a table column as the first argument, a column's date string values must be consistent. Let's consider a case where this consistency is lacking.
5. The NULLIF() expression
Consider a case where the grade_date column contains a dash to represent a missing value. These dashes must be replaced with NULL values to use the DATE() or TO_DATE() function on this column. PostgreSQL will ignore NULL values in date parsing.
While this outcome can be achieved with a CASE expression, PostgreSQL provides the more compact NULLIF() expression for converting a specific string to a NULL value. The expression returns NULL when value1 is equal to value2. Otherwise, the expression returns value1.
The SQL query displayed here converts dash string values in the grade_date column to NULL values.
6. Displaying dates with the TO_CHAR() function
Displaying DATE values in query results using specific formats is also possible in PostgreSQL.
The default display format for dates is the 4 digit year followed by the 2 digit month followed by the 2 digit day using a dash to separate each date component.
The TO_CHAR() function can display a different format such as one that places the date before the month.
The TO_CHAR() function requires two arguments: a DATE and a format string. The function returns a string with the specified format.
As an example, the restaurant_inspection table's inspection_date column contains dates with a 4-digit year.
Using TO_CHAR() as demonstrated here, the date can instead be displayed using a 2-digit year. Notice the use of the double colon shorthand which first converts the inspection_date from a string to a DATE.
Here, we see the resulting inspection_date with a 2-digit year displayed.
7. Date format patterns
Both the TO_DATE() and TO_CHAR() functions require a format string.
The TO_DATE() function uses the format string to parse a date string to create a PostgreSQL DATE.
The TO_CHAR() function uses this format string to display a DATE as a string using the specified format.
8. Date format patterns with TO_DATE()
Some common string format patterns and their usage in the TO_DATE() function are displayed here.
9. Date format patterns with TO_CHAR()
The same string formats with their usage in the TO_CHAR() function are displayed here. However, many other patterns are available. The full list of patterns can be found in the official PostgreSQL documentation linked at the bottom of the slide.
10. Let's practice!
We covered a lot of details on working with dates. Let's put what you've learned into practice!