I think this timestamp-as-a-boolean is a good idea if the field is always going to be interpreted as either True or False and nothing more. If the field in question allows for a 3rd (uncertain) value, then using a timestamp would be extremely confusing.

Good point. However, approaching this problem from “YAGNI” point of view is a bit misleading, I think. If you are not going to need the timestamp, you shouldn’t add it to your code base.

I don't agree it was a good point. It sounds like the blog author missed a requirement a few times, and after getting repeatedly burned in the requirements gathering stage he now overcompensates previous failing with I'll advised usages of timestamps instead of booleans.

YAGNI is always true. Always. The author's point, even when timestamps end up being required, are moot.

Also, if state changes are required them you don't tack on a timestamp to a row. You instead track events, including switching stuff on and off.

I feel this blog post is bad advise fueled by trauma.

Much better to just have two columns, so e.g. you can see “enabled” and an 'enabled_date" that indicates when you last enabled/disabled the entity.

That sounds good until you realize you now have two sources of truth, do you trust enabled or enabled_date? If you really want to go this route enabled should be a virtual field that checks enabled_date in the background so you can have the boolean semantics but still keep a single field.

I also used booleans a lot previously but since using Laravel I have come to enjoy the updated_at, created_at and deleted_at fields that it automatically creates and I follow this format as well now if I need more.

But if it can be disabled we'd also need a disabled_date, however this implies that the state can switch from enabled to disabled and vice versa an infinite number of time, so we should create n*2 fields (enabled_date_1, disabled_date_1, ..., enabled_date_n, disabled_date_n) where n is the maximum amount of state switches/2. Of course we'll have to implement stream logging of events into a database, or at least some sort of counter, to determine the value of n, and then dynamically create new fields as needed.

Problem solved!

Using a nullable Boolean to represent 3 distinct states just adds confusion and complexity to your system. In most cases I would prefer to use an enum with 3 fields which is non nullable.

Yeah, this feels like "premature optimization". When you design your applications and databases, it should reflect your understanding of the problem and how you solved it as best as possible. Using DATETIMEOFFSET NULL when you actually mean BIT NOT NULL isn't saying what you mean. If you already understand that you have a boolean option and you think you might need a timestamp to track it, use 2 columns. Or an audit table. So sayeth the holy SRP.

Completely agree, I cram a timestamp column in every table, but booleans have their purpose too.

To add to this: the DBMS may treat null as unknown rather than not set. This may be not immediately obvious or noticeable, but it means a check requires different syntax and even double checks for set and value. Using null as 'cleared' goes against the DBMS definition of what null means.

Sql server docs

As a side note: this difference in null behavior can become especially problematic when you use entity frame for to map tables and sql queries to C#. Because it's not obvious and may not be known or seen.

You wouldn't store this information on the same table (unless you're using a wide row db like dynamo/Cassandra). In a SQL world, you'd store version information in a separate table - one table for the HEAD state and another for history.