Brace yourself because we're going to talk about time zones. Take a deep breath, find your center, get your tea, or whatever you need to do to relax. It's going to be fine, time zones are very frustrating. One of the reasons that time zones are frustrating is because they're political, not mathematical. We might say, "Great, we'll just move it six hours over." Well, are you sure about that? Is it currently daylight saving time? Are you going to mess up? Is it currently daylight saving time, the date that you're storing is in fact not in daylight saving time? That gets really frustrating. What's even worse is the time zone identifiers. If you simply use offsets, sometimes they're positive, sometimes they're negative.

Here's going to, here's my overarching recommendation when it comes to time zones. Keep it in UTC for as long as possible. Keep it in UTC for as long as possible. Convert it to whatever the user needs at the latest moment possible. That is my recommendation for sanity. My second recommendation for sanity is use named time zones, not offsets, not our offsets. I'll show you that as well I'll show you where you can get totally hosed if you use an hour offset accidentally, you'll end up actually going the opposite direction of what you're trying to do. Keep it in UTC. Let's use named time zones. Lemme show you. Keeping everything in UTC is a matter of making sure that your time zones are set to UTC. We see show time zone here is set to UTC. We can set time zone equal to America/Chicago. I'm not from Chicago, that's my Chicago accent. I'm from Dallas. Then we were to show time zone again. Now we're set to America/Chicago. That is for this session.

You can set that, you can set that per session, you can set that in your PSQL RC file, wherever you want to set your session, you can. If we were to disconnect reconnect, it would reset to the database time zone or the cluster time zone. To change that, we have two options. We can say alter table, nope, alter database demo, set time zone to UTC. That is one way that you can explicitly change your database to the correct time zone, what in my opinion is the correct time zone.

If you don't want to do that, you can change it at the cluster level by modifying your Postgres configuration file. You will have to reload the configuration file after you're done. Importantly, if a database has declared its own discrete time zone, then this default will not apply. The Postgres configuration file is the default then you have the database on top of that then you have your session. Why I am going to set my session back to, let's do it down here. I'm going to set my session back to UTC. We're UTC there, UTC there. Why is this important?

Remember when we talked about timestamps timestamps with time zones, that when a new value comes in for a column that is a timestamp with time zone, Postgres is going to convert that to UTC for storage then convert that back to whatever the time zone is for display. That conversion takes place if your database or your session is not set to UTC. Lemme see if we can prove that. We're going to select this literal again we're going to cast it to a timestamp TZ. If we do that, you see it has been given a time zone, it has been given a time zone of plus zero zero. Where did that time zone come from? You got it. It came from our time zone setting. The first thing that we need to know about this is if you pass in a value or a literal that doesn't have a time zone you're saying please put this in a time zone aware column, it's going to pick the default. Now if we were to say in fact, this is a plus 0, 0, 0, 0, that still works just fine. Now watch, if we were to change this to America/Chicago, it's 11:30 we're going to change that. It's 11:30, now it's 5:30.

What happened? What happened is this is a legit time at UTC, when it was cast to a timestamp TZ coming on the way back out, Postgres looked said, "Aha, you exist in America/Chicago, I'm going to take this I'm going to shift it six hours to account for the fact that you, me, Aaron am in America/Chicago my setting is America/Chicago." it converted it for me. That may be incredibly useful. That might be exactly what you want. That is hardly ever what I want. What I want most of the time is I just want you to give me back UTC. I just want you to give me back UTC, then maybe later in the application I will work with it. Now what happens when you want to convert a UTC timestamp to an actual local timestamp for displaying? You can totally do that. You don't have to do that on the application side you don't have to change the time zone of your session to do that. We have affordances for that.

Before I show you, I want to give you one giant huge caveat. Please, if you're backgrounding me, that's fine. Pay attention to this part. Use a named time zone. Use one of the time zones that is named for two reasons. One, it's going to cover daylight saving time two, it's not going to absolutely shoot you in the foot when you accidentally flip a sign. Let me show you what I mean by the flipping of the sign. Let's confirm we are still UTC show time zone. We're still UTC. If we select that, you see we still have a plus zero zero, we're casting it to a timestamp TZ. I feel like we're in a good spot to start. , we're going to leave that one alone. Then we're going to say at time zone America/Chicago. If we run that, you'll see that's a six hour difference. That's correct. That is correct to me, that is what we are looking for. It has converted it. Importantly, it has lost the time zone identifier.

If we did a PG type of we ran that, you'll see it is a timestamp without time zone. This I think should be the very end of your pipeline where you're just displaying it out to the user. You're no longer doing date math or operations on it or anything like that. We have converted it to a local America/Chicago timestamp. Now let's carry on do this again, instead say CDT, I think CDT is right, no CST, standard time. There you go. That's part of it. I mean whether or not you're in daylight saving time is really going to muck you up if you do it wrong. Let's give these some names. We'll say as base as America/Chicago, as CST as CDT. Okay, the base, why did I call that base? Obvious, that was right in front of me. The UTC is 11:30, America/Chicago is 5, CST is 5, CDT is 6. , provided you're always picking the exact right one between CST CDT, you'll be fine.

If you use the named time zone, you're going to be much better off. Now, you can use the hour offsets, what you're going to find may surprise you. Time zone, we are negative 6. We are a six hour offset from UTC. This is going to blow your mind. 17. 17, not 5, not even 6. 6 is wrong, it's directionally correct. 5 is the correct answer. 5 o'clock in the morning, 6 o'clock in the morning is close, off by one. This is off by 12, right? Isn't that 12? That's off the wrong direction. If I were to change this to a plus 6, you'll see hey, we're right again. I am certain, because I've looked many times that the central time zone is a negative 6. This is the problem, this is the problem using our offsets. Watch this again.

Our offset, let's do an interval offset we'll say interval. There, we're back to where we want to be. 5 is correct, 6 is close. Good try. 5 is correct, 5 is correct. 17, super duper wrong. This is incredibly frustrating, I will admit. Here's what's happening. I'll just cut to the chase here. The Postgres docs, which I have scoured to no end, the Postgres docs declare three different ways you can specify a time zone. The first is a full time zone name like I've been recommending, America/Chicago. That is the first way that you can do it.

The second way is the time zone abbreviation. That's the CST, CDT. That's fine. You might have to handle daylight saving time yourself. That's not ideal, it's okay. The third way listed in the docs, I'm going to read this for you. In addition to time zone names abbreviations, Postgres will accept POSIX-style time zone specifications. This option is not preferable, may be necessary if the time zone entry is not available. Importantly, it says it may it, you can use a POSIX-style time zone specification. On a very separate page of the documentation, it says what a POSIX-style time zone definition is. It has this footnote here.

The positive sign is used for zones west of Greenwich. Parenthese, note that this is the opposite of ISO-8601 sign convention used elsewhere in Postgres. This is the opposite of ISO-8601 used elsewhere. If we go back to, yeah, let's come back here. If we go back to this, what is happening here is this is the first version that the docs recommend, fully named. Awesome. Love it. This is the second version. Abbreviation, fine if you have to. This is the third version, which is interpreted as a POSIX-style instead of ISO-8601 style. In fact, it is the exact opposite of what you're looking for. Because we're moving six hours in either direction, that's where we got our 12 hour difference of being wrong.

If, for whatever reason you must use an hour offset, use this interval in the middle. Save what sanity you have left after fighting with all of this. Do not use a bare hour offset like this because you're going to be wrong in, it's going to be two, it's going to be wrong in, instead of moving left, you're going to move right, your order of magnitude there is going to be quite large. Frustrating. I know. Here's how you can get a list of all of the time zone names. If you want, you can just see what you've got going on there.

You see lots of good ones, many good ones, 597. If you want to narrow it down, you can see like Chicago you see abbreviation currently is CDT because we are existing in daylight saving time, which is a UTC offset of negative 5 instead of negative 6. Which honestly, this proves my point quite nicely. We looked at this time zone table we are currently in DST, we're in daylight saving time at the time of recording, which is correct, that date, that January 31st date that we've been operating on exists in central standard time. I don't care, I really don't care as long as I use a named time zone identifier. I'm going to use America/Chicago, should you. Hopefully that will save you from a lot of pain down the road.