[SOLVED] SELECT upcoming birthdays in SQLite3

[Hi im Pascal]

Hi,

I'm storing birthdays in a SQLite3 database. At first I was storing a single column birthday e.g. as UNIX time (INTEGER), however, I figured that I don't always know the year someone was born, so I would need some sort of "zero value" for the year. However, TDateTimeEdit etc. don't allow this, they only go until the year 1745 or something. Additionally, the date conversion functions where acting up was well, and I don't think using a specified year as "No year known" is a clean solution.

So I use my own three spin boxes and store three INTEGER columns (day, month, year) respectively, whereby the year column can stay NULL if not specified.

Now I want to query the dabase for all birthdays coming up within the next 7 days, I found many examples on StackOverflow etc. but nobody seems to store a day and month separately. Is this a bad approach? I don't know how to structure a query which will use the current year (or following year, if the birthday has already passed), and then compute the difference in days to (current date + N days) and check if it is less than or equal.

Does anyone know a better way to solve this? I would be willing to change my database representation if it makes things easier.

Thanks for your advice.

[Thaddy]

Use DatetimeToTimestamp and store the timestamp in the database.
Decode with TimestampToDatetime. A TDatetime does not need to have a year value encoded, it can be just month-day.

[Hi im Pascal]

Would you by any chance have an example which encodes a given day and month as DateTime? I can't seem to find an appropriate function and DatetimeToTimestamp expects all values, and the year cannot be 0, so I would have to use e.g. year 1 as dummy value, so

Code: [Select]

EncodeDateTime(1, 5, 25, 0, 0, 0, 0)  // 1 means "no year known"
At which point I might as well just use

Code: [Select]

EncodeDate(1, 5, 25);
So I must be missing something?

[Thaddy]

See example in the manual: https://www.freepascal.org/docs-html/rtl/sysutils/strtodatetime.html
Then the example in the manual:  https://www.freepascal.org/docs-html/rtl/sysutils/datetimetotimestamp.html
That should get you going.

[Hi im Pascal]

If I understand you correctly, your suggestion is to convert day and month to a string, like '<day>-<month>' (or in my German locale, '<day>.<month>'), and then use StrToDateTime, and then make a timestamp from the DateTime? The problem is, just because I didn't specify a year, doesn't mean it "isn't required". The current year is simply used, which is not what I want.

So I see two options going forward:

- Simply use year 1 as dummy value "year not known", and try to apply the examples I found on the net to get upcoming birthdays
- Someone knows a way to select upcoming birthdays with separate day and month columns

[Thaddy]

Well the second conversion is not necessary since Sqlite3 can store it as string anyway, but you have to use a string format that is supported by sqlite3. See the Sqlite documentation:https://www.sqlite.org/datatype3.html
If you read that part you see what I mean with the two step approack.

[Hi im Pascal]

Sorry, I can't visualize what you are suggesting. I will do the following, because I think I should store the date as a single column after all, however, I must store a value for the year:

Year: 1 == "Not known" (implicit dummy value). If the user doesn't supply a year, 1 is used.

Code: [Select]

var
    birthday: TDate;
    year: Integer;
// ...
if CbHasYear.Checked then
    year := EdYear.Value
else
    year := 1;
birthday := EncodeDate(year, CbMonth.ItemIndex + 1, EdDay.Value);
// .... SQLQuery.SQL.Text uses placeholder ':birthday', which is stored as TEXT in SQLite3 database ('YYYY-mm-dd')
SQLQuery1.Params.ParamByName('birthday').AsString := FormatDateTime('YYYY-mm-dd', birthday);  // using FormatDateTime with explicit format guarantees the format always matches sqlite3
To check who has their birthday today, you can do:

Code: [Select]

// THIS DOES NOT WORK FOR FEB 29 !!! Don't use !!!
'SELECT * FROM person WHERE strftime(''%d-%m'', ''now'')=strftime(''%d-%m'', birthday);'

So I tried to figure out a proper query that works in SQLite3, but also the method of computing age and age in nDays by dividing by 365.25 etc. there are rounding errors and it never worked properly for all possible configurations. So in this particular case, I am going to select all entries and check them in Pascal Code:

Code: [Select]

var
  nDays: Integer;
  nRemainingDays: Integer;
  dayOfYearToday: Integer;
  dayOfYearBirthday: Integer;
begin
  nDays := EdNDays.Value;
  dayOfYearToday := DayOfTheYear(Now);

  SQLQuery1.SQL.Text := 'SELECT * FROM person;'; // optionally add check for NULL or '', if your config allows it
  SQLQuery1.Open;
  SQLQuery1.First;
  while not SQLQuery1.EOF do
    begin
      ShowMessage(SQLQuery1['first_name']);
      dayOfYearBirthday := DayOfTheYear(StrToDate(SQLQuery1['birthday']));
      nRemainingDays := dayOfYearBirthday - dayOfYearToday;
      if nRemainingDays < 0 then // if birthday already happened this year, we adjust the number of days
        nRemainingDays += DaysInYear(Now); // if Now is a leap year, it will be automatically correctly calculated, as opposed to += 365
      if nRemainingDays <= nDays then
ShowMessage('Birthday is coming up');
      SQLQuery1.Next;
    end;
end;
I know this might not be optimal but the way I see it, SQLite3 would have to look at every entry as well, so...

I claim this works for any combination of currentDate/birthday/upcoming in N days and it's clean (except for the fact I'm doing the selection in Pascal code maybe).

Obviously you would not mix the query-looping code with the computation code, but I kept it all together for demonstration.

Since SQLite3 expects "YYYY-mm-dd", and your locale may change the date separator in Pascal, I added the following as well (Pascal defaults to "dd-mm-YYYY"):

Code: [Select]

  DefaultSQLFormatSettings.DateSeparator := '-';
  DefaultFormatSettings.DateSeparator := '-';
  DefaultSQLFormatSettings.ShortDateFormat := 'y/m/d';
  DefaultFormatSettings.ShortDateFormat := 'y/m/d';
I was too lazy to test which of the two you actually need to set, lol.

Another option would be storing the JulianDay as REAL, but I like the readability of using TEXT in SQLite3 for dates.

For completeness, here would be a possible implementation as standalone function (you could name it something more generic, like 'DaysUntilAnnualDay' or 'DaysUntilDayOfAYear', since it will compute the amount of days until any dayOfAYear). Additionally, one might want to pass the reference date as an argument (currently hard-coded as current date).

Code: [Select]

function DaysUntilBirthday(const birthDate: TDate): Integer;
begin
  result := DayOfTheYear(birthDate) - DayOfTheYear(Now);
  // Add correction if birthday already was in this year:
  if result < 0 then
    result += DaysInYear(Now);
end;

Update: I figured out how to implement the same logic in a SQLite3 query:

Code: [Select]

SELECT
    *,
    strftime('%j', birthday) - strftime('%j', 'now') AS days_remaining
FROM
    person
WHERE :n_days >= CASE
    WHEN days_remaining >= 0 THEN days_remaining
    ELSE days_remaining + strftime('%j', strftime('%Y-12-31', 'now'))
    END
;
So I guess now one could decide which way to do it performs better... I have no idea.

Update 2:
I now realized that StrToDate can accept "one to three" numbers separated by date separator, so it will do both 'YYYY-mm-dd' or 'mm-dd', thus I could also just store either 'YYYY-mm-dd' or 'mm-dd' inside the database. However, I'm not sure what sort of problems that might create down the line.