SQL Tutorial: 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. 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 the 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 ISO0861 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, whereas the example above was at the microsecond precision. 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. Finally, interval types store date and time data as a period of time in years, months, days, hours, seconds, etc. For example, four 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 three day rental duration by adding an interval of three days to the rental date column. For from the rental table in our DVD database, 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 time zone. While this comes in handy in certain situations, most of the time. You'll work with timestamp values without a time zone, which is the default behavior. Now it's your turn. Let's take a closer look at date and time data types in.