In this video, we're going to talk about storing a date a time together in the same column as a date time. In Postgres, we actually call that a timestamp. In some other databases you'll see it called a date time, in Postgres, it's called a timestamp. There is a way to store a date separately in a different column, a time. I don't find it terribly useful to store standalone discrete times. However, storing dates is obviously quite useful, we'll talk about that in a little bit.

When it comes to storing your date times, you have two options in Postgres, a timestamp a timestamp with time zone. More correctly, it would be timestamp without time zone, timestamp with time zone. There's a pretty big difference. They support the same range, they have the same underlying storage, there's a pretty big difference.

When it comes to a timestamp without a time zone, Postgres makes no efforts to do any conversion whatsoever ever, anytime at all. With a timestamp, with time zone, what it's going to do is it's going to take the value that you give it, convert it to UTC to store it in the database, then when you pull it back out, it's going to convert it to whatever time zone you are in. That's the big difference.

My recommendation is always go with the timestamp with time zone option. That's going to make your math or your date math a lot more efficient a lot easier for you, the human to write. If you want to store a timestamp that has no reference to time zone whatsoever, it will discard all time zone information, it will never translate anything. You could use the timestamp without timezone. My strong recommendation for you is timestamp with time zone. I'm going to have a few more recommendations because time zones can be a nightmare. We'll just say that they can be a nightmare.

First, let's create a table. The first thing you're going to want to do is just type timestamp, like I said, without time zone is the default there. You guessed it. You can just make it with time zone. If you're not fond of typing, timestamptz is the timestamp with time zone type. Now, you can pass through zero to six here this dictates how many fractional seconds you want to store. Let's take a look at this real quick. We can select now as a timestamptz with... Let's just do three see what happens. You'll see we have three fractional seconds down here, 0.438. If you do not pass anything, it's going to take the value from the literal then which in this case is six. It will take the number of fractional seconds from whatever you give it, up to the maximum of six. Now, importantly, I want to show you one thing. Yeah, let's do this. If we do one, zero none we run that, you will see something very interesting here. Got it on the first time, which is quite lucky.

If we look here, let's look over here at this one that is completely literal, no truncation or rounding at all. You'll see it is 01:35:01.92. .92, then this one that we said use zero fractional parts is 02. Technically, this value right here is in the future at the time that we inserted this row because it was rounded up to the second, second while we were actually at the 1.9 second. I don't know. Does it matter? I don't know. You could potentially have a date in the future where you're expecting it to be a date in the past. I don't know if that matters for your situation. If it does, you always have date_trunc to the second you can select now on that. In fact, let's bring this up to our comparison guy up here. If we run that, I'm looking for .3, .5. Okay, .5 will round up. You see it's right here, it's 36:17.5. Over here, it's 36:18, over here it's 36:17. I don't know what literals you're going to be passing to your timestamp columns, I don't know if rounding up to the future matters to you.

If it does matter to you, you can use the date sec or the date_trunc second function to just lop it off instead of rounding it up, potentially. Potentially, somewhat helpful, at least an interesting bit of trivia. The thing I want to talk about now is what are these values that we're going to insert? Let's forget the now function here what are we going to hand over to Postgres, potentially, from our application side? You have a couple of different options. Postgres is very forgiving on the formats that it will accept. I don't like that. I mean, I'm happy for Postgres that it does that. I don't like leaving it up to Postgres to determine what my date format means. I want to always communicate in ISO 8601, of course, as we all do.

Let's take a look at ISO 8601 here. ISO 8601 is my absolute favorite. It looks like this. It goes year, month, day, hour, minute, second, potentially, fractional second, potentially, time zone. Now, you might see it written like this with a T in the middle. That's cool. That's totally fine. You might see it written like this with a Z at the end. That stands for Zulu, which is a +00 off of UTC. You might see if you're in the Central time zone at certain parts of the year, you might see -06:00. This is a UTC offset, in fact, I don't remember where it is, there's one time zone that has a 30-minute offset. I even think there's one that has a 15-minute offset. I can't remember, it might be an Australia. Who knows? Time zones are wild. Now, here's the thing about ISO 8601. It is a standard of which there are many, this is a standard for date times it is not ambiguous at all. What is ambiguous is 1/3/2024. What else is ambiguous is 3/1/2024. What do these dates refer to? Well, it depends on where you are in the world.

If you're in the USA, this means January 3rd, this means March 1st. If you're literally anywhere else in the world, that's not what that means. This means March 1st, this means January 3rd, very ambiguous. Postgres is very forgiving in the types or the formats of date it will accept, which is nice. If we did show date style, we look at that, we have in fact two settings here. We have one setting before the comma, which is set to ISO, one setting after the comma, which is set to DMY.

If we were to look at this, let's select a literal of... Let's just do an ISO literal here, 01-31. We were to cast that to a date. Okay, we get an ISO date back. Nothing very interesting far. A little bit interesting to be honest. Then if we were to say 1/3/2024, what's going to happen? Okay, we got back in ISO because remember, this first part right here, that determines the output.

The second part determines the ambiguous date setting. This is the ambiguous date format right here. When we say 1/3/2024, Postgres says that is ambiguous. I agree. I do in fact agree. According to this setting here, it is day, month, year. , when that comes back, we have the day as 01, the month as 03, the year as 2024. Because of that setting, we can change that. We can set date style equal to ISO comma, what is it set to now? It's set to DMY. Let's set it to MDY. In fact, we'll do a quick comparison. We'll just copy it down we'll run it back fourth. Let's first set it to MDY, if we select it again, now we're at January 3rd, then we're going to, wink, switch it back. Now, we're at March 1st. This is how you control how Postgres interprets ambiguous dates. I don't think you should give Postgres ambiguous dates. I don't like this behavior. It's not a knock on the database itself. I just think this is a little bit risky. I want, as the developer, as the person using the database, I want to be as clear as possible sending ISO 8601 dates over feels more clear to me.

There are other options that you can put here. It doesn't always have to be ISO. ISO is the default I wouldn't change it, not at all, not even a little bit. Hey, we're having some fun, let's change it. If you were to change it to SQL, remember, this determines the output. If you were to change it to SQL, then let's put an unambiguous date in here just we can, 2024-01-31. We've changed the output format that is an output format. This is in fact not the SQL standard. This is a lucky or unlucky coincidence of naming that that is called SQL.

For some reason, the Germans have their own style here in PostgreSQL, it is 31.01.24. You could knock this whole thing out put European, that would cover both formats, there you go. I think you should stick to ISO comma whichever, MDY, DMY that you want, because hopefully, that part is never invoked. That ambiguous date format is never invoked. Hopefully, I've convinced you about ISO 8601, there's one other thing that I am certainly sensitive to, that's Unix timestamps. They're awesome. They're just numbers. Everything is clear, clean. What do we do when we get a Unix timestamp? Well, there is fortunately a nice little function we can use.

We can use select to_timestamp then put our integer or potentially afloat for fractional seconds in there. If we select that, you'll see we do get back a timestamp with a +00, which is fundamentally always correct because a Unix timestamp is an offset from the Epoch at UTC. If we were to do our old friend PG type of, wrap that guy up, you'll see timestamp with time zone, which is correct. The time zone is set to UTC. If you had fractional parts, it would preserve those fractional parts. If you do get a Unix timestamp, this is a good way to convert it into the correct type for Postgres, which is a timestamp with time zone. What is a discussion about date times timestamps without discussing time zones? We're going to save that for its own video because time zones are both incredibly important incredibly complex.

The takeaway from this video is you likely want to be using timestamp with time zone, unless you have a good reason not to. My recommendation is always use ISO 8601 that there is no ambiguity you're not relying on that server setting. If you do need a Unix timestamp, you do have that to_timestamp function available to you. Now, brace yourself. We're going to start talking about time zones.