Dealing with Dates in SQLite

Introduction

This is where things start to get really interesting.

In the original Birthday Reminders application we showed the date of a person's next birthday and their age next birthday on the index page.  For sending email reminders we need to know not only a person's next birthday, but the number of days to their next birthday.  In the original application we were able to use SQL Server functions and stored procedures to achieve this.  These are not available to us in SQLite and so we will need  to write the 'raw' SQL before passing it to SQLite as a parametrised select query.

Age Next Birthday

I'll start with 'Age Next Birthday' as it's slightly easier than the date of a person's next birthday.

The age of someone at their next birthday can be calculated by deducting the year of their date of birth from the current year - almost!  But it depends crucially on whether they have had a birthday in the current year, or it is still to come.

Consider two examples, Jane was born on 10th January 2000 and David was born on 10th December 2000 and it's currently 10th June 2022.  Dealing with Jane first; her birthday this year has already occurred, so her current age is 22 (2022 - 2000), so her age next birthday will be 23 (2022 - 2000 + 1).  On the other hand, David hasn't had his birthday this year, so he will be 22 on his next birthday in December (2022 -2000).

The first thing we need to do is determine whether a person has had their birthday in the current year, and to do this we can use the SQLite iif() function. The structure of this function is: 

IIF(expression, true_expression, false_expression);

In English we can express what we need as:

IIF(Birthday has occurred, Current Year - DoB Year + 1, Current Year - DoB Year)

The next problem is how to work out, in code, if the Birthday has occurred.  One way to do this is to compare the current 'day of the year' (i.e. how many days since 1st January), with the birthday 'day of the year'.  Fortunately there is a SQLite function that allows us to do this, it is 'strftime' and is used like this:

strftime(format, timestring [, modifier1, modifier2, ... modifier_n ] )

Where format can extract, for example, the day part of a date by using %d for day of the month, %m for the month, %Y for the year and, most importantly %j for day of the year. 'timestring' can be date or time in various formats, ('now' being the current date, any other date must be in the format ;YYYY-MM-DD').  'modifier1', etc. can be used to add/subtract years, days, months to the result of 'format, timestring'.

Using strftime we can get the current day of the year (line 1 below) and the day of the year of the person's birthday (line 2), and combine them with "<" to form an expression comparing the two (line3):

strftime('%j', 'now')
strftime('%j', PersonDateOfBirth)
strftime('%j', 'now') < strftime('%j', PersonDateOfBirth)

In a similar way we can do a calculation to subtract the year of birth from the current year by using the following (e.g. 2022 - 2000 = 22):

strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth)

Combining these techniques we can calculate the age of a person at their next birthday:

Date of Next Birthday

Since we know the date of birth of the person and, now how to calculate their age next birthday, we can simply(!) add their age next birthday to the 'years' part of their date of birth.

The SQLite function we will be making use of is the 'date' function, the structure of which is:

date(timestring, [modifier1, modifier2, ...])

Where 'timestring' is a date or time in one of the SQLite formats (e.g. YYYY-MM-DD) and 'modifier1', etc. are adjustments, such as + or - NNN years, NNN months, NNN days, etc.

We can therefore use the function like this to get:

date(DateOfBirth, '+AgeNextBirthday years')

But there is one other SQLite function we will need to be aware of so that we can insert the 'AgeNextBirthday' into the 'date' function, and that is the string concatenation function.  This is expressed as || and is used to join two strings of text together. A simple example is shown below which concatenates first name with last name, inserting a space ' ' in between.

SELECT PersonFirstName || ' ' || PersonLastName as PersonFullName from Person

Taking everything together we can arrive at the following, which will return the AgeNextBirthday and NextBirthday.

Modification of PersonListGetByUser

With the ability to retrieve the 'AgeNextBirthday' and 'NextBirthday' we can modify the 'PersonListGetByUser' method to include these columns.  The code for this method is shown below.  Replace the function in PersonService with this code:

 public async Task<IEnumerable<Person>> PersonListGetByUser(string PersonSendReminderTo)
        {
            IEnumerable<Person> people;

            var parameters = new DynamicParameters();
            parameters.Add("@PersonSendReminderTo", PersonSendReminderTo, DbType.String);

            sqlCommand = "SELECT PersonID, PersonFirstName, PersonLastName, ";
            sqlCommand += "PersonDateOfBirth, PersonSendReminderTo, ";

            //If Statement:  iif(Test Expression, Do if True, Do if false)
            sqlCommand += "iif(";
            //If this is true 
            sqlCommand += "strftime('%j', 'now') < strftime('%j', PersonDateOfBirth), ";    //(current day of year < day of year of birthday)
            //Then do this: 
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth), ";    //Calc their current age if current day of year < day of year of birthday
            //Otherwise, do this:
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth) + 1";  //Calc their current age if current day of year > day of year of birthday
            //Close If statement
            sqlCommand += ") ";                                                                 
            sqlCommand += "as AgeNextBirthday, ";

            sqlCommand += "date(PersonDateOfBirth, '+'||";

            sqlCommand += "iif(";
            //If this is true 
            sqlCommand += "strftime('%j', 'now') < strftime('%j', PersonDateOfBirth), ";    //(current day of year < day of year of birthday)
            //Then do this: 
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth), ";    //Calc their current age if current day of year < day of year of birthday
            //Otherwise, do this:
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth) + 1";  //Calc their current age if current day of year > day of year of birthday
            //Close If statement
            sqlCommand += ") ";

            sqlCommand += "||' years'";
            sqlCommand += ") ";
            sqlCommand += "as NextBirthday ";

            sqlCommand += "FROM Person ";
            sqlCommand += "WHERE PersonSendReminderTo = @PersonSendReminderTo ";
            sqlCommand += "ORDER BY NextBirthday ";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                people = await conn.QueryAsync<Person>(sqlCommand, parameters);
            }
            return people;
        }

Days to Next Birthday

Whilst we are dealing with dates we may as well tackle the next problem.  We will need to be able to calculate the number of days to a person's next birthday for the emailing of birthday reminders.  (They are sent out automatically 21, 14 and 7 days before someone's birthday.)

We need a new method in PersonService, which we called 'PersonGetByDaysToNextBirthday' in the SQL Server version of the application.  We will use the same name in our SQLite version. In the SQL Server version we returned the following pieces of data, and our aim is to do the same with the SQLite version:

  • First Name
  • Full Name
  • Next Birthday
  • Age Next Birthday
  • No of Days to Next Birthday
  • Send Reminder To

We'll start by creating a method that includes all the columns, except 'DaysToNextBirthday':

The calculation of the number of days to the next birthday is (NextBirthday - CurrentDate), where both are defined as the number of days from a common date.  Fortunately there is a SQLite function that will assist with this.  JULIANDAY(date) returns the number of days since noon in Greenwich on November 24, 4714 B.C.  Luckily, for our comparison, it doesn't matter what the starting date is!  But a minor problem is that it returns a number with a number of decimal places (note that 'noon in Greenwich').  However, we need the result as an integer, as we will be restricting the records returned by comparing the result with an integer value being passed to the method (i.e. 21, 14 or 7).  To do this we will need to use the 'CAST' function.  The CAST looks to return the JulianDay of today as an integer would be this:

Cast(JULIANDAY(strftime('%Y-%m-%d','now')) as INTEGER)

The SQLite code for the calculation of days to next birthday would look like thsi: 

CAST(JULIANDAY(strftime('%Y-%m-%d', NextBirthday)) as INTEGER) - CAST(JULIANDAY(strftime('%Y-%m-%d','now'))  as INTEGER)

Except that 'NextBirthday' isn't a column in the database, so we have to substitute the code we have already worked out for that.

Including this into the code gives the following method needed for returning the people whose birthdays are x number of days away:

public async Task<IEnumerable<Person>> PersonGetByDaysToNextBirthday(int DaysToNextBirthday)
        {
            IEnumerable<Person> people;

            var parameters = new DynamicParameters();
            parameters.Add("@DaysToNextBirthday", DaysToNextBirthday, DbType.Int32);

            sqlCommand = "SELECT ";
            sqlCommand += "PersonFirstName, ";
            sqlCommand += "PersonFirstName || ' ' || PersonLastName as FullName, ";

            //Next Birthday
            sqlCommand += "date(PersonDateOfBirth, '+'||";
            sqlCommand += "iif(";
            //If this is true 
            sqlCommand += "strftime('%j', 'now') < strftime('%j', PersonDateOfBirth), ";    //(current day of year < day of year of birthday)
            //Then do this: 
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth), ";    //Calc their current age if current day of year < day of year of birthday
            //Otherwise, do this:
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth) + 1";  //Calc their current age if current day of year > day of year of birthday
            //Close If statement
            sqlCommand += ") ";
            sqlCommand += "||' years'";
            sqlCommand += ") ";
            sqlCommand += "as NextBirthday, ";

            //Age Next Birthday
            sqlCommand += "iif(";
            //If this is true 
            sqlCommand += "strftime('%j', 'now') < strftime('%j', PersonDateOfBirth), ";    //(current day of year < day of year of birthday)
            //Then do this: 
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth), ";    //Calc their current age if current day of year < day of year of birthday
            //Otherwise, do this:
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth) + 1";  //Calc their current age if current day of year > day of year of birthday
            //Close If statement
            sqlCommand += ") ";
            sqlCommand += "as AgeNextBirthday, ";

            // No of days to next birthday goes here
            sqlCommand += "CAST (";                                                         //Start of CAST for NextBirthday
            sqlCommand += "JULIANDAY(strftime('%Y-%m-%d', ";
            //Next Birthday
            sqlCommand += "date(PersonDateOfBirth, '+'||";
            sqlCommand += "iif(";
            //If this is true 
            sqlCommand += "strftime('%j', 'now') < strftime('%j', PersonDateOfBirth), ";    //(current day of year < day of year of birthday)
            //Then do this: 
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth), ";    //Calc their current age if current day of year < day of year of birthday
            //Otherwise, do this:
            sqlCommand += "strftime('%Y', 'now') - strftime('%Y', PersonDateOfBirth) + 1";  //Calc their current age if current day of year > day of year of birthday
            //Close If statement
            sqlCommand += ") ";
            sqlCommand += "||' years'";
            sqlCommand += ")";
            sqlCommand += "))";
            sqlCommand += " as INTEGER) ";                                                  //End of CAST for Next Birthday
            sqlCommand += "- CAST(JULIANDAY(strftime('%Y-%m-%d','now')) as INTEGER) as DaysToNextBirthday ";

            sqlCommand += "PersonSendReminderTo ";
            sqlCommand += "FROM Person WHERE DaysToNextBirthday = @DaysToNextBirthday";

            using IDbConnection conn = new SQLiteConnection(_configuration.GetConnectionString(connectionId));
            {
                people = await conn.QueryAsync<Person>(sqlCommand, parameters);
            }
            return people;

Note:  I am probably being over-cautious by casting the 'Julianday' functions as integers.

To finalise this we now need to add the interface.  Add the following to IPersonService:

Task<IEnumerable<Person>> PersonGetByDaysToNextBirthday(int DaysToNextBirthday);

References & Resources

Powered by Publii