SQLite for Beginners: Dates

SQLite doesn't have date and time datatypes, which begs the question, how do you deal with your date and time values?
Want to learn more? Check out this video.
mycelial.com/#newsletter
/ mycelial
github.com/mycelial
/ discord

[@CameronFlint07]

Your tutorials on sqlite are the best! Very well-explained and pragmatic. Thank you!

[@GerbenWijnja]

If you're wondering what the hell is going on when he inserted a string into a real column, and even retrieved it again... Sqlite doesn't care about column definitions. It will happily store any type into any column, regardless of its definition. You can store strings into int columns, sqlite will not complain. It also ignores columns lengths, like "varchar(20)". You can store a 100 character string into that column, no problem. Learn about "type affinity" for more info.

[@kishanbsh]

sqlite now has a "strict mode" prevents such stuff from happening.

[@AnyThink5]

thankyou

[@user-qp7pn1gy5e]

cool video 💀💀💀

[@CanRau]

So which one would you recommend? Specifically for created_at, updated_at?

[@kubre]

These columns usually use timestamps in most projects I've seen

[@nodidog]

I personally use STRFTIME('%Y-%m-%dT%H:%M:%fZ') This gives a standard RFC 3339 timestamp format with sub-second precision, e.g. 2024-04-20T08:03:48.842Z