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 query 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 chose 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 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.