1
00:00:00,010 --> 00:00:04,130
Michael: Hello and welcome to Postgres fm,
a weekly show about all things PostgreSQL.

2
00:00:04,290 --> 00:00:05,810
I'm Michael, founder of PG Mustard.

3
00:00:05,810 --> 00:00:08,570
This is my cohost Nicolay, founder of Postgres ai.

4
00:00:08,590 --> 00:00:10,140
Hey, Nicolay, what are we talking about

5
00:00:10,520 --> 00:00:11,280
Nikolay: Hi Michael.

6
00:00:11,370 --> 00:00:13,090
Let's talk about something priceless.

7
00:00:13,090 --> 00:00:16,410
Time, time stamps, time zones, and so on.

8
00:00:17,114 --> 00:00:18,564
Michael: I like the priceless reference.

9
00:00:18,564 --> 00:00:19,004
That's great.

10
00:00:19,004 --> 00:00:19,284
Yeah.

11
00:00:19,284 --> 00:00:23,004
So this is another, uh, listener
request from quite a while ago.

12
00:00:23,120 --> 00:00:25,040
Nikolay: Let's keep this episode short.

13
00:00:25,584 --> 00:00:26,514
It's about time.

14
00:00:26,772 --> 00:00:27,482
Michael: Oh, nice.

15
00:00:28,107 --> 00:00:30,167
Um, yeah, it's a great suggestion.

16
00:00:30,167 --> 00:00:30,807
Great request.

17
00:00:31,104 --> 00:00:32,984
it's one of our earliest requests actually.

18
00:00:33,289 --> 00:00:38,859
so it was specifically about time stamps in
general and time math, or arithmetic maybe.

19
00:00:39,549 --> 00:00:44,179
So yeah, it's quite broad, but also, I guess
we can take it in a few different directions.

20
00:00:44,776 --> 00:00:45,896
where did you wanna start?

21
00:00:46,612 --> 00:00:49,917
Nikolay: Let's start with idea
that, timestamp with time zone.

22
00:00:49,989 --> 00:00:54,075
Should be always preferred over
regular timestamp without time zone.

23
00:00:54,496 --> 00:00:56,106
What do you think about this idea?

24
00:00:56,393 --> 00:01:02,603
Michael: yeah, so I guess we're
jumping into the types here on how
we're storing these in the database.

25
00:01:02,943 --> 00:01:08,850
And there's a couple, that Postgres
lets you, use timestamp and timestamptz.

26
00:01:08,875 --> 00:01:12,115
So timestamp without time zone
and timestamp with time zone.

27
00:01:12,265 --> 00:01:14,035
I think it's quite a confusing name.

28
00:01:14,405 --> 00:01:19,645
and the reference I normally point
people to is the Postgres Wiki.

29
00:01:19,645 --> 00:01:29,525
They've got an excellent list that's quite
aggressively titled, called Don't Do This,
but one of the, they, it is, it's aggressively
titled, but it also includes information.

30
00:01:30,570 --> 00:01:32,970
you, when it might make sense,
you know, what are the exceptions?

31
00:01:32,970 --> 00:01:38,970
But one of the, one of the ones
that has very few exceptions is you
probably always want time stamp tea.

32
00:01:39,300 --> 00:01:40,290
Uh, when you're storing

33
00:01:40,310 --> 00:01:40,870
Nikolay: Right.

34
00:01:40,870 --> 00:01:49,666
And, uh, since it's couple of more letters,
, it's easier to write Justin Stamp and then
realize that maybe it was not the best choice.

35
00:01:50,217 --> 00:01:54,902
I would, uh, like to, uh, mention
that, Uh, usually it's my perception.

36
00:01:54,902 --> 00:02:04,475
Usually you think about timestamps as something
bigger than regular numbers, integers, especially
if it's with something else like timezone, right?

37
00:02:04,479 --> 00:02:18,834
if you think about storage, You maybe
think, okay, I maybe need to use regular
numbers and of course I don't want to bump
into the limit of four byte integer, so
I will choose eight byte integer, right?

38
00:02:18,834 --> 00:02:24,684
So in, in eight or begin, and then you realize
that regular timestamp is also eight bytes.

39
00:02:25,035 --> 00:02:29,235
And then you realize that, timestamp
with timezone is also eight bytes.

40
00:02:29,235 --> 00:02:30,795
They are all eight bytes.

41
00:02:30,925 --> 00:02:36,446
So the choice should not be based on
storage here in, in a big end timestamp.

42
00:02:36,446 --> 00:02:37,166
Or timestamp.

43
00:02:37,166 --> 00:02:39,566
Is that timestamp with timezone?

44
00:02:39,566 --> 00:02:42,526
If we timestamp, is that, is, is, is Pause system.

45
00:02:43,314 --> 00:02:51,184
because it's a standard way, SQL
standard way or data type is timestamp
with time zone for four words.

46
00:02:52,244 --> 00:03:04,092
so I personally try to encourage all engineers
usually to choose by default choose, times
stamp to that explaining that there is no
storage benefits of regular times stamp, right.

47
00:03:04,596 --> 00:03:05,446
Michael: Yeah, exactly.

48
00:03:05,446 --> 00:03:08,366
And there's loads of benefits
of the timestamp T z.

49
00:03:08,416 --> 00:03:14,086
So for example, if you are doing, I guess we'll
go, go onto arithmetic later, but if we are.

50
00:03:14,266 --> 00:03:19,126
Calculating the difference between a timestamp,
at least in the uk I think a lot in the us.

51
00:03:19,126 --> 00:03:21,526
A lot of other countries observe daylight savings.

52
00:03:21,776 --> 00:03:25,086
If we're doing arithmetic
between two timestamps on what?

53
00:03:25,086 --> 00:03:27,846
And we're crossing a boundary,
like a daylight savings boundary.

54
00:03:28,692 --> 00:03:35,682
We won't get the right answer if we or
we are more likely to have issues if we
are using time stamp without time zone.

55
00:03:36,223 --> 00:03:49,303
so Postgres can handle all of that stuff,
that complicated stuff, the reasons people
talk about times being difficult, that that
can all be handled by Postgres if we're
telling it that this is, um, and I guess
should we, should we mention the name?

56
00:03:49,303 --> 00:03:51,503
It's not the, with timezone, I think.

57
00:03:52,603 --> 00:04:03,740
people off and is confusing, from the Wiki
I stole, their description that times stamp
is a bit like a photograph of a calendar
and a clock, but you have no context.

58
00:04:04,320 --> 00:04:04,740
and.

59
00:04:05,425 --> 00:04:06,905
Timestamp with timezone.

60
00:04:06,955 --> 00:04:18,185
It knows, or it's firstly it can convert
that, or it does convert that to utc and
it, it has the context of a point in time
so it, it knows when that is, is rather than

61
00:04:19,325 --> 00:04:19,885
Nikolay: Right.

62
00:04:19,885 --> 00:04:26,536
And, uh, the resulting value will depend on the
context which you can change, in any session.

63
00:04:26,586 --> 00:04:33,456
So one session sees one value, final value,
another session sees another value just because
they are sitting in different time zones.

64
00:04:33,456 --> 00:04:36,591
So that's why it's more, flexible
and convenient and so on.

65
00:04:37,162 --> 00:04:37,652
Michael: Yeah.

66
00:04:37,982 --> 00:04:41,111
So you've already, you've already
touched on the storage, efficiency.

67
00:04:41,598 --> 00:04:45,146
are there any other, Kind of performance
related things that you wanted to

68
00:04:45,216 --> 00:04:45,506
Nikolay: I

69
00:04:45,626 --> 00:04:45,866
Michael: with

70
00:04:46,136 --> 00:04:46,826
Nikolay: good question.

71
00:04:46,856 --> 00:04:47,426
I don't know.

72
00:04:47,691 --> 00:04:51,581
I never thought about like
timestamp to that less performance.

73
00:04:51,891 --> 00:04:53,901
I never try to measure it.

74
00:04:53,901 --> 00:04:56,861
It's a good question, but I think I
doubt there is big difference here.

75
00:04:57,424 --> 00:05:00,984
Michael: Based on my understanding of how
they're stored, I don't see how they could be.

76
00:05:01,571 --> 00:05:03,423
but yeah, it let us know if you know

77
00:05:03,673 --> 00:05:04,163
Nikolay: Yeah.

78
00:05:04,163 --> 00:05:10,443
You need, uh, of course you need to, to like
to do some etic uh, evacuation in run time.

79
00:05:10,449 --> 00:05:12,039
if you have with time zone.

80
00:05:12,564 --> 00:05:14,064
But I don't know.

81
00:05:14,064 --> 00:05:19,504
I think it's, uh, very small, of
course, an interesting exercise to
compare and, and see some differences.

82
00:05:19,504 --> 00:05:23,584
Maybe there are already these
exercises in some blog post, I dunno.

83
00:05:23,924 --> 00:05:31,884
But, uh, I wanted to mention a
dangerous part of SQL Standard, which
is, a operator called at Time Zone.

84
00:05:32,154 --> 00:05:33,244
It's very dangerous.

85
00:05:33,244 --> 00:05:36,680
It's the ugliest parts, the ugliest corners of.

86
00:05:36,879 --> 00:05:39,273
This huge building called, SQL Standard.

87
00:05:40,243 --> 00:05:44,954
So it can be very tricky if you don't
know that is d It's like mouths, right?

88
00:05:45,204 --> 00:05:48,274
So mouths are dangerous and
at time zone is dangerous.

89
00:05:48,604 --> 00:05:58,821
If you check, how it works and, uh, what
is resulting data type for which value
For timestamp, it'll produce timestamp at
time zone for times, stamp at time zone.

90
00:05:58,821 --> 00:05:59,581
It'll produce times.

91
00:06:00,298 --> 00:06:07,165
. If you take timestamp without time
zone and say at time zone it, you will
have timestamp at time, time zone.

92
00:06:07,575 --> 00:06:12,833
If you take a timestamp at some
time zone and at time zone, it'll
produce timestamp without time zone.

93
00:06:12,942 --> 00:06:15,122
And this is, this is from standard as I remember.

94
00:06:15,122 --> 00:06:20,627
So if you go there, just
open the reference , right?

95
00:06:20,927 --> 00:06:24,147
And, uh, do any movements with reference.

96
00:06:24,957 --> 00:06:26,547
No movements without it, right?

97
00:06:27,137 --> 00:06:29,369
just, just a warning, for.

98
00:06:30,134 --> 00:06:37,014
So, performance, I, I, I didn't think we will
discuss a lot of performance, uh, questions.

99
00:06:37,014 --> 00:06:46,724
Maybe just the idea that, of course,
it's perfectly indexible, both timestamp
and timestamped, is that, If you
have such columns, you can the exam.

100
00:06:47,064 --> 00:06:57,924
But I had several times I had a case in my
engineering, pr engineering practice when
I needed to have, um, something like age
by, by the way, there is such function age.

101
00:06:58,639 --> 00:07:04,125
, which, uh, you can give either two time stamps
for to it, and it'll just calculate interval.

102
00:07:04,125 --> 00:07:07,525
Oh, there is interval type data type.

103
00:07:07,525 --> 00:07:11,605
It's it's whole new world time
ranges, intervals and so on.

104
00:07:11,828 --> 00:07:16,055
so you can give, two points in time
and it'll, it'll calculate difference.

105
00:07:16,055 --> 00:07:20,375
Or you can just give one timestamp and
it will compare it to the current time.

106
00:07:21,055 --> 00:07:21,615
Right.

107
00:07:21,615 --> 00:07:24,615
And, uh, it'll be just a regular like age.

108
00:07:24,615 --> 00:07:26,375
So you say like, my birthday is.

109
00:07:26,970 --> 00:07:30,030
and it gives you age, uh, interval.

110
00:07:30,370 --> 00:07:36,970
But, I wanted to store it this interval and
then I wanted to index it several times.

111
00:07:37,070 --> 00:07:46,089
And, uh, always it was a question how
to do it because you cannot have an
index, uh, on something which involves,
time zone because it's volatile, right?

112
00:07:46,089 --> 00:07:48,649
It depends on, on the context, on your session.

113
00:07:48,699 --> 00:07:53,369
If you, if you change time zone
in session saying, say time zone.

114
00:07:54,354 --> 00:07:57,547
, I don't know, like, Europe,
Berlin or something like this.

115
00:07:57,547 --> 00:08:00,667
Uh, in this case you will,
or new time zone, by the way.

116
00:08:00,757 --> 00:08:03,267
Uh, Europe ki there is new time zone.

117
00:08:03,267 --> 00:08:03,707
Yeah.

118
00:08:03,707 --> 00:08:06,987
But it's not a benefit from
PO's coming from POGS itself.

119
00:08:06,987 --> 00:08:08,107
It's it's lower level.

120
00:08:08,337 --> 00:08:18,708
It's, sorry, I, I don't know libraries, but
it's coming from under underlying software,  So
if you, set it in session, your value will be
different and probably interval will be different.

121
00:08:18,708 --> 00:08:20,668
So you cannot index it easily.

122
00:08:20,798 --> 00:08:29,955
If, if you, for example, you want to index
expression, You store time zone, but you want
to index to support fast lookups based on age.

123
00:08:30,518 --> 00:08:35,469
And, uh, my work around was, uh, I
switched  different approach to store time.

124
00:08:35,499 --> 00:08:37,709
I, based on Unix times stamps and so on.

125
00:08:37,709 --> 00:08:41,294
I just, said, okay, but Unix
times stamps are limited on.

126
00:08:41,965 --> 00:08:54,555
On the beginning, uh, 1970, maybe January 1st, I
don't remember, but some point in time in 1970,
and then it's just a number of seconds passed.

127
00:08:54,785 --> 00:08:57,235
Unix timestamp is number of
seconds passed since then.

128
00:08:57,445 --> 00:09:03,635
By the way, I wish in Povi it would
be easier to convert from timestamp
to Unix timestamp back and forth.

129
00:09:03,785 --> 00:09:10,845
It's, it's quite, Quite an expression,
you need always to Google it and stack or
flow or something will help you, right?

130
00:09:11,249 --> 00:09:13,419
it's memorize it, like memorize it.

131
00:09:13,419 --> 00:09:19,259
It takes, uh, like five years of practicing
constantly to, to write it blindly.

132
00:09:19,749 --> 00:09:26,383
So, hu timestamps are limited on
one and they are technically limited
cause of capacity ofs on another end.

133
00:09:27,770 --> 00:09:43,593
my idea was, okay, I say our software will
exist only till 2050, so we have some line and
we can measure, we can see progress, and we can
have ranges, and this is very stable already.

134
00:09:43,963 --> 00:09:48,713
Of course, I lose time zone benefits and flex.

135
00:09:48,908 --> 00:09:49,668
Convenience.

136
00:09:50,368 --> 00:09:52,868
But, uh, I, I'm on perfect.

137
00:09:53,198 --> 00:09:57,655
Um, range of numbers we live,
this is our universe, right?

138
00:09:57,795 --> 00:10:00,375
And we deal with only with points on this range.

139
00:10:00,375 --> 00:10:04,495
And it's when you think you do there,
you can index any expression, anything.

140
00:10:04,865 --> 00:10:07,135
So this is what my approach as working around.

141
00:10:07,135 --> 00:10:14,495
Maybe there are other approaches to, to
deal with age and, uh, and, uh, still
build an index on some expression.

142
00:10:15,350 --> 00:10:20,830
Involving age, but usually it's a trick and
people bump into it and think, oh, what to do.

143
00:10:21,680 --> 00:10:23,710
So, uh, what else?

144
00:10:24,140 --> 00:10:27,390
What else do you have in
mind related to timestamps?

145
00:10:28,155 --> 00:10:32,025
Michael: So well quickly while we're like, cause
I don't think we'll come back to performance.

146
00:10:32,025 --> 00:10:34,505
I guess this is a good time to mention that.

147
00:10:34,505 --> 00:10:39,425
Obviously Bree indexes work nicely for,
uh, things that are sorted like this.

148
00:10:39,425 --> 00:10:44,905
But we've also had a, we've done a, an
episode on BRI indexes, so that's a,
timestamps are probably the most common.

149
00:10:45,241 --> 00:10:47,752
Uh, use of print indexes that I've seen.

150
00:10:47,752 --> 00:10:52,232
I, I think that's what they, they're
mostly used for in the world, so, yeah.

151
00:10:52,232 --> 00:10:54,552
Refer back to that maybe if that's of interest.

152
00:10:54,935 --> 00:10:56,315
you mentioned intervals.

153
00:10:56,505 --> 00:10:57,675
Should we cover those

154
00:10:58,120 --> 00:11:01,000
Nikolay: yeah, but, but one,
one more comment about indexes.

155
00:11:01,050 --> 00:11:05,880
Uh, there is a very popular
type of index called gin, right?

156
00:11:06,300 --> 00:11:12,760
And there is big problem, uh, that usually you
have a full text church, and you want to order by.

157
00:11:14,098 --> 00:11:15,568
uh, number all timestamp.

158
00:11:16,068 --> 00:11:18,368
And to solve this, there is index called ram.

159
00:11:18,895 --> 00:11:20,935
it's not present, I think on rds.

160
00:11:21,045 --> 00:11:26,415
I, I remember I personally asked
them to consider it, but the problem
with it, usually index size is huge.

161
00:11:27,165 --> 00:11:38,364
I know super base by the way, includes it, ram,
I, I wonder what is, what's experience there
because my experience was, I considered it several
times, and, uh, every time I considered it around.

162
00:11:39,871 --> 00:11:40,361
Huge.

163
00:11:40,531 --> 00:11:46,961
So idea of Ramm is you have Gene, plus you have
knowledge about some timestamp or integer eight.

164
00:11:47,351 --> 00:11:56,671
They originally developed only
timestamp,  support, but I asked also
to at INTE eight, they they, it was many
years ago, so now it has both options.

165
00:11:57,161 --> 00:12:00,151
So Ram is like gene extended with some times.

166
00:12:01,041 --> 00:12:08,087
So now you can have, in single index
scan, you can have almost single index
scan, but you can have, uh, both.

167
00:12:08,452 --> 00:12:16,052
Operations covered, full text, search and
order by, uh, sometimes stamp, which is
perfect for, like search in some comments.

168
00:12:16,052 --> 00:12:19,979
For example, you always usually want
to  fresh comments coming first, right?

169
00:12:20,469 --> 00:12:22,157
So it's very great idea.

170
00:12:22,510 --> 00:12:32,987
I wish post this, uh, like it would be
more include to core contribution models,
maybe anti contribute models, but it.

171
00:12:33,237 --> 00:12:37,497
And it has issues with size and,
and affecting performance and so on.

172
00:12:37,497 --> 00:12:43,070
But idea is great and like, I mean, the goal
is great, so also it becomes timestamps.

173
00:12:43,070 --> 00:12:49,150
So it's interesting that it injects timestamp to
gin and it becomes around very strange, right?

174
00:12:49,812 --> 00:12:54,525
Michael: At the risk of going o off topic,
I know gin is not named after the alcohol.

175
00:12:54,695 --> 00:12:57,445
Uh, and one I think is a play on that, that it is.

176
00:12:57,445 --> 00:13:01,405
But there's also, I heard, I only read
briefly about it, but, uh, an index type

177
00:13:02,294 --> 00:13:02,730
by the

178
00:13:03,210 --> 00:13:07,890
Nikolay: was only like, uh, in development
and never reached some like production stage.

179
00:13:07,890 --> 00:13:09,170
But Ram is quiet like.

180
00:13:09,583 --> 00:13:19,057
quite finished and you can consider it,
but like I just, I'm just saying that
my personal experience was not perfect,
but doesn't mean that there is no need.

181
00:13:19,057 --> 00:13:20,017
Need is is huge.

182
00:13:20,017 --> 00:13:34,993
Like usually we end up, having two types of plans,
like either posts either chooses to use full check
search and then order by memory, or it chooses
to walk along primary key or index on times.

183
00:13:35,758 --> 00:13:43,022
Tease that and then, uh, apply filtering,
in memory for full tech search, which
both are not perfect paths, right?

184
00:13:43,022 --> 00:13:50,944
So like the problem is obvious and it's
not fully solved, but with Ram solved
again, like maybe you should try it.

185
00:13:51,354 --> 00:14:03,955
So,  we should also mention that there is,
uh, there is date and there is time, And
it, when some engineer creates something,
some column call, calling this column time,
but it's timestamp, it breaks my mind.

186
00:14:03,955 --> 00:14:10,155
Always  because like, okay, this,
this, uh, frame framework of.

187
00:14:10,840 --> 00:14:16,443
In pogs, I like adopted it already and
I cannot, see time when it's timestamp.

188
00:14:16,443 --> 00:14:17,203
No, it's not time.

189
00:14:17,203 --> 00:14:20,363
It also includes date, like
at least let's say daytime.

190
00:14:20,453 --> 00:14:22,643
By the way, maybe standard also has daytime.

191
00:14:22,643 --> 00:14:28,643
I remember other database system have daytime
as data type, so there is date, there is time.

192
00:14:28,643 --> 00:14:31,443
When we combine them, it's
the is time stamp, right?

193
00:14:31,913 --> 00:14:33,083
Like it's full thing.

194
00:14:33,253 --> 00:14:33,743
Michael: Yeah.

195
00:14:33,843 --> 00:14:36,483
Nikolay: we can have also a column of type.

196
00:14:36,988 --> 00:14:40,108
Right Or of type time.

197
00:14:41,608 --> 00:14:42,228
Why not?

198
00:14:42,793 --> 00:14:47,323
Michael: Yeah, but I think date gets date's
difficult because of the boundaries, right?

199
00:14:47,323 --> 00:14:51,083
Like what does like midnight and time like?

200
00:14:51,083 --> 00:14:51,803
I think it's tricky.

201
00:14:51,873 --> 00:14:56,563
I think personally I would stick to
timestamps in general, but what I, maybe

202
00:14:56,628 --> 00:14:57,118
Nikolay: Well,

203
00:14:57,143 --> 00:14:58,043
Michael: the point of

204
00:14:58,128 --> 00:15:04,078
Nikolay: uh, too better in terms, two times
better in terms of performance because, uh,
in terms of storage, because of it's four.

205
00:15:04,833 --> 00:15:05,393
Right.

206
00:15:05,393 --> 00:15:18,113
So you can save some bites if you want, but you
should remember of course about alignment page if
you place date, and then enter eight, no benefits
because uh, four bites will be filled by zeros.

207
00:15:18,323 --> 00:15:20,873
So you should combine with other four bite column.

208
00:15:21,440 --> 00:15:37,864
I don't use it often, honestly, so I agree
with you here, but also there is time and
sometimes we Technically we can use timestamp
as time saying like, you can use timestamp
instead of date, taking some time of day.

209
00:15:37,864 --> 00:15:45,744
But also you will start thinking, okay, if
it's time with time zone, it's another date
can be different depending on time zone.

210
00:15:45,744 --> 00:15:47,344
So it's, it's, it's becoming tricky.

211
00:15:47,344 --> 00:15:50,624
So probably this is where
you probably will try to.

212
00:15:51,249 --> 00:15:54,489
Timestamp without time zone, just to be concrete.

213
00:15:54,489 --> 00:15:54,809
Okay.

214
00:15:54,809 --> 00:15:57,129
It's utc, I can see that as utc.

215
00:15:57,599 --> 00:15:58,649
This is our date.

216
00:15:58,649 --> 00:15:59,129
That's it.

217
00:15:59,769 --> 00:16:01,769
Again, depends, right?

218
00:16:01,769 --> 00:16:08,972
But if you think about time, technically you
can use timestamp, just like choosing some date.

219
00:16:09,682 --> 00:16:13,372
Always like January 1st something, right?

220
00:16:13,442 --> 00:16:13,972
I don't know.

221
00:16:14,312 --> 00:16:16,612
But it's not, uh, it took ugly, right?

222
00:16:16,622 --> 00:16:18,572
So time sometimes is.

223
00:16:19,444 --> 00:16:20,374
Michael: Yeah, so there's.

224
00:16:20,679 --> 00:16:25,599
There's time with time zone as well and time, and
there's really good note in the documentation.

225
00:16:25,599 --> 00:16:33,679
And on that wiki page I mentioned earlier to
say basically I, I think there's no exception,
they couldn't think of a single exception
where you would want time with time zone.

226
00:16:33,969 --> 00:16:39,879
Uh, they go into some details as to why
that's, um, why that makes sense, but
time on its own makes sense in terms of.

227
00:16:40,904 --> 00:16:51,504
a photo of a clock or like a, if, if
you're, if you're a calendar application
or a to-do list application and you want
the meet, like let's say you want to
go walk your dogs at 9:00 AM every day.

228
00:16:51,684 --> 00:16:57,144
You want that to be 9:00 AM summer,
and maybe you want that to be 9:00 AM
in winter, but it, it's like, it's,

229
00:16:57,174 --> 00:16:57,664
Nikolay: Yeah.

230
00:16:57,824 --> 00:17:02,744
Michael: the time of day, not,
uh, not a time in, in with a date.

231
00:17:02,764 --> 00:17:05,824
And so I think I, I can see more uses for that.

232
00:17:05,877 --> 00:17:08,797
, and I probably would store it
as time because it's not, uh,

233
00:17:08,842 --> 00:17:09,572
Nikolay: Yeah, maybe.

234
00:17:09,572 --> 00:17:10,012
Yeah.

235
00:17:10,442 --> 00:17:19,755
Also was mentioning they all, both timestamp,
both of timestamps, and both of times, uh, they
have precision, um, how to say modifier, right?

236
00:17:19,755 --> 00:17:23,435
So you can, in parenthesis you
can say, I, I don't want a micro.

237
00:17:24,530 --> 00:17:26,460
Just put a zero there and that's it.

238
00:17:26,460 --> 00:17:27,260
Also convenient.

239
00:17:27,260 --> 00:17:35,860
I use it sometimes because like it
becomes easier to have predictable output
without just now converted to timestamp.

240
00:17:35,860 --> 00:17:41,834
And zero in parenthesis gives,
gives you, timestamp with seconds,
but without my milliseconds.

241
00:17:42,799 --> 00:17:48,859
Michael: The Wiki advises against it, I think,
because it rounds rather than truncates.

242
00:17:48,924 --> 00:17:49,414
Nikolay: Okay.

243
00:17:49,499 --> 00:17:51,939
Michael: it, it's possible to round to a time

244
00:17:52,799 --> 00:17:53,289
Nikolay: Okay.

245
00:17:53,499 --> 00:17:59,699
Michael: which might not be what you want,
but yeah, it's interesting and they, I think
they recommend truncate instead date trunk as

246
00:17:59,719 --> 00:18:00,209
Nikolay: Okay.

247
00:18:00,709 --> 00:18:01,459
Michael: or time, I

248
00:18:01,474 --> 00:18:06,484
Nikolay: Date trunk will give you Yeah, it'll
give you, you can give, you can take seconds.

249
00:18:06,594 --> 00:18:07,084
Yeah.

250
00:18:07,084 --> 00:18:07,444
Right.

251
00:18:07,574 --> 00:18:08,124
So, yeah.

252
00:18:08,124 --> 00:18:10,564
And everything else will be
filled like with zeros, right?

253
00:18:10,564 --> 00:18:10,844
Right.

254
00:18:11,334 --> 00:18:25,074
Or it just can be, if you want, for example,
to take on the date from like my small
trick, usually if I have timestamps, I,
I want only date, for example, to then
aggregate and have some report I use left.

255
00:18:27,169 --> 00:18:29,859
left timestamp value, comma 10.

256
00:18:30,349 --> 00:18:32,899
It gives you date usually, right?

257
00:18:32,969 --> 00:18:39,539
It's, it's stable because we have four digits for
year and divider and like, and so on and so on.

258
00:18:39,749 --> 00:18:44,859
So left is much faster, but of course
they Trunk Day for example, also works.

259
00:18:46,024 --> 00:18:46,854
. So it depends.

260
00:18:47,384 --> 00:18:48,734
It, it depends.

261
00:18:48,794 --> 00:18:51,174
But left function, I love it.

262
00:18:51,174 --> 00:18:57,644
It's, of course it's to work with text, but
it's, it's much shorter, sometimes, so, yeah.

263
00:18:57,683 --> 00:19:01,443
but interesting that time is a
regular time without time zone.

264
00:19:01,443 --> 00:19:04,363
It's also by, but with time zone, it's 12 bites.

265
00:19:05,229 --> 00:19:06,799
Michael: Yeah, I didn't understand that.

266
00:19:06,799 --> 00:19:07,879
How could it be more?

267
00:19:08,904 --> 00:19:09,064
Nikolay: Surprise.

268
00:19:09,174 --> 00:19:10,704
I don't know why, but surprise.

269
00:19:10,704 --> 00:19:15,224
So that's why I usually try to stick
with time stepss as, as long as I can.

270
00:19:15,224 --> 00:19:20,825
And only then I, I go to less, uh,
for me to less popular, data types.

271
00:19:20,885 --> 00:19:23,665
But interval is, interval is even worse.

272
00:19:23,665 --> 00:19:26,846
It's uh, 16 bytes . So

273
00:19:27,096 --> 00:19:28,236
Michael: But I, yeah.

274
00:19:28,236 --> 00:19:29,436
So why, why is that?

275
00:19:29,436 --> 00:19:31,956
Is it because it's storing two timestamps?

276
00:19:34,126 --> 00:19:34,616
Okay.

277
00:19:34,676 --> 00:19:35,476
Nikolay: our listeners.

278
00:19:35,476 --> 00:19:39,716
Leave a comment if you're watching
a YouTube for, for leave a comment.

279
00:19:39,716 --> 00:19:42,956
Please explain why it's interesting question.

280
00:19:42,956 --> 00:19:49,716
Well, interesting but not like if, again,
if you store it or your care, but you
don't not have good, a lot of good options.

281
00:19:50,196 --> 00:19:51,796
Interval is, Very good.

282
00:19:51,846 --> 00:19:56,176
Um, it's very powerful and, uh,
very flexible, very convenient.

283
00:19:56,486 --> 00:19:59,868
I use it a lot, including for dba practice.

284
00:20:00,168 --> 00:20:05,868
For example, you want to understand how
long your transaction or statement lasts.

285
00:20:06,528 --> 00:20:18,318
Or when last state change happened, and
you want something like h you will have,
you can, you can use minus separator
and you will have interval as output.

286
00:20:18,488 --> 00:20:19,078
By the way.

287
00:20:19,278 --> 00:20:19,758
Question to you.

288
00:20:20,461 --> 00:20:27,079
what would you use if you select
from activity, uh, to see when
transaction, like transaction age for.

289
00:20:29,063 --> 00:20:30,083
How would you use it?

290
00:20:30,213 --> 00:20:32,603
So there is exact, exact start,

291
00:20:33,473 --> 00:20:37,176
x a c T under score start, column.

292
00:20:37,236 --> 00:20:38,976
And there is of course current timestamp.

293
00:20:39,826 --> 00:20:47,216
So most people, not most people, many people
use now function minus, uh, exact start.

294
00:20:47,216 --> 00:20:48,656
But uh, it's quite.

295
00:20:49,486 --> 00:20:56,466
Now function is very good in terms of
performance because it gets timestamped only
once in the beginning of your transaction.

296
00:20:56,516 --> 00:20:59,746
So your, your, your select
is also a transaction, right?

297
00:20:59,996 --> 00:21:04,666
So now is calculated only in the beginning,
but activity, it's not a normal table.

298
00:21:04,956 --> 00:21:07,546
Uh, and, uh, values are constantly shifting.

299
00:21:07,546 --> 00:21:13,306
And we, if it's quite big while we
read it, we, we, it's not stable.

300
00:21:13,306 --> 00:21:15,266
It's not, we don't have snapshot there.

301
00:21:15,994 --> 00:21:19,174
You will have like shifted values.

302
00:21:19,174 --> 00:21:24,094
You probably will end up with, uh,
negative age negative intervals.

303
00:21:24,624 --> 00:21:34,686
So what you should use is less performant
because less performed function because it
will calculate, current timestamp for each row.

304
00:21:34,796 --> 00:21:35,886
It's called clock time.

305
00:21:36,841 --> 00:21:42,578
So clock, times stamp minus exact
uh, start and um, transaction start.

306
00:21:42,578 --> 00:21:42,898
Right?

307
00:21:42,898 --> 00:21:49,778
So, and in this case you won't have negative
values, which is good way, . So yeah, small trick.

308
00:21:49,918 --> 00:21:58,378
And sometimes it's also worth remembering that
now it's like, it's like, boom, and you have
it for all, but maybe it's not what you want.

309
00:21:59,168 --> 00:21:59,658
Okay.

310
00:21:59,658 --> 00:21:59,938
Good.

311
00:22:00,108 --> 00:22:01,818
Uh, anything else we wanted to.

312
00:22:02,519 --> 00:22:15,849
Michael: I only had one more, which is
only tangentially related, but I heard some
good advice a while back that if you are
adding a column that's, uh, boo, that you're
probably are planning to use a bullion.

313
00:22:15,924 --> 00:22:17,784
For consider using

314
00:22:18,004 --> 00:22:18,424
Nikolay: Hmm.

315
00:22:18,554 --> 00:22:26,664
Michael: So I think the, the, the good cases
that I've heard, uh, for this are things like act

316
00:22:26,894 --> 00:22:27,384
Nikolay: Yeah.

317
00:22:27,384 --> 00:22:27,824
Yeah.

318
00:22:28,064 --> 00:22:34,723
Michael: it's basically, sometimes you don't,
you don't necessarily need it for the first set
of features, but it can be incredibly useful

319
00:22:34,743 --> 00:22:36,553
Nikolay: Yeah, I also use it, this approach.

320
00:22:36,553 --> 00:22:36,993
Yeah.

321
00:22:36,993 --> 00:22:37,313
Yeah.

322
00:22:37,313 --> 00:22:43,553
But you should remember storage, uh,
overhead, of course, because bullion, of
course, it's not one bit, it's one bite.

323
00:22:43,743 --> 00:22:47,073
It's already, you already are eight.

324
00:22:47,073 --> 00:22:52,793
Time worse than you could be . Oh, you can
be like most, uh, storage, uh, efficient.

325
00:22:52,909 --> 00:22:58,914
uh, approach would be like, uh, spend
one bite for eight, check boxes, right?

326
00:22:58,914 --> 00:23:01,911
And then, work with bit operations.

327
00:23:02,251 --> 00:23:03,391
But, uh, okay.

328
00:23:03,391 --> 00:23:04,791
If you have bull column, it's one bite.

329
00:23:05,851 --> 00:23:11,361
Of course if you, if you pack, if you have
eight of them, they will occupy eight bites.

330
00:23:11,361 --> 00:23:19,481
It's good if you have boo and then
timestamp or integer eight, you
will have seven bites lost anyway.

331
00:23:19,901 --> 00:23:28,481
But, uh, in this case, of course, uh, switching
to timestamp won't, uh, be, be, won't make
any difference in terms of storage overhead.

332
00:23:28,481 --> 00:23:28,721
Right?

333
00:23:29,114 --> 00:23:30,124
Michael: Yeah, but it was

334
00:23:31,484 --> 00:23:32,604
you're right in terms of storage.

335
00:23:33,719 --> 00:23:44,282
more of like a, I thought, I think it's like
quite a good thinking exercise, when you're
designing a new feature, just to think, would
there, could there be benefits down the line of
storing this in a different, different format?

336
00:23:44,732 --> 00:23:45,082
Um,

337
00:23:45,312 --> 00:23:45,802
Nikolay: When,

338
00:23:46,012 --> 00:23:46,762
Michael: it makes, it's made

339
00:23:46,792 --> 00:23:54,882
Nikolay: when you, when you are designing,
think about if you need, uh, to remember
when check boxes are, you are pressed, right?

340
00:23:54,882 --> 00:23:57,242
Clicked or checked, right.

341
00:23:57,372 --> 00:23:59,882
So yeah, it's, it's depends on the application of.

342
00:24:00,802 --> 00:24:04,192
. Oh, I wanted to mention, uh, these old balls.

343
00:24:04,192 --> 00:24:04,992
You know, old balls.

344
00:24:05,382 --> 00:24:06,832
What, what is old balls?

345
00:24:07,217 --> 00:24:07,887
Michael: Oh yes.

346
00:24:07,887 --> 00:24:08,487
So there's,

347
00:24:08,782 --> 00:24:09,272
Nikolay: Yeah.

348
00:24:09,567 --> 00:24:10,167
Michael: time, the

349
00:24:10,172 --> 00:24:10,752
Nikolay: All right.

350
00:24:10,862 --> 00:24:12,752
0, 0, 0 time utc.

351
00:24:12,752 --> 00:24:13,192
Right.

352
00:24:14,122 --> 00:24:20,112
So, yeah, it's a, it's kind of
like interesting joke maybe, right?

353
00:24:20,122 --> 00:24:21,312
So, but,

354
00:24:21,567 --> 00:24:22,787
Michael: but it's committed, right?

355
00:24:22,912 --> 00:24:23,462
Nikolay: It works.

356
00:24:23,462 --> 00:24:24,542
Of course, you can say,

357
00:24:24,897 --> 00:24:25,387
Michael: Yeah.

358
00:24:26,112 --> 00:24:26,902
Nikolay: oh, by the way.

359
00:24:27,232 --> 00:24:44,118
Uh, how to, how to get, um, interval,
how to get, uh, current time minus one
day, like now minus, I write one day
in, uh, in, um, comas, not in comas in.

360
00:24:44,793 --> 00:24:48,673
Codes, single codes, and then
converted to interval and that's it.

361
00:24:49,003 --> 00:24:50,593
Or say minus interval.

362
00:24:50,593 --> 00:24:56,009
And then, uh, string, literal, one day it
works perfectly or one hour one, like it works.

363
00:24:56,629 --> 00:25:01,319
But, uh, also there is, uh, so wall ball
sits like also in single, uh, quotes.

364
00:25:01,739 --> 00:25:03,119
You converted to time and.

365
00:25:03,764 --> 00:25:12,054
this time, but, uh, like it's, it's kind of a
joke, but, uh, what is more useful is infinity,
minus infinity and, uh, plus infinity, right?

366
00:25:12,684 --> 00:25:14,054
This, uh, is useful.

367
00:25:14,114 --> 00:25:25,534
You can have a timestamp infinity, and, uh, I can
imagine very good use cases when it, it's like
very, very helpful to develop good application.

368
00:25:26,298 --> 00:25:27,498
Michael: that's a really good point.

369
00:25:27,628 --> 00:25:28,978
So I, um, there's.

370
00:25:29,893 --> 00:25:39,453
feel like I've mentioned it too many times
already, but that Wiki article I think also
includes not using or between probably not being
what you want when it comes to, timestamps.

371
00:25:39,453 --> 00:25:42,413
And you probably want less than
one and greater than another.

372
00:25:42,553 --> 00:25:45,213
And that's, I, that's where
I've seen infinity used most.

373
00:25:45,343 --> 00:25:54,586
So you might want to be, selecting all
times from in the future to positive
infinity or from sometime in the past.

374
00:25:56,271 --> 00:25:57,931
But less than, uh, minus

375
00:25:57,991 --> 00:25:58,531
Nikolay: All right.

376
00:25:58,731 --> 00:25:59,291
Right.

377
00:25:59,971 --> 00:26:00,531
Agree.

378
00:26:01,155 --> 00:26:01,645
Good.

379
00:26:01,895 --> 00:26:03,685
So what else, or that's it.

380
00:26:04,175 --> 00:26:05,685
We wanted to, to keep it short.

381
00:26:06,235 --> 00:26:06,515
Michael: all I

382
00:26:06,555 --> 00:26:07,045
Nikolay: Yeah.

383
00:26:07,875 --> 00:26:09,943
Then let's wrap up and uh, call today.

384
00:26:09,943 --> 00:26:10,063
Right.

385
00:26:10,805 --> 00:26:11,295
Michael: Yeah.

386
00:26:11,535 --> 00:26:11,615
Fabulous.

387
00:26:11,615 --> 00:26:12,375
Thanks so much.

388
00:26:12,375 --> 00:26:13,575
Thanks everyone for listening.

389
00:26:13,575 --> 00:26:15,855
Keep the suggestions coming and see you next

390
00:26:15,905 --> 00:26:16,585
Nikolay: Thank you.

391
00:26:16,585 --> 00:26:16,985
Bye bye.