SQL Tutorial: Date and time data types

Want to learn more? Take the full course at https://learn.datacamp.com/courses/functions-for-manipulating-data-in-postgresql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- 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 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. 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, 4 days. INTERVALs are useful when you want to do arithmetic on date and time columns.