[SOLVED] SELECT upcoming birthdays in SQLite3
lazarus / 16-Databases

----------------------------------------------------------------------
Hi im Pascal · 2019-04-06 12:26

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 · 2019-04-06 13:01

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 · 2019-04-06 13:49

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 · 2019-04-06 14:08

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 · 2019-04-06 14:31

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 · 2019-04-06 14:45

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 · 2019-04-06 18:26

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.