changelog.com/posts

You might as well timestamp it

future-you will be glad you stored a timestamp instead of that boolean

by Jerod Santo • 2021-04-23 • #databases +1 •

In my 15+ years of web development, there are very few things I can say are unequivocally a good idea. It almost always does depend.

Storing timestamps instead of booleans, however, is one of those things I can go out on a limb and say it doesn’t really depend all that much. You might as well timestamp it. There are plenty of times in my career when I’ve stored a boolean and later wished I’d had a timestamp. There are zero times when I’ve stored a timestamp and regretted that decision.

Why is that? Because any time a piece of data is important enough to store its truth/falsehood in your database, it’s likely important enough that you’ll eventually want to know when that state was set. It’s like the exact opposite of YAGNI. You Ain’t Gonna Regret It?

Even in the rare case that you never need that timestamp… what have you lost? Its cost is negligible, both in data storage and coding overhead. Your code merely needs to consider a NULL timestamp as false and any non-NULL timestamp as true and there’s your boolean use-case. An example, in JS:

// using a boolean
let is_published = true

if (is_published) console.log("it's true!")
if (!is_published) console.log("it's false!")

// using a timestamp
let published_at = new Date()

if (published_at) console.log("it's true!")
if (!published_at) console.log("it's false!")

The ergonomics are darn-near identical. And you can pretty this up with a helper function called isPublished() which checks the published_at attribute. Basic stuff you already know, I’m sure!

  • The next time you’re tempted to store an is_deleted boolean, reach for a deleted_at timestamp instead.
  • If you think you need an is_hidden field, try using hidden_at in its place. Live with that for awhile and then ask yourself if you regret the decision.
  • Or if you’re about to create a has_signed_in boolean? You’re gonna want to know when that happened: signed_in_at to the rescue.

Future-you will thank you.