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

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.

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.

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


Cloud Firestore Database for Web Application

Cloud Firestore is hot new document database in the cloud for mobile and web app for saving your data to the cloud.I want to create a little web app where we can store an Inspirational quote.My web app here consists of a text field, where I can enter my inspirational quote.We’ve got a save button, where we’re going to save whatever inspiring message our user has entered to the cloud.Later on, we’ll load whatever data our user has saved to the cloud and display it at H1 tag, so we can see the latest inspirational quote that we’ve entered.

Getting Started with Cloud Firestore on Web

Let’s take a look at what I’ve got set up.So here’s index.html page, where I’ve got all my elements created.I’m loading the Firebase library.

Then, over here is my app.js file, where I’m configuring my Firebase project with the values that I got from the Firebase Console.

I’ve already created IDs for my header, my text field, and my button.So first things  I’m going to jump into my app.js file and create a few variables to hold references to these elements.I’ll do that just by calling querySelector.

Next up, I’m going to create a click event listener for the save button.Now I’ll grab the text that I want to save from my inputTextField.value and let’s log it to confirm.Let’s log it to confirm we have some actual data.

Initialize Cloud Firestore

Initialize Cloud Firestore through Firebase.

Now, I just need to save it to the cloud and to do that, I need to specify in which document I want to put this data.We do that through something known as a document reference.

I’ll be using the same reference a few times throughout this project.So I’m actually going to define it top of the script.
Firestore Database Pathyou’re always going to be alternating between collections and documents.

Now, I’ve specified my document, let’s go back to my event listener, where I’m going to call set on this document reference. Now, this will take in a JavaScript object to represent the data that we want to save for this document inspirationalQuote as textToSave that’s all I really need.This will replace my current document if it exists, and it will create it if it doesn’t.It also conveniently creates the samples collection too.So I don’t need to worry about whether or not that exists.

Now, the set function, as with most Firestore functions, returns a promise, which means it’s pretty easy to note when this call is I’ll attach a then callback at the end here, and print out a little success message, and come to think of it, I’ll add a catch here to print out any errors.

Error: Missing or insufficient permissions.

Cloud Firestore implementation contains a set of security rules that determine whether or not a certain action is permitted.The proper solution would be to add some sign-in using Firebase Auth and then create some proper, well thought out security rules, base on what information. I’m willing to share to each individual user.So I’m going to do a bit of a hack here and make anything in my samples collection open to the public.

Cloud Firestore Security Rules

So let’s head on over to the Firebase Console, make sure you’ve got your project selected, and I’m in the database section and then here, I’ll make sure cloud Firestore is selected for my list of database options.And then, I will click on the rule tab.Next, I’m going to add this here to allow reading and writing that’s part of my samples collection.

Time to load up our page and give this a try.

Cloud Firestore For web

I can verify this by going back to the Firebase console.We’ll select the Data tab to view our data.We’ll select our samples collection and sure enough, looks like we’ve saved our thought.

Get Data with Cloud Firestore

It’s great that we can see this data in the Firebase Console, we need to show this important information to our users too.So next up, let’s learn how to grab this data from the cloud and put it into our H1 tag.

Now, like the Realtime Database, Cloud FIrestore lets me listen to changes in the database and update my app in real-time.

Now, getting this function to work is actually quite easy.I’m going to take that same document reference we created earlier and, instead of calling set on it, I’m just going to call get instead.This returns a promise.So I can attach a then callback to it, which will run when this get call is complete.Note that, this takes in a documented snapshot that I’m just calling doc here.A document snapshot is basically an object that represents your document. You can find out its ID, read some metadata about it, make sure the underlying document it represents really exists, and more importantly, grab the data that it contains by calling data on it.So we’ll see if this snapshot exists and if it does, we will call data to extract the contents of that document as an object.Then, I can set the text of our big output header to my data.inspirationalQuote.And as long as we’re here, let’s add a catch at the end to catch any error that might come up.

So let’s run this.I can still save our latest quotes to the world. Now, I’ll click the Load button to load it and there it is on our page.
Cloud Firestore Web Get Data

Get Realtime Updates with Cloud Firestore

So this is great.We’ve got our data successfully saved and loaded from cloud Firestore.But what if you are interested in getting your data in real time? What if this explicit fetching seems quaint and old-fashioned to you? Well, let’s show you how to get your data in real-time as well.

Basically, the process is going to work nearly the same.Let’s create a function called getRealtimeUpdates and in there,I’m going to bring up my document reference.But then instead of calling get, I’m going to use the onSnapshot.This will fire the first time I call it.But then, it will also fire any time thereafter when my data changes.Now, this call takes in a callback function, which accepts the document snapshot as an argument, essentially just like my get call.So in fact, I’m just to copy the exact same code for my get call into here to update my label.Well, that’s it.We’ll call getRealtimeUpdates at the bottom of our script.That should be all we need.Here, let’s reload the page.

Now you can see that my text automatically gets updated with current save Quote the value that was in the cloud previously.I hit save and you can see that my label gets updated automatically without my having to even touch that load button, that was fast.In fact, maybe too fast, right? It looks like my label got updated before that data was even saved.How’d that happen?

Cloud Firestore for web real-time update

Well, what’s happening is that cloud Firestore is making my app run as speedy as possible, by notifying me of changes as if they had happened on the server.But in the meantime, It’s still going ahead and updating that data remotely in the background.

Download this project from GitHub


Related Post

Firestore Document Database Data Model.


ConstraintLayout 1.1.0: Circular Positioning

Android published ConstraintLayout 1.1.0 on the google maven repository. One of the more interesting additions to this release is Circular Positioning. Circular positioning allows you to constrain a widget center relative to another widget center, at an angle and a distance. This allows you to position a widget on a circle.

ConstraintLayout Circular constraints

Add ConstraintLayout to your project

To use ConstraintLayout in your project, proceed as follows:

1.Ensure you have the repository declared in your project-level build.gradle file:

2.Add the library as a dependency in the same build.gradle file:

Example Circular positioning

The following attributes can be used:

  • layout_constraintCircle : references another widget id.
  • layout_constraintCircleRadius: the distance to the other widget center
  • layout_constraintCircleAngle : which angle the widget should be at (in degrees, from 0 to 360).


Related Post

New features in Constraint Layout 1.1.0


Firestore Security With Firebase Authentication

Cloud Firestore Security Rules are used to determine who has read and write access to collections and documents stored in Cloud Firestore, as well as how documents are structured and what fields and values they contain.

In this tutorial, We will learn how to secure your data using Firebase Authentication and Cloud Firestore Security Rules to handle serverless authentication, authorization, and data validation.

To set up and deploy your first set of rules, open the Rules tab in the Cloud Firestore section of the Firebase console.

Cloud Firestore populates a default rule, denying read and write access to everyone on all data:

Match Path

Rules match paths representing a document or collection in Cloud Firestore. Rules may match one or more paths and more than one rule can match the document name in a given request.

The basic type of rule is the allow rule, which allows read and write operations if an optionally specified condition is met.

In most cases, you’ll want to write rules that apply to any document within a collection, not just a specific one. In these situations, you can use wildcards to specify any element within a path. A wildcard is created by adding curly brackets around a string.

Apply rules to multiple documents or collections with wildcards. Wildcards represent the document or collection ID in the match path as a string between curly brackets. Add =** to the end of your wildcard string to apply the wildcard to all documents and subcollections in the path.

The request variable

The request variable is a map that contains information about the request that’s coming in from the client. You can view the reference documentation for a full list of fields, but two fields you’ll frequently be working with are the auth field, which contains information from Firebase Authentication about the currently signed-in user, and the resource field, which contains information about the document being submitted.

Common use cases for the request object include:

  • Confirming that request.auth is not null to ensure that a user is signed in
  • Using the value of request.auth.uid to get the user’s ID, as verified by Firebase Authentication
  • Using to ensure the value being submitted for a document write meets a particular format. This is most common way to perform data validation within your database.

For example

Resource  vs Request.resource variable

The resource variable is similar to the request.resource variable, but it refers to the document that already exists in the database. In the case of a read operation, this is the document that is about to be read, while in a write operation, this is the old document that is about to be updated or altered.

Both the resource and request.resource objects are maps. Each contains a __name__ property indicating the document name, and a data property where all user-defined properties are declared. By accessing keys within the data property of these maps, you can get the values of the various fields belonging to the document being referenced. For example, is mapped to the value of the kesselRun field within the document represented by the resource variable.

Examples using the resource object.

Get Document From Database

Using the resource variable will let you evaluate the current document that is about to be accessed, but sometimes you will want to evaluate other documents in other parts of the database.
You can do so by calling the get() function with the document path in the database. This will retrieve the current document, which will allow you to access custom fields through the data property, just like the resource object.
One very common use of this function is to evaluate an access control list and determine if the userID of the current user is part of that list.
The exists() function can also be used to determine if a document already exists.

When entering a path into the exists or get function, you do not include quotes. If you want to include a variable in the path name, you should enclose the variable in parenthesis and preface it with a dollar sign.

For example:

Add Firestore Security Rules with Android Example

In previous tutorial we build a ‘MyThought’ application using Firestore which use for saving thought in Firestore database.In this tutorial we use same project to add firebase security rule.

Add Firebase Authentication

In the Authentication tab of the Firebase console go to the Sign-in Method page and enable ‘Google’
Firebase Enable Login With Google

Add Dependency

Add following dependency in your app.gradle file.

Add FirebaseUI

FirebaseUI has separate modules for using Firebase Realtime Database, Cloud Firestore, Firebase Auth, and Cloud Storage. To get started, see the individual instructions for each module.

Security Rules

Add the following security rules to your project in the: rules tab:

  • Rule 1: Only authorized users can create, read them.
  • Rule 2: Only the user who Creates the Thought can delete it. Thought can never be updated.


Download this project from GitHub


Related Post

Firestore Document Database Data Model.
Firebase Firestore Database for Android Application.


Firestore Document Database Data Model

When we come across the document databases we have to retrain our brain. We have to think slightly differently when we do this otherwise we may run into some challenges.

If you think in the world of relational data you always had a model first. You thought about your tables and thought about what went in and you built your schema.

In a document database that doesn’t mean we don’t model .we’re still modeling as just as in a relational database. It’s a little bit different where the relational data engine typically enforcing your schema or in a document database engine typically it’s the developer that’s enforcing that schema.So you build your models it’s usually built from some use case that you’re working on and driven from your code on down to the database.

If we take a look at a more realistic data modelling example a person and a person has contact details a contact detail has a type and we split these up into separate tables, we use foreign keys to kind of you know to break this up and reassemble the object and that ‘s what it would look like in a relational world.

Relational database

A document world we take a document called the person and we have attributes and on that, we may actually go and embed in and address a contact detail or multiple contact details and this is what the same thing would look like in a JSON document.

Document Database

Why we need  Document Database?

The document database is expressive, efficient, and flexible. Create shallow queries to retrieve data at the document level without needing to retrieve the entire collection or any nested subcollections.

Firestore Document Database

Cloud Firestore is a flexible, scalable database for APP.It keeps your data in sync across client apps through realtime listeners and offers offline support for mobile and web so you can build responsive apps that work regardless of network latency or Internet connectivity. Cloud Firestore also offers seamless integration with other Firebase and Google Cloud Platform products, including Cloud Functions.

How does it work?

Cloud Firestore is a  NoSQL database that your apps can access directly via native SDKs.

Firestore Document Database Structure

Following Cloud Firestore’s NoSQL data model, you store data in documents that contain fields mapping to values. These documents are stored in collections, which are containers for your documents that you can use to organize your data and build queries. Documents support many different data types, from simple strings and numbers, to complex, nested objects. You can also create subcollections within documents and build hierarchical data structures that scale as your database grows.

Add, sorting, filtering, and limits to your queries or cursors to paginate your results. To keep data in your apps current, without retrieving your entire database each time an update happens, add realtime listeners. Adding realtime listeners to your app notifies you with a data snapshot whenever the data your client apps are listening to changes, retrieving only the new changes.

Firestore Data Model

Firestore is a document database that means it kind of stores your data in a big tree like structure kind of like the original real-time database but everything is placed into documents and collections.


You can think of a document is something kind of like a map it’s got key-value pairs which is the Firestone folks like to refer to as fields and the values of these things can be any number of things from strings to numbers to binary values to little small maps that are kind of JSON looking objects.

Firestore Document


The collection is basically well collections of documents.

Firestore Collection

There are a few rules around the use of this thing.

Rule 1: Collections can only contain documents nothing else no collections of strings or binary or anything else.

Rule 2: Documents can’t contain other documents but they can point to sub collections so it’s very common to see a collection containing a bunch documents which then point to sub-collections that contain other documents and so on in a user workout app for instance.

Firestore Document Database Example

I might have a users collection and this collection would contain a document for each user and then each of those documents might point to a workout subcollection that in turn contains a document for each different type of workout that this user is performed and then maybe each one of these has a history sub-collection that keeps track of every time the user performed one of these workouts may be along with like some average heart rate data or some other stuff like that.

Rule 3: The root of your database can only consist of the collection now in a normal production app this is going to feel pretty natural.

Firestore Rule #3


If you’re coming from real-time database land this kind of deep nesting might be giving you heart palpitations but don’t worry this kind of data structure is completely normal in the cloud Firestore world where queries are shallow meaning that when you grab data from a document you’ll garb just that document not any of the documents contained in any of the sub-collections below which nice.


Related Post

Firebase Firestore Database for Android Application.
Cloud Firestore Database for Web Application.
Firestore Security With Firebase Authentication

Firebase Firestore Database for Android Application

You can store your data in the cloud using Cloud Firestore. You can sync it across all your devices or share them with multiple users. It comes with robust client libraries, full support for offline mode. Your app will continue to work just fine whether you’re connected or not. A comprehensive set of security rules to help you manage access and an easy-to-use data browsing tool. It also lets you structure your data in ways that make sense to you thanks to its powerful querying and fetching capabilities.

Cloud Firestore works in real time, automatically fetching changes from your database as they happen, or you can request and fetch data manually.It’s completely up to you.

Getting Started with Cloud Firestore on Android

So I’m working a little app here that lets me store a thought.

My App consists of an EditText where I can enter my thought and then the second one here where I can enter the publisher’s name and  then I have Publish button where we’re gonna save whatever thought our user has entered into the cloud so later we’ll load whatever data our users saved to the cloud and display it with a big Listview or something

Enable Firestore in Firebase

In order access to Firestore you need to enable Firestore in Firebase Console.

Enable Firestore

Add Firebase to your Android project

Create a Firebase project in the firebase console and configure the Google services plug-in with JSON file.

Add Firebase to your Android project


Firestore is a Document Database

Firestore store your data in a big tree like structure kind of like the original real-time database but everything is placed into documents and collections. For more about a document database, read this post.
Firestore DocumentAdd Firestore Dependency to your app

Let’s start building so first thing you need to make sure you have Firestore installed. Into app.gradle file add Firestore.

In main activity here I’m gonna head on down to my publishThought call.

Cloud Firestore also supports writing your own Java objects with custom classes. Cloud Firestore will internally convert the objects to supported data types.Each custom class must have a public constructor that takes no arguments. In addition, the class must include a public getter for each property.

Now you just need to save this to the cloud and to do that you need to specify in which document you want to put this data so you do that through an object known as a DocumentReference let’s initialized it up.

I pass along a slash-separated string as our path so in our case, the path would be “thought/lifeThought

Just remember that in your path you’re always going to be alternating between collections documents collection document and so on.Firestore Database Path

Now that I’ve specified my document. In my publishThought() method where I’m going to call set on the document reference and that will take in the data. I created above as the contents of my document this call will replace my current document if it exists and created if it doesn’t it also conveniently create the sample data collection too so I don’t need to worry about that not existing now I’m going to chain on an addOnSuccessListener like so where we can print a debug message. And then we can add an addOnFailureListener listener where we can print out any error messages.

For those of you who are all about fewer listener in your call, you could also handle this with a single OnCompleteListener like and then just check in here to see if your task is successful either way works and honestly it’s just really up to you which method you prefer.

Error on Document Writing PERMISSION_DENIED: Missing or insufficient permissions.

Just like the real-time database cloud, Firestore implementation contains a set of security rules that determine whether or not a certain action will be permitted.By default they’re set up that nobody can read or write to the database.I’m willing to share with each particular user.So I’m just going to do a bit a hack here and make my sample data documents open to the public.

Set Firestore rules

I’ll select my project go to database selection and then make sure I select cloud Firestore for my list of database options then I will click the rules tab and then I’m going to add these lines here to allows reading and writing to anything that’s part of my sample data collection now this a pretty terrible idea from a security perspective but at least I’ve contained the damage to just what’s in my sample data collection so I’ll publish that and we are done.

So I’m going to start up my app, and I am going to enter thought in the text field and I hit publish button.

Firestore Database on android

I can verify this by going back to the Firebase console we’ll select the data tab and then I can look at our data collection find our document and sure enough look likes we’ve saved our inspiring thought to the world.

Firebase Cloud Firestore Database

Get Data with Cloud Firestore

We need to show our users this inspirational thought by grabbing that data from the cloud and populating in some kind of list view with it now like the real-time database cloud Firestore lets me listen to changes in the database and update my app in real time.

So let’s create a Thought List I ’m gonna add a Recyclerview in the middle of my screen that has a TextView where I can display the thought and publisher name.

I already have my document reference.

Perform Simple Query in Cloud Firestore

Cloud Firestore provides powerful query functionality for specifying which documents you want to retrieve from a collection. These queries can also be used with either get() or addSnapshotListener().

Get Realtime Updates with Cloud Firestore

You can listen to a document with the onSnapshot() method. An initial call using the callback you provide creates a document snapshot immediately with the current contents of the single document. Then, each time the contents change, another call updates the document snapshot.

When I publish thought now my callback is getting called twice once for the change in my local cache and then once on the server.This is because of an important feature called latency compensation.

Download this project from GitHub


Related Post

Firestore Document Database Data Model.
Firestore Security With Firebase Authentication