Dates in SQLite

SQLite doesn't have date and time datatypes, which begs the question, how do you deal with your date and time values?  Want to learn more? Check out this video.

Transcript

"SQLite doesn't have any date and time datatypes, which probably seems odd to most developers.

So is this a problem?

Well, it can be a problem if you're not careful, and you'll see why it's a potential problem in a moment here.

Ok, well if SQLite doesn't have date and time datetypes, the first question you probably have is, how do you deal with date and time values?

In other words, what datatypes would you use in SQLite, to store your date and time values?

Well, there are three ways to store date and time values in sqlite, you can store them as:

1. Strings
2. Real numbers or
3. Integers

Ok, so is there a preference on which of these you should use with your date and time values?

Well, no there isn't a preference, and ultimately you can make any of these three datatypes work for you, as long as you understand how they work.

Now, one of the reasons you might prefer to store your date-time values as a string is because it's human-readable, which can be nice, but there's a potential pitfall in using strings to store your dates.

So, what's the problem with storing dates as strings?

Well, let me show you an example.

So, here's a simple comparison of two dates, and let me ask you, will this simple boolean expression return true or false?

Well, let's evaluate this expression using SQLite's command line utility.

But, before I press enter, I'll ask you again, is this date greater than this date?

Well, no this date is before this date, so based on a proper date comparison, this should result a false value, but check this out, I'll run this query, and the result we get is a one, or a true value.

Ok, so why did we get a true value here?

Well, because this isn't performing a date comparison, it's performing a string comparison, and this string is larger than this other string, because this substring in the beginning here, the 2 is greater than this 1.

Ok, so can we do anything to these strings, to make string comparisons work in a similar way to a date comparison?

Yep, we just need to format the date strings in a big-endian-like format.

Ok, so what do I mean by big-endian-like format?

Well, what it means is, we need to break the date up into its component parts, so years, and months and days, then we need to sort these components so the biggest value is first in the sequence, followed by the next largest value and so on.

Alright, so of these three components, which is the largest?

Well, the year, followed by the month followed by the day, and if we were dealing with time values as well, then we'd add hours, then minutes, then seconds and so on.

Now you'll notice that I'm padding some of the date components with zeros, to ensure proper comparisons.

So, earlier this string comparison was true, but let me ask you, what do you think the result will be with these newly formatted date strings?

I mean, if we were trying to perform a date comparison than this expression would be false, because this date is before this date, but again we're not dealing with dates here, we're dealing with strings, so what do you think the result will be, true or false.

I'll go ahead and run this, and as you can see it returned zero or false. So with our dates properly formatted, the string comparisons of dates work like date comparisons would.

By the way, the date time formatting that I've been describing has a formal specification, which is the ISO8601 international standard, and as long as you format your dates using this standard, you shouldn't run into any problems storing your dates as strings in SQLite.

Ok, let's look at storing dates as a real number.

So how would we store a date-time value as a real number?

Well, in SQLite you can use the Julian day format.

So what's the Julian day format?

Well, it's the fractional number of days since noon November 24, 4714 BC.

Ok, the next question you probably have is, how do you use the Julian day format. In other words, how would you go between a date that looks like this and a Julian date that looks like this?

Well, let me show you.

So, first I'm going to create a table named t1 which has one column named Julian date, of type real.

Next, I'll insert into the t1 tables Julian date column, and the value I'll insert is... So, what do we put here?

What would happen if I just inserted a date that looks like this?

Do you think this would store our date as a real number?

Let's try it and see.

Ok, now I'll query the table, and check that out, the Julian date column doesn't appear to be a real number.

Now, just for clarity, I'll write a select query, that uses the typeof function to determine the Julian date's data type.

And when I run this query, you can see that the datatype is a text type.

So, obviously, this insert didn't work the way we're wanting it to.

Now, as a quick side note, if you're not familiar with how data types generally work in SQLite than you should check out my video on the topic.

Ok, so how do we perform an insert like this but using the Julian date?

Well, you can use the Julian day function.

So, I'll insert into t1's Julian date column, and the value will be the result of calling the julianday function, and I'll pass in a date as a properly formatted string.

Now I'll run this insert, then I'll query the t1 table again, and as you can see here, we successfully stored a date as a Julian date.

The next question you probably have is, how do you go from a Julian date, like this, to a string representation of the date?

Well, you can use the date function, let me show you.

So, I'll select the Julian date, next I'll call the date function, passing in the Julian date, and of course, these values should come from the t1 table.

Now I'll run the query, and as you can see, the date function will convert a Julian value to a string representation of the date, cool.

Ok, there's one last way of storing date and time values in SQLite, which is to store the dates as an integer, or more specifically, you can store your dates in Unix times stamps.

If you aren't familiar, a Unix timestamp represents the number of seconds since January 1st, 1970.

Ok, so how would you store a date as a Unix timestamp?

Well, there's a function in SQLite named, Unix epoch, which you can use to generate a Unix timestamp.

Let me show you an example.

So first, I'll create a table named t2, with one column named unix_date of type integer, next I'll insert into the new t2 tables, unix_date column, the value returned from calling the Unix epoch function, passing in a date string.

Next, I'll query our table by keying, select Unix date, then the type of the Unix date, and lastly, I'll call the date function, passing in the unix_date column, and I need to pass the string, Unix epoch, and now I'll run this query, and as you can see, we're now storing the date as an integer, cool.

Now, in this video, we've already used the following date and time functions.

Date, which returns the date as text in this format.

Julian day, which returns the date as a real number.

And lastly, the Unix epoch function, which returns the date as an integer.

Now, there's three other date and time functions you should be aware of:

1. The time function, that returns a time in this format.
2. the datetime function, that returns a date in this format.
3. and lastly a string, format, time function that formats the date, per a configuration string you provide.

Hey, here at Mycelial, we're big fans of SQLite, and we're building tools around SQLite that will allow you to synchronize a SQLite database to multiple computers without conflict.

If you are interested in SQLite and what we're doing with it, please subscribe to our channel, and consider joining our newsletter to learn more."

‍