How to use DateTime datatype in SQLite Using Room

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 set aside 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:

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. Since Room already knows how to persist String objects, it can use this converter to persist values of type Date.

Next, you add the @TypeConverters annotation to the Field of class class so that Room can use the converter that you’ve defined for each Row in entity.

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 date


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

2.SQLite Query to compare date


3.SQLite Query to group by Year


4.SQLite Select data for a specific year


5.SQLite Query to get Last month Data


6.SQLite Query to Order by Date


7.SQLite Query to calculate age from birth date


 

Download this project from GitHub.

 

Related Post

Room Persistence Library

Room: Database Relationships

Room database Migrating

 

Leave a Reply

Your email address will not be published. Required fields are marked *