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 TEXT, REAL, 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();