One of the most interesting and confusing data types that SQLite not supports is Date and Time. I see more questions in online public discussion forums about this type than any other. In this article, I shed light on some very confusing issues regarding select queries using Date.

Date and Time Datatype in SQLite


SQLite does not have a storage class for storing dates and/or times. Instead, the built-in Date and Time Functions of SQLite are capable of storing dates and times as TEXTREAL, or INTEGER values:

  • TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Using type converters


Sometimes, your app needs to use a custom data type, like DateTime whose value you would like to store in a single database column. To add this kind of support for custom types, you provide a TypeConverter, which converts a custom class to and from a known type that Room can persist. For example, if we want to persist instances of Date, we can write the following TypeConverter to store the equivalent Text in the database:

public class TimestampConverter {
    static DateFormat df = new SimpleDateFormat(Constants.TIME_STAMP_FORMAT);
    @TypeConverter
    public static Date fromTimestamp(String value) {
        if (value != null) {
            try {
                return df.parse(value);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return null;
        } else {
            return null;
        }
    }

The preceding example defines 2 functions, one that converts a Date object to a String object and another that performs the inverse conversion, from String to Date Next, you add the @TypeConverters annotation to the Field of class so that Room can use the converter that you’ve defined for each Row in the entity.

//Table names in SQLite are case insensitive.
@Entity(indices = {@Index(value = "first_name")})
public class User {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "user_id")
    public long userId;
    @ColumnInfo(name = "first_name")
    public String firstName;
    @ColumnInfo(name = "created_date")
    @TypeConverters({TimestampConverter.class})
    public Date createDate;
    @ColumnInfo(name = "date_of_birth")
    @TypeConverters({DateConverter.class})
    public Date dob;
    @Embedded
    public Address address;
}

Note:You can also limit the @TypeConverters to different scopes, including individual entities, DAOs, and DAO methods.

1.SQLite Query to select data between two dates


I have a start_date and end_date.I want to get the list of dates in between these two dates. Put those two dates between single quotes like.

@Query("SELECT * FROM User WHERE date_of_birth BETWEEN date(:from) AND date(:to)")
LiveData<List<User>> fetchUserBetweenDate(String from, String to);

2.SQLite Query to compare date


@Query("select * from user where date_of_birth=Date(:date)")
LiveData<List<User>> fetchUserByUserDOB(String date);

3.SQLite Query to group by Year


@Query("SELECT strftime('%Y', date_of_birth) as year,count(date_of_birth) as count FROM User GROUP BY date_of_birth")
LiveData<List<GroupByUtil>> groupByUserDOBYear();

4.SQLite Select data for a specific year


@Query("SELECT * FROM User WHERE strftime('%Y', date_of_birth) = :year")
LiveData<List<User>> fetchUserByDOBYear(String year);

5.SQLite Query to get Last month’s Data


@Query("select * from User where created_date>=datetime('now', 'now', '-30 day')")
LiveData<List<User>> fetchUserByDuration(String duration);

6.SQLite Query to Order by Date


@Query("select * from User ORDER BY date(date_of_birth) asc")
LiveData<List<User>> fetchUserOrderByDOB();

7.SQLite Query to calculate age from birth date


 @Query("SELECT *, cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', date_of_birth) as int) as age FROM User")
 LiveData<List<UserWithAge>> fetchUserWithAge();

Download this project from GitHub.