SQLite might not have a format to store dates. Well, it definitely doesn't have a format to store dates, but there are 3 different ways that you can store dates or date times that work exceptionally well with SQLite's date functions of which there are many. We're gonna look at all 3 and a few of the date functions. I'll list them for you. Now, the first one would be stored as text in ISO 8601 format which I'll show you here in a second.

The second would be store it numerically as a Julian day. This is very interesting. The third 1 would be to store it as a UNIX time stamp, number of seconds since 1970. No matter which way of these 3 that you choose, it's gonna work with SQLite's built in date functions. Let me show you.

Starting with ISO 8601, we can get a peek at that format by selecting date. And this format is YYYM ddyearmonthday. It also supports, time. So if you did date time, you would get year month day, hour, minute, second. So you get year, month, day, and then, this is from PHP, HISI don't know exactly what that looks like in other languages.

In fact, in PHP, this would be YMD like that, I think. But you get year, month, day, hour, minute, second. Now, moving on to Julian day. Now, Julian day is interesting because never in my life have I used it, but that doesn't mean it's good or bad. It is the fractional number of days that have passed since noon Greenwich Mean Time on November I gotta check the notes.

November 24th 4714 BC. No idea. If you wanted to see it, you could say select Julian Day and you get it's been 2, 000, 000 days, which is a lot of days. It's been 2, 000, 000 days since BC, 4714. Moving on.

I don't have no idea if Julian day is useful. Maybe it's useful to you. If you do unix epoch, you get the number of seconds since the unix epoch but you could get the number of seconds and a fractional part if you need the fractional part. I think let's go ahead and put them all on screen here. So if we did fractional part.

I think let's go ahead and put them all on screen here. So if we did select date, that's, ISO 80 601, time as its own part of ISO 8601, date time as ISO 8601, then we have JulianDay as a number, we have unixepoch as a number. And what is the last 1? We have, the other 1 is strof time, STRF time, and this will give you a format and then a time value and a modifier. So you could say, just give me let's see.

Just give me the day of the month from now. And if we run that, you'll see that it is the 11th day of the month. And finally, the 7th date time function is a timediff. Diff, and you put in, some time value. So we'll say we'll say date and date, and I bet that comes back as 0.

There you go. So 2 dates are the exact same. That's obvious. One of the more interesting and more powerful things that you can do with these date functions is pass in modifiers. So coming back over here, let's hop up to the top and let's play around with date time.

So the first thing you need to do is you need to pass in, the time. Now is the default but because we're gonna start chaining on modifiers, let's go ahead and add now in there and then we can do stuff that's pretty cool. We can say start of month. So let's, let's get rid of that and say now is 611, and if you say start of month, it goes to 61. But you can keep going.

So we could come in here and say plus 1 month. Now we're at 7 1. Now we can say minus 1 day. And now we're at the last day of the current month. Just by chaining on a couple of modifiers, we've we've we've mucked about with the date to get the last day of the current month which is very interesting.

You could also feed in, let's take this unix epoch here, and we could feed in this unix epoch right there, but that's not gonna work exactly like you want. So if you need to take a unix epoch and convert it into a datetime, you could do something like this. You could say that this is a unix epoch and now SQLite knows, oh okay, I'll interpret that first one as a unix epoch and then we could do, we could do the whole thing again. I won't do it all, but we could do the whole thing again using a Unix epoch as your starting point. We're gonna do another modifier example, and this time we're gonna look for American Thanksgiving.

US Thanksgiving falls on the 4th Thursday in November. How can we find that? Let's start with the date. Yep. Then we pass in the date of now.

Exact same thing, but now that we're gonna add modifiers on we have to pass in the date of now. Let's do start of year. So if we do start of year, that puts us back January 1. Then we can add how many months. Well, if November is month 11, we're already at month 1, let's say plus 10 months.

That gets us to the beginning of November. Now we gotta find Thursday. Fortunately, it is super easy. Weekday 0 gives us Sunday, Monday, Tuesday, Wednesday, Thursday. That gives us the 1st Thursday.

I would love to say plus 3 weeks, but weeks isn't a valid modifier so you get nothing. So instead of plus 3 weeks, we'll say plus 21 days which, using my powerful brain, is 3 weeks. And if we do that, we get 11 28 of 2024 and we'll say thanksgiving is November 28th. We did it. So this is a very cool feature that SQLite offers.

I'll have the full link to the docs that has a full list of modifiers. I had to actually look at the docs to see if weeks was a modifier. Unfortunately, it's not. Let me show you just a few more date things before we move on. If we were to do something like select timediff, of now, and we can just throw in, let's say 1989, 02, 14, very important date.

We can see that the number man, it's it's been 35 years. It's been 35 years since that particular date which makes me feel old for no particular reason. We can do also we can do days. So if we did Julian day minus, let's do Julian day again, and we can pass in 1989-0214. And we get the number of days since that very important date.

And we can wrap this guy and say divided by 3655. And again, man, it didn't change. Still 35 years which is just it's just too many years. While SQLite may not have a date type, it definitely doesn't. Hopefully, this assuages some of your fears that SQLite is underpowered when it comes to dates.

It's really not. It just doesn't have a native type for it. So you can store it as text, ISO 8601. You can store it as fractional numbers, Julian day, or you can store it as an integer, unix epoch, or if you wanted a fractional number with the unix epoch plus the sub second if you wanted to do that. So lots of ways to store dates, lots of ways to manipulate and modify and query against and play with dates.

So if you need to use dates in your database, you're just fine using SQLite.