Michael: Hello and welcome to Postgres fm, a weekly show about all things PostgreSQL.
I'm Michael, founder of PG Mustard.
This is my cohost Nicolay, founder of Postgres ai.
Hey, Nicolay, what are we talking about
Nikolay: Hi Michael.
Let's talk about something priceless.
Time, time stamps, time zones, and so on.
Michael: I like the priceless reference.
That's great.
Yeah.
So this is another, uh, listener request from quite a while ago.
Nikolay: Let's keep this episode short.
It's about time.
Michael: Oh, nice.
Um, yeah, it's a great suggestion.
Great request.
it's one of our earliest requests actually.
so it was specifically about time stamps in general and time math, or arithmetic maybe.
So yeah, it's quite broad, but also, I guess we can take it in a few different directions.
where did you wanna start?
Nikolay: Let's start with idea that, timestamp with time zone.
Should be always preferred over regular timestamp without time zone.
What do you think about this idea?
Michael: yeah, so I guess we're jumping into the types here on how we're storing these in the database.
And there's a couple, that Postgres lets you, use timestamp and timestamptz.
So timestamp without time zone and timestamp with time zone.
I think it's quite a confusing name.
and the reference I normally point people to is the Postgres Wiki.
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.
you, when it might make sense, you know, what are the exceptions?
But one of the, one of the ones that has very few exceptions is you probably always want time stamp tea.
Uh, when you're storing
Nikolay: Right.
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.
I would, uh, like to, uh, mention that, Uh, usually it's my perception.
Usually you think about timestamps as something bigger than regular numbers, integers, especially if it's with something else like timezone, right?
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?
So in, in eight or begin, and then you realize that regular timestamp is also eight bytes.
And then you realize that, timestamp with timezone is also eight bytes.
They are all eight bytes.
So the choice should not be based on storage here in, in a big end timestamp.
Or timestamp.
Is that timestamp with timezone?
If we timestamp, is that, is, is, is Pause system.
because it's a standard way, SQL standard way or data type is timestamp with time zone for four words.
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.
Michael: Yeah, exactly.
And there's loads of benefits of the timestamp T z.
So for example, if you are doing, I guess we'll go, go onto arithmetic later, but if we are.
Calculating the difference between a timestamp, at least in the uk I think a lot in the us.
A lot of other countries observe daylight savings.
If we're doing arithmetic between two timestamps on what?
And we're crossing a boundary, like a daylight savings boundary.
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.
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?
It's not the, with timezone, I think.
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.
and.
Timestamp with timezone.
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
Nikolay: Right.
And, uh, the resulting value will depend on the context which you can change, in any session.
So one session sees one value, final value, another session sees another value just because they are sitting in different time zones.
So that's why it's more, flexible and convenient and so on.
Michael: Yeah.
So you've already, you've already touched on the storage, efficiency.
are there any other, Kind of performance related things that you wanted to
Nikolay: I
Michael: with
Nikolay: good question.
I don't know.
I never thought about like timestamp to that less performance.
I never try to measure it.
It's a good question, but I think I doubt there is big difference here.
Michael: Based on my understanding of how they're stored, I don't see how they could be.
but yeah, it let us know if you know
Nikolay: Yeah.
You need, uh, of course you need to, to like to do some etic uh, evacuation in run time.
if you have with time zone.
But I don't know.
I think it's, uh, very small, of course, an interesting exercise to compare and, and see some differences.
Maybe there are already these exercises in some blog post, I dunno.
But, uh, I wanted to mention a dangerous part of SQL Standard, which is, a operator called at Time Zone.
It's very dangerous.
It's the ugliest parts, the ugliest corners of.
This huge building called, SQL Standard.
So it can be very tricky if you don't know that is d It's like mouths, right?
So mouths are dangerous and at time zone is dangerous.
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.
It'll produce times.
. If you take timestamp without time zone and say at time zone it, you will have timestamp at time, time zone.
If you take a timestamp at some time zone and at time zone, it'll produce timestamp without time zone.
And this is, this is from standard as I remember.
So if you go there, just open the reference , right?
And, uh, do any movements with reference.
No movements without it, right?
just, just a warning, for.
So, performance, I, I, I didn't think we will discuss a lot of performance, uh, questions.
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.
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.
, which, uh, you can give either two time stamps for to it, and it'll just calculate interval.
Oh, there is interval type data type.
It's it's whole new world time ranges, intervals and so on.
so you can give, two points in time and it'll, it'll calculate difference.
Or you can just give one timestamp and it will compare it to the current time.
Right.
And, uh, it'll be just a regular like age.
So you say like, my birthday is.
and it gives you age, uh, interval.
But, I wanted to store it this interval and then I wanted to index it several times.
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?
It depends on, on the context, on your session.
If you, if you change time zone in session saying, say time zone.
, I don't know, like, Europe, Berlin or something like this.
Uh, in this case you will, or new time zone, by the way.
Uh, Europe ki there is new time zone.
Yeah.
But it's not a benefit from PO's coming from POGS itself.
It's it's lower level.
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.
So you cannot index it easily.
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.
And, uh, my work around was, uh, I switched different approach to store time.
I, based on Unix times stamps and so on.
I just, said, okay, but Unix times stamps are limited on.
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.
Unix timestamp is number of seconds passed since then.
By the way, I wish in Povi it would be easier to convert from timestamp to Unix timestamp back and forth.
It's, it's quite, Quite an expression, you need always to Google it and stack or flow or something will help you, right?
it's memorize it, like memorize it.
It takes, uh, like five years of practicing constantly to, to write it blindly.
So, hu timestamps are limited on one and they are technically limited cause of capacity ofs on another end.
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.
Of course, I lose time zone benefits and flex.
Convenience.
But, uh, I, I'm on perfect.
Um, range of numbers we live, this is our universe, right?
And we deal with only with points on this range.
And it's when you think you do there, you can index any expression, anything.
So this is what my approach as working around.
Maybe there are other approaches to, to deal with age and, uh, and, uh, still build an index on some expression.
Involving age, but usually it's a trick and people bump into it and think, oh, what to do.
So, uh, what else?
What else do you have in mind related to timestamps?
Michael: So well quickly while we're like, cause I don't think we'll come back to performance.
I guess this is a good time to mention that.
Obviously Bree indexes work nicely for, uh, things that are sorted like this.
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.
Uh, use of print indexes that I've seen.
I, I think that's what they, they're mostly used for in the world, so, yeah.
Refer back to that maybe if that's of interest.
you mentioned intervals.
Should we cover those
Nikolay: yeah, but, but one, one more comment about indexes.
Uh, there is a very popular type of index called gin, right?
And there is big problem, uh, that usually you have a full text church, and you want to order by.
uh, number all timestamp.
And to solve this, there is index called ram.
it's not present, I think on rds.
I, I remember I personally asked them to consider it, but the problem with it, usually index size is huge.
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.
Huge.
So idea of Ramm is you have Gene, plus you have knowledge about some timestamp or integer eight.
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.
So Ram is like gene extended with some times.
So now you can have, in single index scan, you can have almost single index scan, but you can have, uh, both.
Operations covered, full text, search and order by, uh, sometimes stamp, which is perfect for, like search in some comments.
For example, you always usually want to fresh comments coming first, right?
So it's very great idea.
I wish post this, uh, like it would be more include to core contribution models, maybe anti contribute models, but it.
And it has issues with size and, and affecting performance and so on.
But idea is great and like, I mean, the goal is great, so also it becomes timestamps.
So it's interesting that it injects timestamp to gin and it becomes around very strange, right?
Michael: At the risk of going o off topic, I know gin is not named after the alcohol.
Uh, and one I think is a play on that, that it is.
But there's also, I heard, I only read briefly about it, but, uh, an index type
by the
Nikolay: was only like, uh, in development and never reached some like production stage.
But Ram is quiet like.
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.
Need is is huge.
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.
Tease that and then, uh, apply filtering, in memory for full tech search, which both are not perfect paths, right?
So like the problem is obvious and it's not fully solved, but with Ram solved again, like maybe you should try it.
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.
Always because like, okay, this, this, uh, frame framework of.
In pogs, I like adopted it already and I cannot, see time when it's timestamp.
No, it's not time.
It also includes date, like at least let's say daytime.
By the way, maybe standard also has daytime.
I remember other database system have daytime as data type, so there is date, there is time.
When we combine them, it's the is time stamp, right?
Like it's full thing.
Michael: Yeah.
Nikolay: we can have also a column of type.
Right Or of type time.
Why not?
Michael: Yeah, but I think date gets date's difficult because of the boundaries, right?
Like what does like midnight and time like?
I think it's tricky.
I think personally I would stick to timestamps in general, but what I, maybe
Nikolay: Well,
Michael: the point of
Nikolay: uh, too better in terms, two times better in terms of performance because, uh, in terms of storage, because of it's four.
Right.
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.
So you should combine with other four bite column.
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.
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.
So it's, it's, it's becoming tricky.
So probably this is where you probably will try to.
Timestamp without time zone, just to be concrete.
Okay.
It's utc, I can see that as utc.
This is our date.
That's it.
Again, depends, right?
But if you think about time, technically you can use timestamp, just like choosing some date.
Always like January 1st something, right?
I don't know.
But it's not, uh, it took ugly, right?
So time sometimes is.
Michael: Yeah, so there's.
There's time with time zone as well and time, and there's really good note in the documentation.
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.
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.
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.
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,
Nikolay: Yeah.
Michael: the time of day, not, uh, not a time in, in with a date.
And so I think I, I can see more uses for that.
, and I probably would store it as time because it's not, uh,
Nikolay: Yeah, maybe.
Yeah.
Also was mentioning they all, both timestamp, both of timestamps, and both of times, uh, they have precision, um, how to say modifier, right?
So you can, in parenthesis you can say, I, I don't want a micro.
Just put a zero there and that's it.
Also convenient.
I use it sometimes because like it becomes easier to have predictable output without just now converted to timestamp.
And zero in parenthesis gives, gives you, timestamp with seconds, but without my milliseconds.
Michael: The Wiki advises against it, I think, because it rounds rather than truncates.
Nikolay: Okay.
Michael: it, it's possible to round to a time
Nikolay: Okay.
Michael: which might not be what you want, but yeah, it's interesting and they, I think they recommend truncate instead date trunk as
Nikolay: Okay.
Michael: or time, I
Nikolay: Date trunk will give you Yeah, it'll give you, you can give, you can take seconds.
Yeah.
Right.
So, yeah.
And everything else will be filled like with zeros, right?
Right.
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.
left timestamp value, comma 10.
It gives you date usually, right?
It's, it's stable because we have four digits for year and divider and like, and so on and so on.
So left is much faster, but of course they Trunk Day for example, also works.
. So it depends.
It, it depends.
But left function, I love it.
It's, of course it's to work with text, but it's, it's much shorter, sometimes, so, yeah.
but interesting that time is a regular time without time zone.
It's also by, but with time zone, it's 12 bites.
Michael: Yeah, I didn't understand that.
How could it be more?
Nikolay: Surprise.
I don't know why, but surprise.
So that's why I usually try to stick with time stepss as, as long as I can.
And only then I, I go to less, uh, for me to less popular, data types.
But interval is, interval is even worse.
It's uh, 16 bytes . So
Michael: But I, yeah.
So why, why is that?
Is it because it's storing two timestamps?
Okay.
Nikolay: our listeners.
Leave a comment if you're watching a YouTube for, for leave a comment.
Please explain why it's interesting question.
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.
Interval is, Very good.
Um, it's very powerful and, uh, very flexible, very convenient.
I use it a lot, including for dba practice.
For example, you want to understand how long your transaction or statement lasts.
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.
By the way.
Question to you.
what would you use if you select from activity, uh, to see when transaction, like transaction age for.
How would you use it?
So there is exact, exact start,
x a c T under score start, column.
And there is of course current timestamp.
So most people, not most people, many people use now function minus, uh, exact start.
But uh, it's quite.
Now function is very good in terms of performance because it gets timestamped only once in the beginning of your transaction.
So your, your, your select is also a transaction, right?
So now is calculated only in the beginning, but activity, it's not a normal table.
Uh, and, uh, values are constantly shifting.
And we, if it's quite big while we read it, we, we, it's not stable.
It's not, we don't have snapshot there.
You will have like shifted values.
You probably will end up with, uh, negative age negative intervals.
So what you should use is less performant because less performed function because it will calculate, current timestamp for each row.
It's called clock time.
So clock, times stamp minus exact uh, start and um, transaction start.
Right?
So, and in this case you won't have negative values, which is good way, . So yeah, small trick.
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.
Okay.
Good.
Uh, anything else we wanted to.
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.
For consider using
Nikolay: Hmm.
Michael: So I think the, the, the good cases that I've heard, uh, for this are things like act
Nikolay: Yeah.
Yeah.
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
Nikolay: Yeah, I also use it, this approach.
Yeah.
But you should remember storage, uh, overhead, of course, because bullion, of course, it's not one bit, it's one bite.
It's already, you already are eight.
Time worse than you could be . Oh, you can be like most, uh, storage, uh, efficient.
uh, approach would be like, uh, spend one bite for eight, check boxes, right?
And then, work with bit operations.
But, uh, okay.
If you have bull column, it's one bite.
Of course if you, if you pack, if you have eight of them, they will occupy eight bites.
It's good if you have boo and then timestamp or integer eight, you will have seven bites lost anyway.
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.
Right?
Michael: Yeah, but it was
you're right in terms of storage.
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?
Um,
Nikolay: When,
Michael: it makes, it's made
Nikolay: when you, when you are designing, think about if you need, uh, to remember when check boxes are, you are pressed, right?
Clicked or checked, right.
So yeah, it's, it's depends on the application of.
. Oh, I wanted to mention, uh, these old balls.
You know, old balls.
What, what is old balls?
Michael: Oh yes.
So there's,
Nikolay: Yeah.
Michael: time, the
Nikolay: All right.
0, 0, 0 time utc.
Right.
So, yeah, it's a, it's kind of like interesting joke maybe, right?
So, but,
Michael: but it's committed, right?
Nikolay: It works.
Of course, you can say,
Michael: Yeah.
Nikolay: oh, by the way.
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.
Codes, single codes, and then converted to interval and that's it.
Or say minus interval.
And then, uh, string, literal, one day it works perfectly or one hour one, like it works.
But, uh, also there is, uh, so wall ball sits like also in single, uh, quotes.
You converted to time and.
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?
This, uh, is useful.
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.
Michael: that's a really good point.
So I, um, there's.
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.
And you probably want less than one and greater than another.
And that's, I, that's where I've seen infinity used most.
So you might want to be, selecting all times from in the future to positive infinity or from sometime in the past.
But less than, uh, minus
Nikolay: All right.
Right.
Agree.
Good.
So what else, or that's it.
We wanted to, to keep it short.
Michael: all I
Nikolay: Yeah.
Then let's wrap up and uh, call today.
Right.
Michael: Yeah.
Fabulous.
Thanks so much.
Thanks everyone for listening.
Keep the suggestions coming and see you next
Nikolay: Thank you.
Bye bye.