1
00:00:00,900 --> 00:00:06,600
Whenever you enter a date into a search form, you are reaping the benefit of the databases, having

2
00:00:06,600 --> 00:00:12,210
an awareness of the current time and received from the server, plus the ability to handle formats for

3
00:00:12,210 --> 00:00:17,370
dates, times and nuances of the calendar, such as a leap year and time zones.

4
00:00:17,370 --> 00:00:22,740
So this is essential for storytelling with data because they usually, when something occurred, is

5
00:00:22,740 --> 00:00:28,440
usually a valuable as question, as who, what or how many were involved.

6
00:00:28,440 --> 00:00:33,870
So here we have four types of data and times here.

7
00:00:33,870 --> 00:00:34,950
So I will open this.

8
00:00:35,160 --> 00:00:35,940
So let us here.

9
00:00:35,940 --> 00:00:44,010
So we have data, data and time data types.

10
00:00:44,400 --> 00:00:48,750
So here we have firstly, of course we will need to make some here.

11
00:00:49,050 --> 00:00:57,990
We have a data type here and after that we will have storage size, storage size and after that we will

12
00:00:58,410 --> 00:01:00,780
description.

13
00:01:00,780 --> 00:01:02,760
After that we will add range.

14
00:01:03,120 --> 00:01:09,780
So here firstly we will have timestamp time stamp.

15
00:01:09,810 --> 00:01:16,230
The storage size of timestamp is eight bytes and description is date.

16
00:01:17,480 --> 00:01:19,010
They date.

17
00:01:19,800 --> 00:01:20,520
And.

18
00:01:21,680 --> 00:01:21,860
Um.

19
00:01:21,980 --> 00:01:22,340
Type.

20
00:01:23,070 --> 00:01:23,250
Yeah.

21
00:01:23,550 --> 00:01:24,360
Date and time.

22
00:01:25,530 --> 00:01:28,140
Here and let's make a range here.

23
00:01:28,140 --> 00:01:29,370
So that's it.

24
00:01:29,370 --> 00:01:31,080
And the ranges.

25
00:01:35,150 --> 00:01:39,230
44,713

26
00:01:40,370 --> 00:01:46,000
here and PC And from this here.

27
00:01:46,670 --> 00:01:49,910
2942.

28
00:01:51,220 --> 00:01:55,180
And 7907, six, seven, six.

29
00:01:55,220 --> 00:01:56,340
Add here.

30
00:01:58,060 --> 00:01:58,800
That's it.

31
00:01:58,810 --> 00:01:59,830
And.

32
00:02:00,660 --> 00:02:02,130
We also have date.

33
00:02:03,410 --> 00:02:03,860
Date.

34
00:02:04,810 --> 00:02:08,680
And in date we have storage size of four bytes.

35
00:02:10,010 --> 00:02:11,090
Four bytes.

36
00:02:11,420 --> 00:02:17,090
And here we have only date, but no time.

37
00:02:18,390 --> 00:02:20,040
And the range is.

38
00:02:21,820 --> 00:02:24,180
The range starts Same.

39
00:02:25,660 --> 00:02:32,500
Back to like it has more add than timestamp.

40
00:02:32,590 --> 00:02:37,390
So it's 5874.

41
00:02:39,300 --> 00:02:40,810
800 897.

42
00:02:42,570 --> 00:02:42,990
Add.

43
00:02:44,220 --> 00:02:48,960
And here we also have time, which is time is eight bytes.

44
00:02:49,740 --> 00:02:50,970
Eight bytes.

45
00:02:50,970 --> 00:02:55,470
And the time only stores time and no date.

46
00:02:55,920 --> 00:03:00,180
The theme time starts from 000.

47
00:03:00,390 --> 00:03:07,170
And also it counts seconds to 24 0000 here.

48
00:03:07,320 --> 00:03:09,940
And we also have interval.

49
00:03:09,960 --> 00:03:11,460
Lastly, interval.

50
00:03:11,670 --> 00:03:13,530
This is here.

51
00:03:13,560 --> 00:03:16,440
This is 16 bytes.

52
00:03:17,130 --> 00:03:25,620
And here we have just had time interval interval and it starts plus minus here.

53
00:03:27,860 --> 00:03:36,650
118 000000 181 178 million years.

54
00:03:37,930 --> 00:03:38,670
That's it.

55
00:03:38,710 --> 00:03:42,520
So now the time stamp here.

56
00:03:43,390 --> 00:03:44,290
Time stamp.

57
00:03:44,290 --> 00:03:45,910
Let's actually make it like that.

58
00:03:45,940 --> 00:03:51,220
The time stamp records date and time, which are useful for a range of situations.

59
00:03:51,220 --> 00:03:58,270
You might track departures and arrivals of passenger flights, a schedule of Major League baseball games,

60
00:03:58,270 --> 00:04:01,060
football games or incidents along a timeline.

61
00:04:01,060 --> 00:04:09,790
So you will always, almost always want to add the keywords with time zone to ensure that the time recorded

62
00:04:09,790 --> 00:04:14,020
for an event includes the time zone where it occurred.

63
00:04:14,050 --> 00:04:20,320
Otherwise, times recorded in the various places around the globe become impossible to compare, so

64
00:04:20,320 --> 00:04:24,370
the former timestamp with Time zone is a part of the SQL standard.

65
00:04:24,400 --> 00:04:32,260
With PostgreSQL you can specify the same data type using the time stamps time.

66
00:04:33,600 --> 00:04:35,640
Stamps here.

67
00:04:35,760 --> 00:04:38,490
And we also have date here.

68
00:04:39,000 --> 00:04:40,800
This is records just a date.

69
00:04:40,800 --> 00:04:43,350
And this is a part of the SQL standard.

70
00:04:43,380 --> 00:04:45,840
We have also time here.

71
00:04:46,260 --> 00:04:48,300
The time records just a time.

72
00:04:48,300 --> 00:04:56,040
And this is a part of the SQL standard, although you can add with time zone keywords without a date,

73
00:04:56,040 --> 00:04:58,710
the time zone will be meaningless.

74
00:04:58,950 --> 00:05:01,050
And here we have also.

75
00:05:02,320 --> 00:05:03,910
Uh, interval.

76
00:05:06,940 --> 00:05:12,850
The interval holds a value representing an unit of time expressed in the format quantity unit, and

77
00:05:12,850 --> 00:05:17,500
it doesn't record the start or end of the time period only its length.

78
00:05:17,620 --> 00:05:23,080
So examples include, for example, 12 days or eight hours.

79
00:05:23,200 --> 00:05:30,550
And here you can also find the SQL PostgreSQL documentation lists, unit values ranging from microseconds

80
00:05:30,550 --> 00:05:31,840
to millennium.

81
00:05:32,050 --> 00:05:35,620
So now let's go to writing our code here, right?

82
00:05:36,330 --> 00: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

83
00:05:42,900 --> 00:05:43,950
today's lectures.

84
00:05:44,790 --> 00:05:45,720
So we will.

85
00:05:46,590 --> 00:05:47,580
Uh, create a new table.

86
00:05:47,580 --> 00:05:50,470
That's why we're going to go to here.

87
00:05:50,490 --> 00:05:54,060
As you can see, we have tables right click on this create table.

88
00:05:54,060 --> 00:05:57,480
Now, of course, we're going to use the query tool as well.

89
00:05:57,600 --> 00:05:59,220
We will create SQL queries.

90
00:05:59,220 --> 00:06:02,970
So create table and date.

91
00:06:02,970 --> 00:06:11,910
I will explain all of these codes here, date time types, and here we will have timestamp also.

92
00:06:11,910 --> 00:06:12,510
Let's actually.

93
00:06:12,510 --> 00:06:12,930
Yeah.

94
00:06:12,960 --> 00:06:13,980
Timestamp.

95
00:06:16,710 --> 00:06:20,970
Column and timestamp with time zone.

96
00:06:22,220 --> 00:06:23,600
Time zone.

97
00:06:24,940 --> 00:06:25,200
Here.

98
00:06:25,210 --> 00:06:26,830
After that we will have interval.

99
00:06:26,830 --> 00:06:27,320
Column.

100
00:06:27,790 --> 00:06:28,660
Interval.

101
00:06:28,870 --> 00:06:29,650
Column.

102
00:06:29,650 --> 00:06:31,030
Interval.

103
00:06:32,520 --> 00:06:33,150
That's it.

104
00:06:33,570 --> 00:06:37,860
And here we will also add another code here, which is insert.

105
00:06:37,860 --> 00:06:42,990
We will insert into datetime types.

106
00:06:42,990 --> 00:06:46,110
And this we're going to have some values in it.

107
00:06:46,140 --> 00:06:50,910
Values, which is these values include 2000.

108
00:06:52,640 --> 00:06:53,570
23.

109
00:06:54,690 --> 00:06:56,850
The month and then day.

110
00:06:58,260 --> 00:06:58,560
Here.

111
00:06:58,560 --> 00:07:00,390
After that, we will add.

112
00:07:02,120 --> 00:07:08,210
Hours here ist And after that we will add here.

113
00:07:09,360 --> 00:07:10,140
To.

114
00:07:10,920 --> 00:07:11,580
Base.

115
00:07:13,590 --> 00:07:16,020
Note that another value in it.

116
00:07:17,660 --> 00:07:21,980
So here we will add same date, 2103.

117
00:07:23,170 --> 00:07:24,040
January.

118
00:07:25,110 --> 00:07:29,070
Or this number here or this 31.

119
00:07:29,940 --> 00:07:34,380
And here, 0100 minus eight.

120
00:07:35,710 --> 00:07:37,720
And after that, we will make it.

121
00:07:38,850 --> 00:07:39,510
One.

122
00:07:40,770 --> 00:07:41,280
Month.

123
00:07:42,770 --> 00:07:44,870
After that, we will add a comma here.

124
00:07:45,110 --> 00:07:55,370
We will add another 1323 1230 101 here and we will add let's actually add some Australia, right?

125
00:07:55,370 --> 00:07:57,620
So Australia.

126
00:07:58,830 --> 00:07:59,700
Mel.

127
00:08:00,960 --> 00:08:03,390
Or so here.

128
00:08:03,390 --> 00:08:04,140
We will add.

129
00:08:04,140 --> 00:08:06,750
After that, we will do it one.

130
00:08:07,390 --> 00:08:08,290
Century.

131
00:08:10,610 --> 00:08:11,780
And after that.

132
00:08:12,610 --> 00:08:22,090
We will list all of the information that we wrote here, which is using the select asterisk from date

133
00:08:22,510 --> 00:08:24,220
time types.

134
00:08:25,070 --> 00:08:34,670
Here and here we create a table with a column for both types and here and insert we insert a.

135
00:08:35,900 --> 00:08:39,920
Uh, for also, we also need to insert another row here, which is.

136
00:08:39,920 --> 00:08:41,150
It's going to be now.

137
00:08:41,930 --> 00:08:42,860
Now.

138
00:08:44,450 --> 00:08:50,030
You will see what this magic does, what this function does, and it's going to be one week.

139
00:08:50,330 --> 00:08:52,400
So that's it.

140
00:08:53,180 --> 00:08:58,310
So here we first create a column for both types.

141
00:08:59,690 --> 00:09:00,250
Timestamp.

142
00:09:00,440 --> 00:09:02,960
Timestamp with time zone and interval.

143
00:09:03,200 --> 00:09:04,760
And after that.

144
00:09:06,130 --> 00:09:08,560
I insert our four rows.

145
00:09:08,560 --> 00:09:17,230
So for the first three rows are we Our insert for the timestamp column uses the same date and time.

146
00:09:17,230 --> 00:09:19,780
December 31st, 2020.

147
00:09:20,290 --> 00:09:21,130
The chamber.

148
00:09:21,130 --> 00:09:23,470
Yeah, it's the chamber.

149
00:09:23,500 --> 00:09:35,080
December 31st, 2023 and at 1 a.m. and using the International Organization for Standardization ISO

150
00:09:35,200 --> 00:09:37,060
format for the dates and times.

151
00:09:37,060 --> 00:09:46,900
Firstly, we got years, months and days and also we have hours, minutes and seconds.

152
00:09:46,990 --> 00:09:52,420
In this case we didn't add any seconds here, so SQL supports additional date formats such as months,

153
00:09:52,420 --> 00:09:57,070
days, years, but ISO is recommended for portability worldwide.

154
00:09:57,130 --> 00:10:06,140
So following the time we specify a time zone but use a different format in each of the first three results.

155
00:10:06,140 --> 00:10:13,430
And so in the first row we use the abbreviation here, which is a Eastern Standard Time in the United

156
00:10:13,430 --> 00:10:14,170
States.

157
00:10:14,180 --> 00:10:19,550
In the second row, we set the time zone with the value minus eight.

158
00:10:19,580 --> 00:10:28,250
That represents the number of hours difference or offset from the coordinated universal time UTC and

159
00:10:28,250 --> 00:10:30,680
the time standard for the world.

160
00:10:30,680 --> 00:10:40,970
The value UTC is plus -zero hours and zero minutes, so minus eight specifies a time zone, eight hours

161
00:10:41,000 --> 00:10:42,680
behind UTC.

162
00:10:42,800 --> 00:10:51,920
So in in the United States, when daylight daylight saving time is in effect, minus eight is the value

163
00:10:51,920 --> 00:10:53,660
for the Alaska time zone.

164
00:10:53,660 --> 00:11:02,180
So from November through the early March, when the United States reverts to a standard time, it refers

165
00:11:02,180 --> 00:11:05,960
to the Pacific Time, time zone.

166
00:11:06,080 --> 00:11:14,540
So for the third row here, we specify the time zone using the name of an area and location, which

167
00:11:14,540 --> 00:11:16,010
is Australia.

168
00:11:16,040 --> 00:11:17,270
Melbourne.

169
00:11:17,930 --> 00:11:24,520
So that format uses values found in standard time zone database, often employed in computer programming.

170
00:11:24,530 --> 00:11:35,000
You can learn this more from here in Wikipedia TZ database and here you will see.

171
00:11:36,960 --> 00:11:39,480
Time zones, as you can see here, America.

172
00:11:41,100 --> 00:11:41,690
Asia.

173
00:11:42,300 --> 00:11:43,720
Macassar Manila.

174
00:11:43,770 --> 00:11:44,220
Muscat.

175
00:11:44,250 --> 00:11:44,940
Nicosia.

176
00:11:44,940 --> 00:11:46,050
And so on.

177
00:11:46,050 --> 00:11:48,780
And here we also have the.

178
00:11:49,970 --> 00:11:54,980
UTC offset State DST and time zone abbreviation.

179
00:11:54,980 --> 00:11:55,760
That's it.

180
00:11:58,130 --> 00:12:00,320
And let's run the script now.

181
00:12:01,600 --> 00:12:03,700
Location data type does not exist.

182
00:12:03,700 --> 00:12:03,910
So.

183
00:12:03,910 --> 00:12:04,300
Because.

184
00:12:04,300 --> 00:12:04,960
Yes.

185
00:12:09,700 --> 00:12:14,680
Create data date out, not data date time.

186
00:12:15,610 --> 00:12:16,210
That's it.

187
00:12:16,210 --> 00:12:19,630
As you can see here, this is our output.

188
00:12:19,630 --> 00:12:26,050
Even though we supply the same date and time in the first three rows on the timestamp column.

189
00:12:26,080 --> 00:12:32,590
As you can see here, the rows output differentiates, right?

190
00:12:32,590 --> 00:12:39,310
So the reason is that Pgadmin reports the date and time relative to my time zone, which is the result

191
00:12:39,310 --> 00:12:48,310
shown is indicated by the UTC offset of minus five you see here, minus five is T minus one.

192
00:12:48,310 --> 00:12:49,580
And Australia, Melbourne.

193
00:12:50,310 --> 00:12:52,770
So at the end of the each time step of course.

194
00:12:52,770 --> 00:13:04,650
So a use UTC offset of -0 five means five hours behind UTC equivalent to the US Eastern time zone during

195
00:13:04,650 --> 00:13:08,250
fall and winter months when standard time is observed.

196
00:13:08,250 --> 00:13:13,590
So if you live in different time zone you will likely to see a different offset.

197
00:13:13,590 --> 00:13:18,090
And the times and dates also may differ from what's shown here.

198
00:13:18,090 --> 00:13:25,710
So we can change how PostgreSQL reports the timestamp values and I will cover how to do that, plus

199
00:13:25,710 --> 00:13:31,860
other tips for wrangling dates and times in the next lectures.

200
00:13:32,910 --> 00:13:41,670
And finally, the interval column shows the value you entered and PostgreSQL changed one century to

201
00:13:41,670 --> 00:13:43,380
100 years here.

202
00:13:44,220 --> 00:13:53,090
And one week to seven days because of its preferred default setting for interval the display.

203
00:13:53,100 --> 00:13:59,610
And you can also read the interval input section of the PostgreSQL documentation on their official website

204
00:13:59,610 --> 00:14:03,240
to learn more about these interval inputs here.

205
00:14:03,240 --> 00:14:05,040
So I'm waiting you in next lecture.