SQLITE Datetime Query

SQLITE does not have date datatype fields. The date information is either entered as text or numbers. Querying and getting the information is a bit tricky.

If it is a text, the date is stored as YYYY-MM-DD HH:MM:SS.SSSS.

If it is a Julian date, the data is stored as REAL, a float type.

If it is a Unix timestamp, it is stored as INTEGER.

Normally we want to get the data formatted according to local date time format. Singapore format is DD/MM/YYYY.  To get the data we use SQLITE function strftime.

If it is a text or Julian Date, we use strftime('%d/%m%Y %H:%M:%S',fieldname).

If it is Unix datetime, we use strftime('%d/%m%Y %H:%M:%S',fieldname,unixepoch).

The above is both applicable in the SELECT and WHERE query. You can use datetime('now') when adding/updating to add current date time.

Often we need to find the difference between two datetime. Using the above method, the difference can be found for text and Julian date. Use strftime and %S, %M, or $H or even %d or %M to find  the difference in the respective area. For Unix timestamp, since it is already in seconds, it is simply one minus the other as they are already integer values.

Now getting an offset of a date time is also not that difficult. Use datetime(fieldname, '+' || offset || ' minutes') to add minutes (can be any part of a datetime).

Conclusion, it is a bit troublesome but manipulating datetime in SQLITE is not that difficult.