Get days until future event in SQLite

Question

Trying to get several things from a SQLite table with names and dates of birth and am having trouble getting the # of days until a person's next birthday. Dates are stored as SQLite's TEXT data type in format '%Y-%m-%d 00:00:00'.

I can get age:

SELECT ((strftime('%s', 'now') - strftime('%s', dob)) / 31536000) AS age

I like this solution for showing the closest birthdays first:

ORDER BY SUBSTR(date('now'), 6) > SUBSTR(dob, 6), SUBSTR(dob, 6) ASC

But I'm breaking my brain over getting the days until the next birthday. My latest attempt is taking the julianday of the substring of the day and month from dob and concatenate it with the current year to compare against julianday() and put in conditionals to take the year change into account, but I haven't worked that out yet and I'm hoping someone has a more elegant solution.

How is this not a duplicate of http://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite — Dan D., Nov 30 '14 at 18:40
The date to compare against (the person's next birthday) can be in this or the next year, and the difference should only be calculated versus the next birthday. My current attempt takes an answer from the quoted SO question plus a conditional plus a date format change, but I'm hoping for something less hideous. — Wilco van Esch, Nov 30 '14 at 19:38
Are you really using `%d-%m-%Y` and not `%Y-%m-%d`? Because the former would not work. — CL., Nov 30 '14 at 21:20

score 0 · Accepted Answer · answered Dec 02 '14 at 18:01

Have made my hideous solution work, so here it is:

SELECT
    CASE WHEN 
        julianday((SUBSTR(date('now'), 1, 5) || SUBSTR(dob, 6, 5))) > julianday('now')
    THEN CAST(ROUND(
        julianday((SUBSTR(date('now'), 1, 5) || SUBSTR(dob, 6, 5))) - julianday('now'), 0) AS INTEGER)
    ELSE CAST(ROUND((
        julianday(SUBSTR(date('now'), 1, 5) || '12-31') - julianday('now')) + (
        julianday(SUBSTR(date('now'), 1, 5) || SUBSTR(dob, 6, 5)) - julianday(SUBSTR(date('now'), 1, 5) || '01-01')), 0) AS INTEGER) 
    END 
AS dub FROM person;

Will only have to put in another conditional to improve the rounding.

Get days until future event in SQLite

1 Answers1