We just spent like 30 minutes going through storing date times in timestamp columns, talking about time zones all of that. This is going to be a lot easier. We're going to breeze through this. We're going to talk about storing dates times separately.

If you are storing a discreet point in time in the physical universe, use a date time. Use the timestamp column that keeps a date time together because that is one value. There's no need to separate it. However, if you are storing a time, regardless of what date it happens on, the time column is awesome for that. If you're storing a date that is irrespective of time, maybe you just want to know somebody's birth date or the day that they signed up you don't need the time, well, then a date column is perfect for that. If you're storing a date a time, please don't separate it. Your life is going to be a lot easier if you keep that together.

There are some perfectly valid use cases for storing time on its own. I think those use cases are fewer further between, they totally exist. Store operating hours could be one. You have an open time a closed time. What happens when you start talking about holidays? Well, that is a time plus a date. Even that kind of falls apart a little bit. You can imagine plenty of scenarios where a time can exist on its own outside of a date. That being said, there's a timestamp TZ column.

Just like we had timestamp timestamp TZ, we have time time TZ. Time TZ makes literally zero sense to me. In fact, in the Postgrads docs it says that was implemented strictly for compliance with the SQL standard they don't recommend using it because a time in a time zone is totally ambiguous pointless without a date. 5:00 PM in America, Chicago, well, that could be in or out of daylight saving time we don't really know. To the extent that you need to store a standalone time, do not use the time TZ column. Just use the time column.

Let's take a look at creating some of these things. This is simple, it's almost not even worth covering, hey, we're going to cover it all. That's it, you just type date. There's no precision, there's no with time zone, there's nothing. Honestly, we're grateful for that. We're grateful for just a simple straightforward type. Remember that the ambiguous date rules still apply. If you don't remember what those are, you can look back at the timestamp video. I go over those in detail, too much detail, who knows? If we select this, we see that this has been turned into January 3rd. The reason is if we did show date style, if we check that, you'll see that our ambiguous date style is set to MDY. Meaning the month comes first then the day, then the year. Those same rules apply. The output is still ISO 8601, to disambiguate ambiguous dates, wow, that's kind of a tongue twister. It uses that ambiguous date style format.

Time on the other hand, does have a few options that we can specify. We'll go ahead do that. Remembering that the width time zone is not something I recommend using. I could do it like this. I could say time col is a time, even in this example I don't want to do that because that would suggest that storing a discreet point in human history or in the history of the universe as two separate columns is a good idea. I don't think that that's a good idea. Even for this silly little example, we're not going to have a date column right next to a time column because I don't think that that's a good pattern.

You could do with time zone I don't think you should because a time outside of a date doesn't really have a time zone. Time TZ also exists. Time is the default of without time zone. Then you have the same precision rules that you have for timestamps, which is 026 places of fractional seconds. If we come down here we did 12:01:05 cast to a time we select that, you'll see that that just retained the literal precision. If we did 1, 2, 3, 4, it will retain the literal precision up to six places unless we do this, which will round it, remember, which may or may not be what you're looking for, it will round it to the correct point of precision. You can also remember that if you don't pass it through, it will retain what the literal has, which in this case is up to six digits. The literal up to six digits. There are some fun string literals you can use. All balls is all zeros, I think, hope it's military jargon. That's what I've been told. All balls will give you all zero. A string of epoch will give you nothing.

That is because the epoch is a point, a discreet point in time, not just a time. If we were to change this to time stamp, you would see there's the discreet point in time. There's other fun stuff like tomorrow is a magic string. You can change that one to a date, you can change it to infinity date, you can change it to yesterday as a date or a timestamp.

I will say that using this magic string in something like a storage procedure, a trigger or function definition is not a good idea because this magic could be converted into its actual literal value then that could become stale. If you need, it's fine for interactive sessions, queries like this, if you need a current date plus one for tomorrow or minus one for yesterday, that is the preferred way to do it. If you're using it in a function, stored procedure trigger, anything like that. Maybe just to be safe, maybe just do that all the time.

There's one other thing I want to warn you about with these constants that is not to use current time. 'cause that gives you back a time with a time zone. If we were to run this, you'll see current time gives us a time plus 00. Now the plus 00 is the giveaway, if we ran PG type of current time, time with time zone, bad news bears, we've got a current time stamp that gives you a timestamp with a time zone, which is fine. That's totally legit, we've talked about that a lot. In fact, current date gives you a current date. A local time is going to give you a time without a time zone in our time zone.

If we did show time zone, which it should always be UTC in my opinion, if we did a PG type of local time, you'll see that it is a time without a time zone. If we did local time stamp, you'll see it as a timestamp without a time zone. You need to know what these constants return. I think they're all fine except for current time 'cause that returns the one thing that we definitely don't want to be using, which is time with time zone. As long as you kind of stay away from that one, you should be fine.

Again, you might have a legit use case for a timestamp without time zone you can generate that by using a local timestamp. Whatever you do, make sure you know what it's returning you can always use the PG type of to get at that underlying type determine what it is actually returning.