When I first began working with SQLite I was surprised to learn that there was no
DATETIME datatype equivalent. Instead, time-based data must be stored as
REAL types. But, how can you logically order a
TEXT-based date? How can you move seamlessly between Moment.js on the clientside and these primitive types in SQLite? Read on!
If you’d like to store a date, like June 27th 2019, I would recommend using the
TEXT datatype and providing it with date strings in the following format:
YYYY-MM-DD. There are other formats (look for “Time Strings”) you can use when you need to store time of day as well, such as
You can create a table with a column of this type during app initialization:
The table will be created if it does not already exist.
Later on, when you’d like to write to this table, craft an INSERT statement that formats your
Moment object into a string matching one of the supported SQLite time string formats:
To read your date values back from the DB, craft a query that returns the column as a string and create a new
Moment from it’s value:
There are a couple of things happening in the code above:
- Note the
date(transactionDate)function call. This enables us to
ORDER BYbased on the date, amongst other date-related things. In this example the transactions will be ordered oldest to newest.
- We can conditionally create a new
Momentinstance if the row’s
transactionDateis defined. Otherwise, it will be
You now have a date value available for use on the app’s client side, in the super flexible and versatile
I’ve written a couple of other posts on the topic of SQLite and React Native. Check out: