4 ms·

I've yet to find a case where using a timestamp over a boolean hasn't been the better option. This is because turning a boolean on is an event so it'll always

I've yet to find a case where using a timestamp over a boolean hasn't been the better option. This is because turning a boolean on is an event so it'll always have a timestamp. Sometimes it's useful to know when this event happened. The only exception I can think of is if for some reason you're trying to save on bytes, which in this day and age, especially true for web applications, this is practically never the case.

mewse 5y ago
I guess I’m a little confused, as this article seems to be speaking to programmers who are doing stuff that I’ve never done and in programming paradigms I’ve never used, so I’m definitely not in the target audience. But.. if I’m understanding the proposal correctly, this only gives you a timestamp if the value is ‘true’, and not if it’s ‘false’. Is that correct? Is there a reason why we care about when a boolean is turned on but not when it’s turned off? Why would we not store the boolean and the “timestamp of the last change” as separate values, so we can track the timestamp of changes in both directions, if that’s a thing we care about?
- mjhagen 5y ago
  That's how I usually do it (deleted bit, updated timestamp and also a created timestamp as that's sometimes info we need). In cases that need an audit trail I'll add a log line as well.
- rini17 5y ago
  Correct. But to track both directions, I'd use two timestamp columns: is_active and is_inactive with check constraint that only one of them can be non-null. Otherwise someone is likely to omit updating the “timestamp of the last change” in a hurry.
- deleted 5y ago
  [deleted]
- mgreenleaf 5y ago
  I think it depends very much on the data being modelled. Certain situations might call for a timestamp instead of a boolean, especially if it is a value that is only ever turned on once and never turned off, possibly `user_deactivated_at`; I do prefer having a bit field and a separate timestamp for things that can flip; and for a lot of use cases it is good to just have a full event stream implementation where you can construct the state at any point in time and you get events data combined with the timestamps.
- jpswade 5y ago
  It’s a good question, but if you care about the audit trail, then perhaps event sourcing is a better choice. So yes, this only assumes you care about when the on state happened and you don’t care about the history.
  - vbsteven 5y ago
    Eventsourcing is a possible solution for audit trails but comes with additional complexity. If you just want to add audits of what changed when then you can also get there with a separate audit table that just stores timeframe, changed fields with new values and who/what made the change. Some frameworks have support for this out of the box or with a small library.
johnchristopher 5y ago
> This is because turning a boolean on is an event so it'll always have a timestamp. Turning it off too but we don't have a timestamp :/.
- joosters 5y ago
  I suppose you could store negative timestamps to represent boolean false values, but that will inevitably lead to bugs where a piece of code mistakenly compares a negative timestamp with zero.
defaultname 5y ago
Every change you can make in a database becomes an event by this definition. It's just as useful to know when every change happens in your database, and by who. Which is why we have `audit' logs. Usually online logs that can recover every change over the entire history of a database, every row having a versioned history of what changed by who. By keeping it separate, not only does it make domain modelling and intuitive use more logical, it makes primary query performance dramatically better. And if logically you want to treat them as events, they should be in a chronological events table by themselves, not as an overloaded nullable field. To distill what I've said above, as politely as possible I will say that if you model a boolean as a timestamp, you are covering up for other much larger problems.

mewse 5y ago

I guess I’m a little confused, as this article seems to be speaking to programmers who are doing stuff that I’ve never done and in programming paradigms I’ve never used, so I’m definitely not in the target audience. But.. if I’m understanding the proposal correctly, this only gives you a timestamp if the value is ‘true’, and not if it’s ‘false’. Is that correct? Is there a reason why we care about when a boolean is turned on but not when it’s turned off? Why would we not store the boolean and the “timestamp of the last change” as separate values, so we can track the timestamp of changes in both directions, if that’s a thing we care about?

mjhagen 5y ago
That's how I usually do it (deleted bit, updated timestamp and also a created timestamp as that's sometimes info we need). In cases that need an audit trail I'll add a log line as well.
rini17 5y ago
Correct. But to track both directions, I'd use two timestamp columns: is_active and is_inactive with check constraint that only one of them can be non-null. Otherwise someone is likely to omit updating the “timestamp of the last change” in a hurry.
deleted 5y ago
[deleted]
mgreenleaf 5y ago
I think it depends very much on the data being modelled. Certain situations might call for a timestamp instead of a boolean, especially if it is a value that is only ever turned on once and never turned off, possibly `user_deactivated_at`; I do prefer having a bit field and a separate timestamp for things that can flip; and for a lot of use cases it is good to just have a full event stream implementation where you can construct the state at any point in time and you get events data combined with the timestamps.
jpswade 5y ago
It’s a good question, but if you care about the audit trail, then perhaps event sourcing is a better choice. So yes, this only assumes you care about when the on state happened and you don’t care about the history.
- vbsteven 5y ago
  Eventsourcing is a possible solution for audit trails but comes with additional complexity. If you just want to add audits of what changed when then you can also get there with a separate audit table that just stores timeframe, changed fields with new values and who/what made the change. Some frameworks have support for this out of the box or with a small library.

johnchristopher 5y ago

> This is because turning a boolean on is an event so it'll always have a timestamp. Turning it off too but we don't have a timestamp :/.

joosters 5y ago
I suppose you could store negative timestamps to represent boolean false values, but that will inevitably lead to bugs where a piece of code mistakenly compares a negative timestamp with zero.

defaultname 5y ago

Every change you can make in a database becomes an event by this definition. It's just as useful to know when every change happens in your database, and by who. Which is why we have `audit' logs. Usually online logs that can recover every change over the entire history of a database, every row having a versioned history of what changed by who. By keeping it separate, not only does it make domain modelling and intuitive use more logical, it makes primary query performance dramatically better. And if logically you want to treat them as events, they should be in a chronological events table by themselves, not as an overloaded nullable field. To distill what I've said above, as politely as possible I will say that if you model a boolean as a timestamp, you are covering up for other much larger problems.