[davidverhasselt]

There is a downside which I've experienced: if you want a triple-state boolean (null, false, true) then having a boolean column allows for that while a timestamp-as-boolean column does not (you lose the "null" value because that equals `false` in timestamp-as-boolean).

Having a distinction between `null` and `false` can be handy for values that are optional or have a dynamic default. If it's `null` you know it is not explicitly set and could use a fallback value. If it's false you know it's explicitly set as false.

A simple use-case for this is when a user can leave the field blank. This is impossible to model with only a timestamp-as-boolean.

Another use-case is dynamic defaults or fallbacks, e.g. `hidden` of a folder where if `hidden` is nil, you fall back to the parent folder's value.

TL;DR a boolean column actually has 3 states, a timestamp only has 2. Article makes a big deal about there not being any nuance about the fact that a timestamp is superior. I disagree, because you go from 3 states to 2 states, there are cases where you'd want a boolean instead of a timestamp. Ironically OP missed this nuance (or they'll pull a no-true-scotsman).

[alpaca128]

What you describe calls for an Enum, not a nullable boolean which is just another way of passing a hardcoded magic value carrying an implicit meaning.

> a boolean column actually has 3 states, a timestamp only has 2.

Going with your logic a timestamp has billions of states, you just have to arbitrarily assign special meanings to certain dates that won't ever be used. Just like using null as another state I wouldn't call it a good idea, though.

[hallway_monitor]

Your statement is false. Many use cases call for optional bools. The great thing is there is no implicit meaning - it's True, False or Unset. It is true that nulls can be abused but in this case, far more elegant than an enum.

[andix]

Tri-state booleans are also very ugly. What happens if you suddenly need a 4th state?

Use enums (or any equivalent) for states that are non-boolean.

[jimktrains2]

Null is a signifier that a value isn't know. Yes, if you're using null to mean something else you should use another type, but nullable-booleans (and nullable fields in general) can be extremely useful. For instance, if you're collecting information, but wont know everything at creation time, the unknown values are null.

[hallway_monitor]

If you are using a nullable bool, you do not need a fourth state. True, False, Unset. Very useful in a lot of cases. It's actually not ugly but very elegant.

[cdirkx]

The obvious solution is to encode it like this: `null` is `null, `yes` is the timestamp when it was set and `false` is a timestamp somewhere in the future. Now t < now() maps nicely to the value of your boolean.

You just have to pick your `false` timestamp somewhere far into the future, let's say something arbitrary like 03:14:07 on Tuesday, 19 January 2038. The software won't be around for that long anyway, so it will never be a problem...

[davidverhasselt]

Hehe, that's creative. But I'd say - if you're making it that complex, just use a boolean column and a timestamp column separately instead, if you really need the timestamp, and make it explicit and simple to understand.

[gpvos]

Better use something so far in the past that the software certainly didn't exist yet, like 3 January 1970. In fact, you could use all of, say, the '70s to encode lots of flags... gets excited

[Supermancho]

Store your timestamps in a long int or string.

Positive = timestamp

-1 = false

0 = null

[thirdlamp]

I can’t tell if you’re being ironic or not

[notatoad]

OP missed that nuance, and so will a lot of other developers you work with. Relying on a difference between null and false is one of those things that seems clever when you first do it, but then three years later your project is scattered with comments like "// be very careful checking this, it must be false here null means something else" after you or some other dev you're working with confuses falsy for false

[cratermoon]

I've been in software development for 27 years, and I've never seen a case where a boolean column or variable being null was anything but a terrible, confusing, fragile, hack.

Remember that in some languages the behavior of null is weird, and can be false, or can be treated as 0.

    $ node
    Welcome to Node.js v14.16.0.
    Type ".help" for more information.
    > null + null
    0
    >
    > 0 == false
    true
    > 0 === false
    false
    >

[secondcoming]

What has the ridiculousness of Javascript got to do with a database schema?

[doodpants]

Long time readers of The Daily WTF know that the canonical tri-state boolean is { True, False, FileNotFound } [1]

[1] https://thedailywtf.com/articles/What_Is_Truth_0x3f_

[timzaman]

"triple state boolean", what's next, a double precision fp32?

[tuukkah]

Also known as *bool, "Maybe Bool", "Boolean?", "Optional<Boolean>" etc.

[hibbelig]

Long Boolean! Yes, no, don’t know, weekdays only.

[pezezin]

If boolean comes from George Boole, then maybe we should call triple state values "Łukasiewiczan", it would make for a funny typing exercise :P https://en.wikipedia.org/wiki/Jan_%C5%81ukasiewicz

[chadlavi]

The use cases the blog post is discussing aren't tri-state bools though. Either the user did or did not sign in. Either the version was or was not published. Null and false are equivalent here.

[BiteCode_dev]

If the timestamp is a creation date after your system went on, which it is here, you can always store 0 for false.

[kijin]

-1 can be useful, too, especially if you've already assigned a certain meaning to 0.

Javascript developers are used to certain functions returning -1 if there's no match, so -1 shouldn't feel strange as long as it's well documented.

[johnchristopher]

But then it's empty string vs null value all over again.

Besides it's about an improvement to boolean, not about adding one more optional value (which likely lead to optional values and definitely out of the boolean field).

[underwater]

I'm struggling to think of an example following the OP's example - discrete events that may have not occured yet - where you'd need to differentiate between a certain `false` and an uncertain `null`.

[rrrrrrrrrrrryan]

A question like "Do you have any allergies?" probably requires ternary logic: yes/no/unset

Discrete events are usually more binary by nature: a thing either happened or it didn't.

That said, if it's possible for an event to un-happen, you're back in ternary-land: there's now a distinction between un-set and false which may be important to capture.

There's a reason why relational databases use ternary logic when most of the rest of the computing world uses binary logic.

You might argue that you could just create a brand new event, but now you've almost assuredly changed the grain of your table and goofed up the primary key. Your nice normalized table is now a dumb, non-performant endless event log: good luck with indexing that table and tuning those SELECT queries.

[davidverhasselt]

The OP does not talk about events, OP talks about state and when it was set. There's no where in the article that OP restricts themselves to discrete events that may or may not have occured.

[another-dave]

Well the article uses the example of swapping `is_published` for `published_at` — with a boolean you could have:

* NULL — never published (e.g. draft) * true — live now * false — previously live but explicitly unpublished

Which you miss if just a date. Similarly he talks about `is_signed_in` — NULL/true/false let's you model the case where a user has never signed in (e.g. an admin created your account but you've never used it) but NULL/timestamp missed this

[goofballlogic]

In my case the null state was "not yet processed" i.e. a decision pending

[joosters]

Perl got this right decades ago with its 'undefined' status for unset variables, so you can tell the difference between false and undef

[maxlybbert]

That make me think more of Javascript, which distinguishes between undefined values (usually means “not set”) and null values.

You’re correct that a Perl scalar can always be set to undef, which is the Perl name for null. But that’s not really unique to Perl. For instance, while a Java boolean can’t be null, a Java Boolean can be.

[jwr]

That depends on the language. In Clojure and ClojureScript, for example, distinguishing between nil and false is not a problem at all.

[davidverhasselt]

I'd assume most languages don't have a problem with distinguishing between nil and false. The article explicitly maps nil to false, thus I don't see the relevance of your comment.

[867-5309]

the initial state is going to be false before any user input. if the user leaves a field blank, then that still counts as input, just run a check such as 'if blank then null else timestamp' on the insert

[gorkish]

0 for never set, timestamp for set time, negate the timestamp for unset time

[Viliam1234]

dates between October 4th and 15th 1582 for special values

[Aeolun]

You can use negative values for your alternative states.