[petcat]

> the "architect" imagines they need more (or is preparing for the potential)

I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.

Hipp said that it will never be added because it will bloat the size of the embedded object. Because that is what SQLite was designed for: single-user embedded databases. Like the address book on your phone.

[Xenoamorphous]

> I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.

To be honest if you're using JSON at any point in your stack you have the same issue.

[masklinn]

In fairness, sqlite is perfectly happy with Julian dates or Unix timestamps (that’s the affinity of a column typed “datetime” in non-strict mode) and timestamp(tz) are nothing to write home about except in complaint.

[petcat]

so now we've traded a text column for an int column that still can't validate that the number is actually externally consistent with the real world.

[echoangle]

Is there an invalid unix timestamp? What is there to validate?

[petcat]

Well for one we should probably validate that the number is smaller than the total life of the physical universe.

SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed.

Try reading that back in any application date/time code and your app probably crashes immediately.

[masklinn]

> SQLite will gladly store a u64::MAX as a "unix timestamp"

SQLite does not support unsigned integers.

> Try reading that back in any application date/time code and your app probably crashes immediately.

Postgres will happily ingest and produce dates in the 280th millenium, which will also crash your application if its datetime type can’t handle that shrug.

You can add a check constraint that your field passes through SQLite’s datetime functions and it’ll be clamped between the years 0 and 9999. Or you can put in your own limits matching your application layer, or your application’s business logic. That’s what check constrains are for (amongst other things).

[petcat]

> SQLite does not support unsigned integers.

omg you're right. Another example of SQLite's type-flimsyness.

> You can add a check constraint

So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.

[echoangle]

And how would you validate that? Which arbitrary cutoff do you find reasonable?

And if your app crashes on an input like that, you should pick a different date Library or stop and rethink your coding skills.