WEBVTT 00:00.900 --> 00:06.600 Whenever you enter a date into a search form, you are reaping the benefit of the databases, having 00:06.600 --> 00:12.210 an awareness of the current time and received from the server, plus the ability to handle formats for 00:12.210 --> 00:17.370 dates, times and nuances of the calendar, such as a leap year and time zones. 00:17.370 --> 00:22.740 So this is essential for storytelling with data because they usually, when something occurred, is 00:22.740 --> 00:28.440 usually a valuable as question, as who, what or how many were involved. 00:28.440 --> 00:33.870 So here we have four types of data and times here. 00:33.870 --> 00:34.950 So I will open this. 00:35.160 --> 00:35.940 So let us here. 00:35.940 --> 00:44.010 So we have data, data and time data types. 00:44.400 --> 00:48.750 So here we have firstly, of course we will need to make some here. 00:49.050 --> 00:57.990 We have a data type here and after that we will have storage size, storage size and after that we will 00:58.410 --> 01:00.780 description. 01:00.780 --> 01:02.760 After that we will add range. 01:03.120 --> 01:09.780 So here firstly we will have timestamp time stamp. 01:09.810 --> 01:16.230 The storage size of timestamp is eight bytes and description is date. 01:17.480 --> 01:19.010 They date. 01:19.800 --> 01:20.520 And. 01:21.680 --> 01:21.860 Um. 01:21.980 --> 01:22.340 Type. 01:23.070 --> 01:23.250 Yeah. 01:23.550 --> 01:24.360 Date and time. 01:25.530 --> 01:28.140 Here and let's make a range here. 01:28.140 --> 01:29.370 So that's it. 01:29.370 --> 01:31.080 And the ranges. 01:35.150 --> 01:39.230 44,713 01:40.370 --> 01:46.000 here and PC And from this here. 01:46.670 --> 01:49.910 2942. 01:51.220 --> 01:55.180 And 7907, six, seven, six. 01:55.220 --> 01:56.340 Add here. 01:58.060 --> 01:58.800 That's it. 01:58.810 --> 01:59.830 And. 02:00.660 --> 02:02.130 We also have date. 02:03.410 --> 02:03.860 Date. 02:04.810 --> 02:08.680 And in date we have storage size of four bytes. 02:10.010 --> 02:11.090 Four bytes. 02:11.420 --> 02:17.090 And here we have only date, but no time. 02:18.390 --> 02:20.040 And the range is. 02:21.820 --> 02:24.180 The range starts Same. 02:25.660 --> 02:32.500 Back to like it has more add than timestamp. 02:32.590 --> 02:37.390 So it's 5874. 02:39.300 --> 02:40.810 800 897. 02:42.570 --> 02:42.990 Add. 02:44.220 --> 02:48.960 And here we also have time, which is time is eight bytes. 02:49.740 --> 02:50.970 Eight bytes. 02:50.970 --> 02:55.470 And the time only stores time and no date. 02:55.920 --> 03:00.180 The theme time starts from 000. 03:00.390 --> 03:07.170 And also it counts seconds to 24 0000 here. 03:07.320 --> 03:09.940 And we also have interval. 03:09.960 --> 03:11.460 Lastly, interval. 03:11.670 --> 03:13.530 This is here. 03:13.560 --> 03:16.440 This is 16 bytes. 03:17.130 --> 03:25.620 And here we have just had time interval interval and it starts plus minus here. 03:27.860 --> 03:36.650 118 000000 181 178 million years. 03:37.930 --> 03:38.670 That's it. 03:38.710 --> 03:42.520 So now the time stamp here. 03:43.390 --> 03:44.290 Time stamp. 03:44.290 --> 03:45.910 Let's actually make it like that. 03:45.940 --> 03:51.220 The time stamp records date and time, which are useful for a range of situations. 03:51.220 --> 03:58.270 You might track departures and arrivals of passenger flights, a schedule of Major League baseball games, 03:58.270 --> 04:01.060 football games or incidents along a timeline. 04:01.060 --> 04:09.790 So you will always, almost always want to add the keywords with time zone to ensure that the time recorded 04:09.790 --> 04:14.020 for an event includes the time zone where it occurred. 04:14.050 --> 04:20.320 Otherwise, times recorded in the various places around the globe become impossible to compare, so 04:20.320 --> 04:24.370 the former timestamp with Time zone is a part of the SQL standard. 04:24.400 --> 04:32.260 With PostgreSQL you can specify the same data type using the time stamps time. 04:33.600 --> 04:35.640 Stamps here. 04:35.760 --> 04:38.490 And we also have date here. 04:39.000 --> 04:40.800 This is records just a date. 04:40.800 --> 04:43.350 And this is a part of the SQL standard. 04:43.380 --> 04:45.840 We have also time here. 04:46.260 --> 04:48.300 The time records just a time. 04:48.300 --> 04:56.040 And this is a part of the SQL standard, although you can add with time zone keywords without a date, 04:56.040 --> 04:58.710 the time zone will be meaningless. 04:58.950 --> 05:01.050 And here we have also. 05:02.320 --> 05:03.910 Uh, interval. 05:06.940 --> 05:12.850 The interval holds a value representing an unit of time expressed in the format quantity unit, and 05:12.850 --> 05:17.500 it doesn't record the start or end of the time period only its length. 05:17.620 --> 05:23.080 So examples include, for example, 12 days or eight hours. 05:23.200 --> 05:30.550 And here you can also find the SQL PostgreSQL documentation lists, unit values ranging from microseconds 05:30.550 --> 05:31.840 to millennium. 05:32.050 --> 05:35.620 So now let's go to writing our code here, right? 05:36.330 --> 05:42.890 So we also had some code that I will share that as an attachment, but it has nothing to do with our 05:42.900 --> 05:43.950 today's lectures. 05:44.790 --> 05:45.720 So we will. 05:46.590 --> 05:47.580 Uh, create a new table. 05:47.580 --> 05:50.470 That's why we're going to go to here. 05:50.490 --> 05:54.060 As you can see, we have tables right click on this create table. 05:54.060 --> 05:57.480 Now, of course, we're going to use the query tool as well. 05:57.600 --> 05:59.220 We will create SQL queries. 05:59.220 --> 06:02.970 So create table and date. 06:02.970 --> 06:11.910 I will explain all of these codes here, date time types, and here we will have timestamp also. 06:11.910 --> 06:12.510 Let's actually. 06:12.510 --> 06:12.930 Yeah. 06:12.960 --> 06:13.980 Timestamp. 06:16.710 --> 06:20.970 Column and timestamp with time zone. 06:22.220 --> 06:23.600 Time zone. 06:24.940 --> 06:25.200 Here. 06:25.210 --> 06:26.830 After that we will have interval. 06:26.830 --> 06:27.320 Column. 06:27.790 --> 06:28.660 Interval. 06:28.870 --> 06:29.650 Column. 06:29.650 --> 06:31.030 Interval. 06:32.520 --> 06:33.150 That's it. 06:33.570 --> 06:37.860 And here we will also add another code here, which is insert. 06:37.860 --> 06:42.990 We will insert into datetime types. 06:42.990 --> 06:46.110 And this we're going to have some values in it. 06:46.140 --> 06:50.910 Values, which is these values include 2000. 06:52.640 --> 06:53.570 23. 06:54.690 --> 06:56.850 The month and then day. 06:58.260 --> 06:58.560 Here. 06:58.560 --> 07:00.390 After that, we will add. 07:02.120 --> 07:08.210 Hours here ist And after that we will add here. 07:09.360 --> 07:10.140 To. 07:10.920 --> 07:11.580 Base. 07:13.590 --> 07:16.020 Note that another value in it. 07:17.660 --> 07:21.980 So here we will add same date, 2103. 07:23.170 --> 07:24.040 January. 07:25.110 --> 07:29.070 Or this number here or this 31. 07:29.940 --> 07:34.380 And here, 0100 minus eight. 07:35.710 --> 07:37.720 And after that, we will make it. 07:38.850 --> 07:39.510 One. 07:40.770 --> 07:41.280 Month. 07:42.770 --> 07:44.870 After that, we will add a comma here. 07:45.110 --> 07:55.370 We will add another 1323 1230 101 here and we will add let's actually add some Australia, right? 07:55.370 --> 07:57.620 So Australia. 07:58.830 --> 07:59.700 Mel. 08:00.960 --> 08:03.390 Or so here. 08:03.390 --> 08:04.140 We will add. 08:04.140 --> 08:06.750 After that, we will do it one. 08:07.390 --> 08:08.290 Century. 08:10.610 --> 08:11.780 And after that. 08:12.610 --> 08:22.090 We will list all of the information that we wrote here, which is using the select asterisk from date 08:22.510 --> 08:24.220 time types. 08:25.070 --> 08:34.670 Here and here we create a table with a column for both types and here and insert we insert a. 08:35.900 --> 08:39.920 Uh, for also, we also need to insert another row here, which is. 08:39.920 --> 08:41.150 It's going to be now. 08:41.930 --> 08:42.860 Now. 08:44.450 --> 08:50.030 You will see what this magic does, what this function does, and it's going to be one week. 08:50.330 --> 08:52.400 So that's it. 08:53.180 --> 08:58.310 So here we first create a column for both types. 08:59.690 --> 09:00.250 Timestamp. 09:00.440 --> 09:02.960 Timestamp with time zone and interval. 09:03.200 --> 09:04.760 And after that. 09:06.130 --> 09:08.560 I insert our four rows. 09:08.560 --> 09:17.230 So for the first three rows are we Our insert for the timestamp column uses the same date and time. 09:17.230 --> 09:19.780 December 31st, 2020. 09:20.290 --> 09:21.130 The chamber. 09:21.130 --> 09:23.470 Yeah, it's the chamber. 09:23.500 --> 09:35.080 December 31st, 2023 and at 1 a.m. and using the International Organization for Standardization ISO 09:35.200 --> 09:37.060 format for the dates and times. 09:37.060 --> 09:46.900 Firstly, we got years, months and days and also we have hours, minutes and seconds. 09:46.990 --> 09:52.420 In this case we didn't add any seconds here, so SQL supports additional date formats such as months, 09:52.420 --> 09:57.070 days, years, but ISO is recommended for portability worldwide. 09:57.130 --> 10:06.140 So following the time we specify a time zone but use a different format in each of the first three results. 10:06.140 --> 10:13.430 And so in the first row we use the abbreviation here, which is a Eastern Standard Time in the United 10:13.430 --> 10:14.170 States. 10:14.180 --> 10:19.550 In the second row, we set the time zone with the value minus eight. 10:19.580 --> 10:28.250 That represents the number of hours difference or offset from the coordinated universal time UTC and 10:28.250 --> 10:30.680 the time standard for the world. 10:30.680 --> 10:40.970 The value UTC is plus -zero hours and zero minutes, so minus eight specifies a time zone, eight hours 10:41.000 --> 10:42.680 behind UTC. 10:42.800 --> 10:51.920 So in in the United States, when daylight daylight saving time is in effect, minus eight is the value 10:51.920 --> 10:53.660 for the Alaska time zone. 10:53.660 --> 11:02.180 So from November through the early March, when the United States reverts to a standard time, it refers 11:02.180 --> 11:05.960 to the Pacific Time, time zone. 11:06.080 --> 11:14.540 So for the third row here, we specify the time zone using the name of an area and location, which 11:14.540 --> 11:16.010 is Australia. 11:16.040 --> 11:17.270 Melbourne. 11:17.930 --> 11:24.520 So that format uses values found in standard time zone database, often employed in computer programming. 11:24.530 --> 11:35.000 You can learn this more from here in Wikipedia TZ database and here you will see. 11:36.960 --> 11:39.480 Time zones, as you can see here, America. 11:41.100 --> 11:41.690 Asia. 11:42.300 --> 11:43.720 Macassar Manila. 11:43.770 --> 11:44.220 Muscat. 11:44.250 --> 11:44.940 Nicosia. 11:44.940 --> 11:46.050 And so on. 11:46.050 --> 11:48.780 And here we also have the. 11:49.970 --> 11:54.980 UTC offset State DST and time zone abbreviation. 11:54.980 --> 11:55.760 That's it. 11:58.130 --> 12:00.320 And let's run the script now. 12:01.600 --> 12:03.700 Location data type does not exist. 12:03.700 --> 12:03.910 So. 12:03.910 --> 12:04.300 Because. 12:04.300 --> 12:04.960 Yes. 12:09.700 --> 12:14.680 Create data date out, not data date time. 12:15.610 --> 12:16.210 That's it. 12:16.210 --> 12:19.630 As you can see here, this is our output. 12:19.630 --> 12:26.050 Even though we supply the same date and time in the first three rows on the timestamp column. 12:26.080 --> 12:32.590 As you can see here, the rows output differentiates, right? 12:32.590 --> 12:39.310 So the reason is that Pgadmin reports the date and time relative to my time zone, which is the result 12:39.310 --> 12:48.310 shown is indicated by the UTC offset of minus five you see here, minus five is T minus one. 12:48.310 --> 12:49.580 And Australia, Melbourne. 12:50.310 --> 12:52.770 So at the end of the each time step of course. 12:52.770 --> 13:04.650 So a use UTC offset of -0 five means five hours behind UTC equivalent to the US Eastern time zone during 13:04.650 --> 13:08.250 fall and winter months when standard time is observed. 13:08.250 --> 13:13.590 So if you live in different time zone you will likely to see a different offset. 13:13.590 --> 13:18.090 And the times and dates also may differ from what's shown here. 13:18.090 --> 13:25.710 So we can change how PostgreSQL reports the timestamp values and I will cover how to do that, plus 13:25.710 --> 13:31.860 other tips for wrangling dates and times in the next lectures. 13:32.910 --> 13:41.670 And finally, the interval column shows the value you entered and PostgreSQL changed one century to 13:41.670 --> 13:43.380 100 years here. 13:44.220 --> 13:53.090 And one week to seven days because of its preferred default setting for interval the display. 13:53.100 --> 13:59.610 And you can also read the interval input section of the PostgreSQL documentation on their official website 13:59.610 --> 14:03.240 to learn more about these interval inputs here. 14:03.240 --> 14:05.040 So I'm waiting you in next lecture.