Exercise- 2.0 Date Functions in SQL

So let's see how all of these things kind of work together. So this is date functions. Now, I know this looks like a lot here, but really it's just the same thing with just one word changed for each one of these.

And just ignore all the SQL Server stuff because we're not doing the SQL Server. So maybe I'm just going to delete that. If I go into the orders table.

Oh, and I'm not getting code hinting. What's the lack of code hinting a sign of? What did I forget to do? I didn't choose my data source. I come up here to choose my data source, choose my server, make sure I'm in the right database.

Now I'm getting code hinting. I like to make that mistake in front of you because I know that you're going to be excited and get ready to go. And you're like, well, it doesn't work.

That's your reminder. Ding, ding, okay. So here's all my orders and notice I have created it over here.

I've got my orders and I've got created that I've got that little clock. It's a timestamp. It's got year, month, day, hour, minute, second point milliseconds, but I don't want the whole thing.

I just want to pull a piece of it. If I show the created at column, just so we can see just that column, and then I create another column for date part date underscore part, I can say, I want just a part of it. Let's say just the hour from that created that call.

So it just pulls out the hour part, the 10, the one, the 19, the five, the eight, just pulls that part. And I can pull out the day. So the 21st, the 22nd, the 12th, just pulls out that part to pull out the month.

Notice how it's just pulling these numbers out, just part of it, not the whole thing. I could do the year. So I get the year.

DOW is something you can't see because you can't see it in here. But if you were to look up April 21st in 2019, and here's a cool thing. You're probably not going to remember what day of the week that was.

Although I've heard that there's some special people who can just be like, oh, that was a Tuesday. I'm not one of those special people. You can go to Google and say, what day of the week was 2019? Um, oh, four, uh, 21.

And it knows that April 21st, 2019 was a Sunday. Remember how I said, we start the week starts on a Sunday. That's zero Mondays, one Tuesdays, two and three.

And so we get all the way to Saturday, which is a six. But if you want to spot check yourself, which is always good because unfortunately SQL Server and PostgreSQL SQL Server starts at one and PostgreSQL starts at zero. So I think it's a good idea to just spot check yourself to make sure you're starting with the right number to make sure you know that zero in this case is Sunday.

So, okay. So that's Sunday. And if I do the 23rd, just in case you're not good with math or anything, the third is a Tuesday, the 23rd.

So Tuesday, because Sunday's zero Mondays one. So Tuesday would be two. That's correct.

Ah, yes. Can you get names instead of, instead of numbers? Yes. Thank you for that lovely transition to the next thing.

Okay. So this is just one way to do this. Okay.

Doing the date part. Instead of that, you can say two car, which is two characters. Now what's weird is they flipped this and they said, put the database location first, the column, and then in quotes here, you can say what you want.

If I say month here, I'm getting the character version of it, not the number version of it. Right? So, cause I'm using two car, two characters. So I can get the month, right? Also, if I do a lowercase M notice how it lowercases it.

So this is important with case sensitivity. Not everything is case sensitive, but this is. If I do all capital month, notice how it's all capitals.

So is it all lowercase? Is it all capitals? Is it capitalized? You can choose how you want the characters to appear. If I say day, oh, there you go. That's what you wanted.

You get the nice day. Now, if you just do D Y that's the short three letter version of it. If you do D notice how that gives you the numerical version of it.

So there's these different abbreviations that you can use to get different parts, kind of like date part. Now, typically I would just use date part for something like this. But like, why, why, why, why am I typing this so many times? This is not giving you a name.

Like they're not going to say, oh, Chinese year of the dragon or something like that. You're not going to get names for everything. Although you can get these different parts in a way, mainly you're using this for things like month and day to get the names of those things, but you can get different ones.

There are a lot of different things you can get. And here I put a link to the PostgreSQL website. You know, uh, when somebody asked earlier, is there, is there a place where I can go get all this stuff? This website is the PostgreSQL documentation.

They do list all of the stuff. So I did link you out to the source, the people who define the thing. And there are lots of different things that you can type in here and you can see all of the abbreviations and all of what they give you.

We're not going to sit here going through every single one of them because once you get the idea, you just put in whatever it is here that you want and they will give you something. But the fullest of our abbreviations is all of these things here, like all the different days that you can do with upper and lowercase and all of those different things. Right.

So I wanted to link you out to all of it. Um, but yeah, so you could do something like day and then you get the names. Again, we're going to ignore all of the SQL Server stuff here.

That's all SQL Server. We're just ignoring all of that stuff. This is one area.

Dates are very different between SQL Server and PostgreSQL, uh, in terms of like the code is always just a little bit different or kind of more majorly different. This is one place where they, they vary a lot more like the SQL Server. So let's say I want to find weekend orders.

If I look at all of my orders, I'm going to see all of the orders not filtering, but I want to filter down to where the created ads is a weekend. So a weekend Sunday is zero. Then you go one, two, three, all the way to Saturday, which is six.

So Sunday's zero Saturday six. So I want to go in and get the part of created that. So I'm going to say the date part that I want is the day of the week from the created at call.

That's going to give me a number. And I want to say if that's equal to zero or six, I think the easiest way to do that is to say, is it equal to one of the numbers in this list? Is it a zero or is it a six? And those are now the weekend orders. You could say date part day of the week equals zero, or this whole thing equals six, but you're going to repeat a lot of your code if you do this or that.

So the end is much more efficient. Also, if you want it to do, let's say weekday, you could say it's between one and five, sorry, one, right? Cause if you got zero and six, if you're between one and five, that's a weekday, that will give you a weekday orders. Cause between those two, between is inclusive of the one and the five and everything in between.

So those will be weekday orders or, or you could also say it's not in that list. That would give you a weekday as well. Cause if it's in the list, that's weekend.

If it's not in the list, that's weekday, right? Cause things can either be weekday or weekend, right? It's one or the other. Now the difference between two dates, like how many years old was something? For example. If I say select now, this just throws in now, like the date and time right now, what date and time is it? 2025 January 22nd, 15 hours, 23 minutes, eight seconds point blah, blah, blah.

And then the time zone. So that's like a, a GMT time zone. If you want to look up GMT, five, you can see what the time zone of our server is.

These are all relative to the server because we're working off the server dates and times, right? But I want to compare now to some old dates. And I want to say, Hey, let me see like the year right now. Like what's the current year.

So I don't need all of this stuff. I just want the date underscore part. And I want to know for now, right? That's the part that I want to get it from.

I want to know the year from this. So that'll just extract 2025. We're just getting that one part when the year rolls over to 2026, this would update to 2026, right? If we were here at new year celebration, we'd see this change, but we're not going to see this change for a long time for a year.

So that tells me what the current year is. Now I'm going to write this in another query here, and then I'll put the two together. So, so we see this, if I go into my orders table and in my order stable, I've got created that.

So let's say I go into create that. So in there, I've got a year, right? So I could do the same thing and say, I want the year from created at somewhat the same thing. I want the year that just extracts the year from that, whatever the year part is.

And I want to compare this year to the stored year. How many years ago was that? If I took 2025 minus 2019, that would give me how many years ago that was, right? So I could do that math. I can say, subtract this date.

So it's the current year minus the old year because the current year is newer than the old one. And then for each row, it's going to tell me what the difference in years are. So I know how many years ago that order was placed.

So you can do math. They're just numbers, right? 2025 minus 2019, right? Six, just doing math on some numbers. That's all we're doing there.

Now, a really common thing for people to do is to say, show me the orders between two dates. That's really common. And maybe you want to know how many orders you had between two dates, but we'll come back to that.

First, I'd just like to see the orders between two dates. If I'm just looking at all the orders here, these are all, but I want to see orders that have a created at between two different dates, right? Because dates are arranged from one date to another. So between is perfect.

Now this is text essentially here because I've got things like dashes in there. So I'm using single quotes around this because of the dashes and I write my dates just like I have them written here. So I can say from 2021, January 1st, right? Year, month, day.

So between January 1st of 2021 and, and let's just say, I want to go to the next year. I go to 2022. So I want to see all the orders between January 1st, 2021 and January 1st, 2022.

So here is an order on January 1st, 2021. It goes through all the way up and notice here, I get then to the end of 2021. Now, the reason that in the example here, I don't go to January is because I know I have an order on February 1st.

If I bumped this up to February, I know for a fact there was an order on February 1st, because I've looked at the data before. But when I look at this results down here at the bottom, notice that I only get to January 31st. Notice I don't get to February 1st.

But I know for a fact, I've looked at this data before and we have an order on February 1st. So there's a problem because I know that these are inclusive. Between is including the start and the end.

So theoretically it should include that day, right? Here's the problem. My data includes time, but I didn't specify a time. So what happens if you don't specify a time? It assumes nothing for time.

It assumes zero hours, zero minutes, zero seconds, and zero milliseconds. Now that's the start of that day, which is fine if I'm starting and I get the whole day. But that's a problem if it's here, because that goes up to the start of that day, but it doesn't go through the end of that day.

So by not specifying a time, I left it up to them to fill in a time and that's not good at the end. Because what I really would need is I need to say go to the end of the day. Now there's a couple solutions that you could do to fix this.

I'll show you the solution I don't like the first way, but I'm going to show you a much better way in just a second. I could say go to 23 hours, 59 minutes, 59.999 seconds. That would go through like the very end of the day.

And just to show you that it works, there's my February 1st, which I knew that we had. Notice it was on 14 hours. So the issue here though is I'm just thinking date.

I'm not thinking time, but the problem is my data has both date and time. And if I don't tell it a time, it assumes a time. And in my case, that's a bad assumption.

So I really have a data type issue. I only am concerned with dates. I don't care about the time.

I don't want to change my database even if I could, because for this particular query, I don't care about time. But for other queries, I might care about time. But for this one query, I don't want to have this be a timestamp, which is date and time.

I want the data type to be simply date, not date and time. And if we want to convert data types, what function do we use? Cast. So I think the best solution is to say that, hey, created at is in the wrong data type.

And if I want to, I can cast that as a date only. And that I think is the simplest solution because now it throws out the time because I converted it to just be dates. Now I know that you see that it works, but I want you to fully believe me.

So I'm going to add a column up here with that so that you can see what it actually does. If I add a new column over here on the right, now I know that it kind of displays a little weird here, but see how all the times went away. So just pretend that those aren't there.

Notice how it's just the date. So by throwing away the time for forgetting the time, it's not even a thing that it thinks about. Casting as date gives you just the date.

Casting as time just gives you the time. Ignore the 1970 thing. It's just a weird way that it displays.

So the point of this is when you have a timestamp, that's saying date and time, but you don't always want both. So the simplest thing in my opinion is to cast it as a date, which throws out the time. It's no longer an issue.

I can simply speak dates and everything works fine. And I include that. I think this is the simplest, cleanest solution to this and it's the most straightforward for other people to understand.

Hey, we're thinking about this as a date. No time, just date. This is where we started to see like real uses for cast.

Yeah, I said earlier like, hey, we have to learn this just so we can use it later. Like these are useful use cases for cast. Because no matter what, I don't want to change my database.

I want to keep that information. I just don't need it for the sake of this where. So you get that.

All right. So this is 2.0 date functions. So let's go ahead and open up that and work through those 2.0 date functions.