1. Date and time data types
Next up we will learn about some common date and time data types. Understanding how to work with these data types is crucial for preparing and extracting data for machine learning and data science. In this lesson, you will learn about the precision and features of timestamps, intervals and date and time types.
2. TIMESTAMP data types
You'll find that most of the date and time data you'll be working with in SQL will have a TIMESTAMP data type. TIMESTAMPs contain both a date value and a time value with microsecond precision. These data types are very common because they can be used to record an exact point in time like when a payment was made or a record was last updated.
TIMESTAMPs in PostgreSQL use the ISO 8601 format which is a four digit year followed by a two digit month and day separated by dashes. If you look in the payment table of the Sakila database, you'll see what this format looks like. Notice that the values contain both a date and a time value with seconds precision where the example above was at the microsecond precision.
3. DATE and TIME data types
Next, you'll look at DATE and TIME types. When you only need to store a part of the TIMESTAMP in your database, the DATE and TIME types may be better options.
DATE and TIME types are essentially the date and time values of the TIMESTAMP. As you see in this example, DATE types contain a date value with no time of day while TIME types contain the time of day but without, you guessed it, a date.
If we look at the create_date from the customer table of our DVD rental database you'll see an example of a DATE type.
4. INTERVAL data types
Finally INTERVAL types store date and time data as a period of time in years, months, days, hours, seconds, etc. For example, 4 days.
INTERVALs are useful when you want to do arithmetic on date and time columns. Here you'll see a query where we calculate an expected return date based on a 3 day rental duration by adding an INTERVAL of 3 days to the rental_date column from the rental table in our DVD database.
5. Looking at date and time types
We can use the same technique that we learned in the previous lesson to determine information about a column with a date and time type by querying the INFORMATION_SCHEMA system database.
If we look at the rental_date column of the rental table you'll see that this is a TIMESTAMP. However, you'll also notice the "without time zone" reference. PostgreSQL provides you with the ability to store TIMESTAMP and TIME data types with or without a timezone. While this comes in handy in certain situations, most of the time you'll work with TIMESTAMP values without a timezone which is the default behavior.
6. Let's practice!
Now it's your turn. Let's take a closer look at date and time data types in the exercises.