this post was submitted on 24 Dec 2023

98 points (96.2% liked)

Today I learned

7850 readers

1 users here now

founded 4 years ago

MODERATORS

[email protected]

TIL that SQLite has only five data types (lemmy.dbzer0.com)

submitted 10 months ago by [email protected] to c/[email protected]

21 comments fedilink hide all child comments

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!

all 24 comments

sorted by: hot top controversial new old

[–] [email protected] 32 points 10 months ago

Well it's kinda in the name SQLite

[–] [email protected] 22 points 10 months ago (2 children)

I find the lack of dates the most annoying

[+] [email protected] 18 points 10 months ago (4 children)

[deleted]

[–] [email protected] 18 points 10 months ago (1 children)

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

[–] [email protected] 7 points 10 months ago* (last edited 10 months ago) (1 children)

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.

[–] [email protected] 15 points 10 months ago

because I'm objectively a terrible programmer.

Sounds like you know enough to know you don't know that much, which means you're a better programmer than you think!

[–] [email protected] 3 points 10 months ago (1 children)

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.

[–] [email protected] 1 points 10 months ago (1 children)

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

[–] [email protected] 2 points 10 months ago (1 children)

you wouldn't be able to sort or compare in your queries though

[–] [email protected] 2 points 10 months ago (1 children)

why not? assuming you're saving them all in UTC they should be perfectly sortable and comparable (before, equal, after) as strings, even with varying amounts of precision when you compare substrings. You can't really do math with them of course, but that's what I meant about how DBs interpret dates and time: if you use it do to math and then you also use your application's date library to do math, you'll likely run into situations where the two come to different answers due to timezone settings, environments, DB drivers and the like. Of course if I could rely on the DB to do the math exactly the way I'd expect it to, then having that ability is awesome, however that requires more knowledge about databases and their environments than I currently have

[–] [email protected] 2 points 10 months ago (1 children)

obviously it depends on your requirements but what I meant was you can't compare them at the DB level within queries.. you have to pull the data out into your application layer to use your date library.

The problem there is you effectively need to grab more data than you need to answer queries like "which record has the most recent date" or "give me all the records between the values of the result of this subquery". it can quickly become a massive bottleneck and may even prevent you from doing certain types of queries at all due to memory limitations.

[–] [email protected] 3 points 10 months ago* (last edited 10 months ago)

But, you totally can? When you store all your dates as an ISO 8601 string (UTC, so with Z at the end), you can simply compare the strings themselves with no further complications, if the strings match, the dates match, if one string is less than the other, the date therein is before the other. Their lexical order is equal to their chronological order

I agree that it's a massive and unnecessary overhead that you should definitely avoid if possible, but for anything where this overhead is negligible it's a very viable and safe way of storing date and time

edit: I forgot, there's also a format that's output by functions like toUTCstring that's totally different and doesn't have any logical order, but I honestly forgot about that format because nobody in their right mind would use it

[–] [email protected] 10 points 10 months ago

Is it me or isn't ISO 8601 string more than enough?

[–] [email protected] 20 points 10 months ago* (last edited 10 months ago)

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.

[–] [email protected] 6 points 10 months ago (1 children)

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.

[–] [email protected] 3 points 10 months ago

I started a project trying to use excel to analyse data, but quickly found that it's very limited, then I dumped the data into SQLite and added a statistics plugin, and bingo!

[–] [email protected] 5 points 10 months ago* (last edited 10 months ago) (1 children)

"Null" is not a distinct data type. It's a specific value that a data type can contain.

[–] [email protected] 4 points 10 months ago* (last edited 10 months ago) (1 children)

Ok, but is a Null Integer the same as a Null Text value? I'm really asking, I don't know.

[–] [email protected] 5 points 10 months ago* (last edited 10 months ago) (1 children)

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?

[–] [email protected] 3 points 10 months ago

In a system like Java's where everything derives from a common object class one can say null is a valid value of object type, so any two null values are equivalent. With ANSI nulls, even null isn't equivalent to null.

[–] [email protected] 3 points 10 months ago

Have you ever tried storing a string in an INTEGER column ?

Due to a bug it happened in a software I worked on.

SQLite just stores the string. I have no idea if the column type has any real effect on storage as far as SQLite is concerned.