Learned this from a friend. The types are null, integer, real, text, and blob. My friend describes them thusly:
- Null stores nothing, but like, actively nothing, as opposed to the absence of a thing.
- Integer is a signed integer, up to 8 bytes.
- Real is always an 8-byte float.
- Text is an arbitrary-length UTF-8 or UTF-16.
- Blob is an arbitrary-length anything-else. But I hope you remembered what you put there. Because it sure isn’t gonna tell you. Oh, and it doesn’t have strong typing, so if you ask for it back as an integer, it’ll quite happily give you it back as an integer, especially if that doesn’t make sense!
Well it’s kinda in the name SQLite
I find the lack of dates the most annoying
Lol I just make sure they’re formatted as ISO dates or timestamps and store them as text, you can do good enough date operations on them
This. Lexical sort works on dates stored as text in ISO format. For times, better standardize in storing in UTC and converting to and from local time. Although storage will suffer as this consumes way more data than storing as number.
because I’m objectively a terrible programmer.
This comment made me chuckle at 6am. I was expecting some sort of profound reason to store them at text.
I’ve gotten used to storing dates as numbers and then just using some sort of library to convert them back to human readable text.
Dates are much easier to work with when they are numbers.
Personally, I would probably just store them as text, because I’m objectively a terrible programmer.
I don’t know man, I’d far prefer storing a string and have whatever date library I’m using figure it out than have to deal with whatever the database thinks about dates and timestamps
IMO this is fine. The whole point of sqlite is to be as lightweight as possible. It doesn’t need the bloat introduced by any other types. Blobs + data serialization in the client library will be good enough for most other cases.
Anyone else here use R (+ openxlsx2) + SQLite to produce Excel reports? I’m finding that to be way better than flat file bullshit and writing macros.
“Null” is not a distinct data type. It’s a specific value that a data type can contain.
Ok, but is a Null Integer the same as a Null Text value? I’m really asking, I don’t know.
Conceptually, as in what the null represents at the DB level, yes. Logically, at the software level, I’d say sort of, but not really. It’s complicated. Does it make sense to compare values of fundamentally different types?
