SQLite query to sort upcoming birthdays based on current date

Question

My date-column is of type integer:

myDate.getTime() //long saved in db

During my research on how to best sort upcoming birthdays, I found this: MySQL query to sort upcoming birthdays based on current date

I've tried to translate the solution into SQLite syntax, but with no luck. Anyone want to push me in the right direction?

I'm not even able to get for instance the month of a timestamp:

strftime('%m', birthday) //unexpected value (dec = 1, jan = 1, jan = 6)
strftime('%m', 'now') // as expected (mar = 3)

Any help appreciated.

UPDATE Ok, now I have really created a mess:

cur = db.rawQuery("SELECT "
                        + "(strftime('%Y','now') - strftime('%Y',datetime(("+BIRTHDATE+"/1000), 'unixepoch', 'localtime'))) AS age,"
                        + "date((" + BIRTHDATE + "/1000),'+' || age || ' year') AS currbirthday,"
                        + "date((" + BIRTHDATE + "/1000),'+' || age+1 || ' year') AS nextbirthday"
                    + " FROM " + TABLE
                    + " ORDER BY CASE" 
                        + " WHEN currbirthday < date('now') THEN nextbirthday"
                        + " ELSE  currbirthday" 
                    + " END", null);

Error: "No such column age"

Ok, well, I tried putting the entire expression calculating "age" inside the concat, but the result is strange(age stores the correct value):

Log.d("bdate", contact.getFirstname() + ": currbday=" + cur.getString(1));
Log.d("bdate", contact.getFirstname() + ": nextbday=" + cur.getString(2));

Samantha: currbday=-5705--6--29

Samantha: nextbday=-5704--6--29

Any suggestions?

What is the *meaning* of that integer value? SQLite interprets numbers as Julian date numbers, or as seconds with the `unixepoch` modifier. — CL., Mar 26 '13 at 07:45
The meaning of the integer value is unixtime. Restoring the date: Date myDate = new Date(longFromDb); — msfoster, Mar 26 '13 at 23:52
My problem was that I stored milliseconds, which really is not unixtime. strftime('%m', date(birthday/1000)) gives me the correct output. — msfoster, Mar 27 '13 at 01:38

score 1 · Accepted Answer · answered Apr 02 '13 at 09:41

1

In case anyone wonders, this is my solution:

cur = db.rawQuery("SELECT "
                        + "date(strftime('%Y', 'now','localtime')||strftime('-%m-%d', datetime(("+BIRTHDATE+"/1000), 'unixepoch', 'localtime'))) as currbirthday,"
                        + "date(strftime('%Y', 'now','localtime')||strftime('-%m-%d', datetime(("+BIRTHDATE+"/1000), 'unixepoch', 'localtime')),'+1 year') as nextbirthday"
                    + " FROM " + TABLE
                    + " ORDER BY CASE" 
                        + " WHEN currbirthday < date('now') THEN nextbirthday"
                        + " ELSE  currbirthday" 
                    + " END", null);

answered Apr 02 '13 at 09:41

msfoster

2,474
1
17
19

What did you save the date as? Unix time stamps? How did you add the dates before todays date? Please help!! – Skynet Dec 11 '13 at 12:51
The date column is of type integer. Yes, unix time stamps. Regarding the question "how did you add the dates.." I dont know what you are asking. – msfoster Dec 15 '13 at 20:06
I meant to the Query, I had put up a question here got quite a few elegant solutions. http://stackoverflow.com/questions/20320925/ordering-of-dates-and-retrieving-results-based-on-current-date-birth-day-list – Skynet Dec 16 '13 at 11:04

score 0 · Answer 2 · answered Apr 07 '19 at 10:39

I found a solution with a different approach, which works correctly for any corner cases I believe. It computes the number of remaining days based on the day of the year, and corrects the result if the birthday falls into the next year:

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
;

SQLite query to sort upcoming birthdays based on current date

2 Answers2