Comment by jchw
20 hours ago
32-bit UNIX timestamps are often signed so you can actually go before that, but most UNIX timestamps are 64-bit now, which can represent quite a larger range. And SQL datetime types might have a totally different range.
Not that it really matters; deleted_at times for your database records will rarely predate the existence of said database.
It's not about the scale, it's that `if (0)` will evaluate to `false` in many languages.
In addition to the sibling comment, which is exactly right (you should be using a nullable column here, if you're using SQL, for multiple reasons) I reckon this a design issue in the programming language that is largely unrelated to how you model the database. It's pretty easy to run into bugs especially if you compound it with other quirky APIs, like strcmp: `if (strcmp(a, b)) // forgot to do == 0; accidentally backwards!` -- So really, you just don't have much of a choice other than to tread carefully and enable compiler warnings. Personally in this case I'd use an Optional wrapper around the underlying timestamp type anyways, if I needed to be able to represent the UNIX timestamp at 0 as well as an empty state.
So you're still fine as long as you're not tracking things that were deleted on that exact instant 50 years ago, a safe assumption, for instance, for things that happened in your application that has only existed for less time than that. That said, I haven't ever seen this implemented in a way that casts. It's implemented with scopes in the ORM, usually.
etc.
which generates a query with "WHERE deleted_at IS NULL"
1-1-1970 is fine.