Data Wrangling on the DATE Data Type

Most transactional data has some or other form of date-type data that holds meaningful information, especially when doing analysis on historical data or data modeling. In SQL, the DATE data type is used to store date values. Dates are stored in the format YYYY-MM-DD, where YYYY represents the year, MM represents the month, and DD represents the day. For example, January 5, 2020 would be stored as 2020-01-05. The DATE data type is supported by most SQL implementations, including MySQL, PostgreSQL, and SQLite. In addition to the DATE data type, most SQL implementations also support additional data types for storing time or timestamp information, such as TIME and TIMESTAMP.

For example, extracting just the month information from a date column in a table. The goal of this chapter is to help you achieve hands-on experience in how to extract meaningful information after data wrangling on the DATE data type.

In this chapter, we will cover the following...

SQL DATE data type functions

The DATE data type typically takes 4 bytes of storage in a database and can hold date values with a range of 1000-01-01 to 9999-12-31. The TIMESTAMP data type is similar to the DATE data type, but it also includes information about the time to a precision of one second. The format used to store timestamp values is typically YYYY-MM-DD HH:MM:SS.

There are several date functions provided by the SQL engine to manipulate the date and time information stored in date or DateTime columns. Let’s look at each of them in detail.

Figure 4.1 – DateTime functions

EXTRACT

The SQL EXTRACT function is used to extract a part of a date or a timestamp value. The function takes two arguments: the first is the part of the date or timestamp that you want to extract (such as the year, month, or day), and the second is the date or timestamp value that you want to extract from. The syntax for the EXTRACT function is as follows:

...

Summary

This brings us to the end of this chapter, and by now, you should have learned about the following:

• The different ways to store date and time information in a SQL database, specifically the DATETIME, DATE, and TIME data types
• The syntax and format for each of these data types
• Various built-in functions provided by SQL for manipulating and extracting information from DateTime data, such as NOW(), DATE(), and DATEDIFF()
• How to perform calculations and comparisons with DateTime data using SQL functions
• The importance of good data validation and integrity in order to ensure that the data stored in DateTime columns is accurate, consistent, and in the correct format

In the next chapter, we will learn about Null values in a dataset and will look at ways to avoid and handle such values in a dataset. Handling null values in SQL is an important step before introducing data wrangling, as it ensures that your data is accurate, consistent, and ready for data...