How to find birthdays ranging between two dates using sqlite?

Question

I am using greendao or sqlite and my model (User) has a DOB column which stores the birthdate of the user. How can I find the users which have birthday between a range of dates?

I am using following query in mysql but cannot find a way to do this using sqlite:

SELECT *
FROM user
WHERE datediff( MAKEDATE( YEAR(NOW()) , DAYOFYEAR(dob) ) , NOW())
    BETWEEN 0 AND 7
OR datediff( MAKEDATE( YEAR(NOW())+1 , DAYOFYEAR(dob) ) , NOW())
    BETWEEN 0 AND 7
OR datediff( MAKEDATE( YEAR(NOW())-1, DAYOFYEAR(dob) ), NOW())
    BETWEEN 0 AND 7;

you should read the greendao documentation and lean how date is stored with this library (AFAIK it using sqlite and there is no date/datetime data format) ... if there is no default metod the use EPOCH time format and now it should be easy as comparing two ints/longs ... — Selvin, Aug 26 '14 at 15:10
greendao gives an interface to handle dates in java format. For comparision of dates, it is very easy to handle. But when it comes to applying some operations on dates while comparision in SQL query, I don't understand how to approach with greendao. — Anuj, Aug 27 '14 at 04:13

score 1 · Answer 1 · answered Aug 28 '14 at 09:40

Like SQLite documentation says, there is no Date format in SQLite (http://www.sqlite.org/datatype3.html).

So, greenDAO uses timestamp to save Dates. Maybe you are looking for this kind of query:

Date startRangeDate = new Date(); 
Date endRangeDate = new Date(); 
DeveloperDao targetDao = daoSession.getDeveloperDao();
QueryBuilder<Developer> qb = targetDao.queryBuilder();
qb.where(Properties.Birthday.between(startRangeDate.getTime(), endRangeDate.getTime()));

score 0 · Answer 2 · answered Aug 28 '14 at 10:27

After lot of trying, I finally came up with this solution. I am posting this so it may help somebody in future:

public static List<Customer> getThisWeekCustomersByBirthday(Context context,int limit, int offset) {
    String whereQuery = " julianday(strftime('%Y','now','localtime')||strftime('-%m-%d',datetime(dob/1000, 'unixepoch'),'localtime')) between julianday(strftime('%Y-%m-%d','now','weekday 0','-7 days','localtime')) and julianday(strftime('%Y-%m-%d','now','weekday 0','localtime')) "
            + "OR julianday(strftime('%Y','now','+1 year','localtime')||strftime('-%m-%d',datetime(dob/1000, 'unixepoch'),'localtime')) between julianday(strftime('%Y-%m-%d','now','weekday 0','-7 days','localtime')) and julianday(strftime('%Y-%m-%d','now','weekday 0','localtime')) "
            + "OR julianday(strftime('%Y','now','-1 year','localtime')||strftime('-%m-%d',datetime(dob/1000, 'unixepoch'),'localtime')) between julianday(strftime('%Y-%m-%d','now','weekday 0','-7 days','localtime')) and julianday(strftime('%Y-%m-%d','now','weekday 0','localtime')) ";
    Query query = getCustomerDao(context).queryBuilder().where(
            new WhereCondition.StringCondition(whereQuery)).limit(limit).offset(offset).build();

    return query.list();
}

How to find birthdays ranging between two dates using sqlite?

2 Answers2