For our application, we will be creating two tables, one each for our temperature and humidity readings. The structure will be similar for both of them:
- The createdAt column to store the date and time of creation
- The value column to store the actual value read by the sensor
When creating the table, it's useful to know SQLites datatypes. This will not be a hard task since there are only five:
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB
In most other relational databases, there is a data type for datetime, which we would have used for our createdAt column. In the case of SQLite, the datetime is represented either as a string in the ISO format (YYYY-MM-DD HH:MM:SS), or as an integer in Unix time (the time elapsed since 1970-01-01 00:00:00 UTC).
From this, we decide the types of these:
- createdAt as TEXT (ISO formatted datetime)
- value as REAL...