Date and time data types

- [Instructor] Dates and times are another common type of data that you'll need to store in your relational databases, and the SQL language provides a number of different options, depending on the granularity that you require. You have the option of only storing a date without a time component, or just a time with no date component. All RDBMS platforms support these basic date and time datatypes. Where things get a little bit trickier is when you want to combine a date and a time in a single column. For this, different RDBMS platforms will use a variety of different datatype names. On PostgreSQL, you'll either use the timestamp or timestamp with time zone datatypes, depending on whether or not you want to include the time zone information as well. On SQL Server, the same values will be stored in columns with the datetime2, or datetimeoffset datatypes. There are additional options on each of these datatypes that allow you to customize the precision. And in fact, the default is to record times down to 100 nanoseconds in SQL Server and one microsecond in PostgreSQL, but for most use cases recording the time to the second is precise enough, so you might specify a zero in parentheses after the datatype name in order to only record time to the exact second. So, let's take a look at that on our database servers. In Azure Data Studio, I'm going to select the two trees database that's in the SQL Server instance. And then we'll come up to the file menu and choose open file. In the chapter one folder, I have two different files: one that's set up for SQL Server and the other one that's set up for PostgreSQL. I'm working on the SQL Server database, so I'll select date, time, SQL Server and press open. That'll load up a script that'll allow us to experiment with our date and time datatypes. First, I'm going to set up a new table called time experiment, and it's just going to have two columns: one called column A and one called column B. Column A will use the date, time offset datatype. So this'll store dates and times with time zones. And it'll be precise to 100 nanoseconds. Column B is going to use the date, time offset, but will specify a precision of zero. This'll round the times to the nearest second. Once I create this table, we're going to insert a new row into it. I'm going to use the current system date and time, and apply that into both columns. To do that, I'm using the get date function in SQL Server. Finally, we'll select that row back out of the table. Let's highlight lines two through 12, and press run to execute all of that. And here I can see the result. The times are being stored in UTC time. So this is effectively equivalent to Greenwich Mean Time. In column A, you can see that the time is recorded to 100 nanoseconds. If I take a look at the time in column B, you can see that it's been rounded down. Now, date and time values can be entered into your database in a variety of different ways. Let's scroll down in the script a little bit until I get to line number 14. When entering in data, you're going to want to make sure that your dates and times get wrapped inside of single quotation marks. The default value for dates follows the ISO standard of a four-digit year, a hyphen, a two-digit month, another hyphen and a two-digit day, but most database systems will also understand other formats, such as this one here that uses the abbreviation for January. Depending on your computer's region settings, things can get a little bit confusing when using just numbers. For instance, the date 01/02/03 could refer to January 2nd, 2003; February 1st, 2003; or February 3rd, 2001, depending on the mode that your database is in. So for the best results, you'll want to stick with the standard four-digit year, two-digit month and two-digit day, all separated by hyphens when entering in your dates. Times on the other hand, are usually entered in on a 24 hour clock, like I have here on line number 19. If you wanted to use a 12 hour clock, you could specify PM after it. So for instance, here on line 20, I'm specifying 2:25 PM. Let's highlight lines 15 all the way down to 21 to insert some new rows into that table. Then, I'm going to scroll back up here and rerun line number 12 in order to see the results. So here I can see all of the new values that we just entered in column A. They've all been entered in correctly as January 2nd, 2020, regardless of the format that I supplied up here in the script. We can also see that the two times that I supplied are equivalent at 14 hours and 25 minutes on a 24 hour clock. So that's how you would work with your date and time data inside of SQL Server database. Let's take a look at the PostgreSQL database now. I'm going to select the two trees database in the PostgreS Server, and then come up to file and choose open file again. This time I'll choose date, time PostgreSQL, and press open. Now this script is identical to the script that we ran on the SQL Server instance. The only difference here is that we're using different data types. So column A is going to be timestamp with time zone and column B will be timestamp zero with time zone. We're going to insert these same values using the now function in PostgreSQL, and take a look at the table again. So let's highlight lines two through 12 and press run. And we can see similar results to what we got on our SQL Server instance. Here, we have the full version of the time. And here, in column B, we have the rounded version of the time. Just like on SQL server, though, all of the data gets entered in, in exactly the same way. So we can enter in the exact same date and times that we entered previously. Let's highlight lines 15 through 21 and press run. Then I'll scroll back up and select everything out of the table again. Here, we'll see the exact same results we got on the SQL Server instance. So that's how you can work with your dates and times in PostgreSQL. When you're done experimenting with both of these, make sure you scroll down to the bottom of the script and run line number 23 to drop this table when you're finished.