tsrange or daterange for sqlite?

Question

Is there a datatype equivalent to tsrange or daterange (or actually any of the range types available in PostgreSQL) in e.g. sqlite?

If not, what is the best way to work around it?

Answer 1

Sorry, there only 5 datatypes in SQLite: https://www.sqlite.org/datatype3.html

NULL, INTEGER, REAL, TEXT, and BLOB. And NUMERIC.

As @ a_horse_with_no_name mentioned, "You would typically use two columns of that type that store the start and end value of the range". This does make it a bit tricky if you want to do database calculations on intervals. But this resources might be found as an run-time loadable extension.

You would typically use two columns of that type that store the start and end value of the range. – a_horse_with_no_name 42 mins ago

Be cautious; SQLite is quite forgiving in what it accepts for each data type:

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

This means that you can throw text at integer fields; if the text is an integer, that's fine. If it's not, that's fine. It will be stored and returned to you when retrieved. The difference being, if it could be converted to an integer, it will be and you will be returned an integer. If it could not be converted, you will get returned a text string. This could make programming with SQLite databases interesting.