13 ms·

You might as well timestamp it

307 points by mot2ba 5y ago

perspicace 5y ago
I love the drake meme they embeded for sharing the post!
dashwav 5y ago
This seems to only really work in languages that allow null variables/timestamps. I wouldn't really want to have to do comparators to the default value of a timestamp.
- choeger 5y ago
  The author is talking about databases, not programming languages. I do see a different issue, though: The article indeed seems to make no distinction between an absent value and a default timestamp of 0. That limits your database to more or less "now". You cannot really store things about the past. Someone might take such a pattern and fixate it into some kind of library. If then someone else tries to store data from 1970, things can get ... interesting.
  - ghego1 5y ago
    That is a very neat and smart improvement!
  - kedean 5y ago
    That does assume your database only allows unix-style timestamps. MariaDB, for example, has "datetime", supporting dates between the year 1000 and 9999, distinct from null/zero. Unfortunately, datetime takes 8 bytes vs the 4 for a timestamp.
- ShroudedNight 5y ago
  > ...default value of a timestamp. Assuming the timestamp represent a change of state in a contemporary application, I would expect 1970-01-01 0:00:00Z (UNIX epoch +0 seconds) to be unambiguous enough (But that's definitely an engineering constraint to maintain awareness of)
- samus 5y ago
  PostgreSQL has computed columns. Creating one for every field that returns its `IS NOT NULL`-ness would accommodate such programming languages.
- nicoburns 5y ago
  Languages with Option or Maybe types instead of null will also work fine. So it works in every langauge except Go?
  - rsa25519 5y ago
    Yep. And I assume you could use Time.IsZero() for Go https://stackoverflow.com/a/36234533 https://stackoverflow.com/a/36234533
redact207 5y ago
To quote the author: "it depends" If when something happens needs to fold into your business logic then by all means go for it. If you're moreso doing it as an audit then logging out the event with it's context is going to be more useful.
- rsa25519 5y ago
  > To quote the author: "it depends" Where do you see this? To quote the article I'm reading: "it doesn’t really depend"
  - mwill 5y ago
    Not op but in the first paragraph he links to another post of his that is all about saying "it depends" often >https://changelog.com/posts/good-reason-experienced-devs-say-it-depends https://changelog.com/posts/good-reason-experienced-devs-say... I'm assuming op is referencing that (I agree with you here though that in this case he makes a very good argument for "it doesn't really depend")
xlii 5y ago
It's a very neat idea that I'll consider in the future however I have some concerns about it. One thing is about the database design. I remember lecturer proclaiming that models with many nullable relations is: - bad design - performance risk - might mess with indexing I haven't verified this knowledge in many years, to I'm not sure if that point still stands, also in wake of not optimizing pre-mature this might not be an issue. The other thing is introducing of (needless) complexity to the system. It allows to make unwise decisions which otherwise would not be possible if the flag would remain simple boolean and as such stands against KISS system design principles.
- diamondo25 5y ago
  Yes, nullable relations are harder to work with, especially with ON DELETE CASCADE and checking if another table contains a row based on a nullable column. However, this is about booleans, so they werent used in relations to begin with. The indexing in this case could be a bit less performant but probably neglegable.
- madacol 5y ago
  The OP doesn't talk about *nullable relations*, which I agree is a sign of bad desing
davnicwil 5y ago
This is right. I'm a big fan of this sort of embedded audit metadata wherever it makes sense. I do wonder when doing stuff like this though, if this really shouldn't be something that the database gives you for free. I read a few years ago about 'fact based' event stream style databases which store your data as a stream of time ordered ops that can later serve as an audit log, but can be used for even more powerful things such as backups at any point in time, debugging at any point in time, etc. For practical reasons (i.e. just picking a standard postgres setup to get stuff done) I've never dug into any of these systems or played around with them. Anyone know what the latest and greatest is here? Is there anything I can install on top of postgres to give me this functionality today?
- terhechte 5y ago
  I think Datomic[1] works like that Here's the relevant quote: "Understand how and when changes were made. Datomic stores all history, and lets you query against any point in time. Learn More" [1] https://www.datomic.com/ https://www.datomic.com/
- oftenwrong 5y ago
  This is often called "event sourcing". The other commenter mentions Datomic, which probably is the 'latest and greatest' form of event sourcing (I've never used it). https://vvvvalvalval.github.io/posts/2018-11-12-datomic-event-sourcing-without-the-hassle.html https://vvvvalvalval.github.io/posts/2018-11-12-datomic-even... If you want something built on postgres, I don't have any specific recommendations, but you can build a simple event sourcing system yourself. I worked somewhere that used event sourcing on postgres, and the core log was basically just a table with aggregate IDs, event sequence numbers, and a JSONB column for the event payload. I recommend starting with just one part of your application if you're going to adopt event sourcing. You will quickly find that there are a lot of new considerations and pitfalls that you don't have with traditional RDBMS usage. Overall, event sourcing is hard to get right, so you should consider the trade-offs carefully. There are easier ways to get audit logs, for example: https://www.pgaudit.org/ https://www.pgaudit.org/ I will also recommend this as a way to start to understand the tricky aspects of event sourcing: https://leanpub.com/esversioning https://leanpub.com/esversioning
- 5y ago
ghego1 5y ago
TLDR don't use a boolean, leave the field empty (NULL) and set a timestamp when should be true. To be honest a tldr isn't actually needed, the post is both very concise, straight to the point, and convincing. As per the languages I use more often to query DBs (TS, JS, PHP, Python) I don't see any downside. Evaluating if a variable is empty or not, or it's type, is not "bad", compared to evaluating if a variable is true or false. Even in TypeScript in strict mode, evaluating if a variable with type number is empty or not will result in validly typed code, without any noticeable difference compared to evaluating a variable with type boolean.
lars512 5y ago
Sitting in the data science seat, downstream from the application development and trying to gain insights on production data, I completely second this approach. It just gives you more to go on and more ways to validate whether something unexpected is going on.
rurban 5y ago
You might as well version it. Timestamps are relative if different machines set it. Versions are primitive (ie atomic) and always absolute, and usually the best way to treat concurrency.
alephnan 5y ago
This reminds me of tricks in JavaScript such as using !! to convert values to Boolean: it’s clever, “idiomatic” and save a few characters, but it’s not self explanatory to someone not familiar with the idioms
- rsa25519 5y ago
  Fortunately `published_at == null` is much more intuitive that using `!!` to typecast.
jchw 5y ago
Indeed, this is a bit of wisdom I first encountered when playing with Django and seeing others do it. You can still see it in a couple of the many soft-delete packages available on PyPI, in the form of deleted or deleted_at fields with DateTimeFields. (Though admittedly, I'm pretty out of date on Django these days.) (Though it is worth noting that you sort-of get this for free if you implement a scheme with 'revisioned' data, or a database that simply has that as a feature. But, it's still useful to just have this additional bit of information handy, if nothing else.)
- vbsteven 5y ago
  I first picked it up from Rails many years ago. Now almost every SQL table I create has created_at, updated_at and possibly deleted_at or archived_at fields. Many frameworks or ORMs have support for automatically setting updated_at. And it might even be done at SQL level but I’m not 100% sure on that.
  - sopooneo 5y ago
    I’ve tried that approach of moving users to a “deleted_users” table but ran into the problem of losing the foreign key references to any records pointing at the user.
- dsego 5y ago
  I've had experience with the soft delete in laravel and I'm not fond of it. Because we had a separate dashboard with manual SQL reports without an ORM to magically filter out all the deleted_at rows. And it gets tedious to remember to filter out the rows with deleted_at timestamps. I would like to see the soft-delete implemented differently, say a second trashcan table for each active table, e.g. users_archive, and the delete operation would move the row there.
  - sdfhbdf 5y ago
    Or you could have views with `deleted_at` filtered out: https://learnsql.com/blog/sql-view/ https://learnsql.com/blog/sql-view/
jpswade 5y ago
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.
deleted 5y ago
[deleted]
deleted 5y ago
[deleted]
lazyasciiart 5y ago
Am I missing something, or does this assume I never care about the timestamp for turning a value off?
- globular-toast 5y ago
  Yeah, I don't get that either. This only seems to work in very specific cases, like where the boolean starts off false, becomes true and can never be false again. So like "user viewed homepage". That seems like a tiny subset of what Booleans are used for but it's presented as being the one, obvious use case.
davidverhasselt 5y ago
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).
- jwr 5y ago
  That depends on the language. In Clojure and ClojureScript, for example, distinguishing between nil and false is not a problem at all.
  - davidverhasselt 5y ago
    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.
- johnchristopher 5y ago
  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).
- joosters 5y ago
  Perl got this right decades ago with its 'undefined' status for unset variables, so you can tell the difference between false and undef
nickjj 5y ago
Indeed, I do this pretty much all the time too. One common'ish example not mentioned in the article is storing whether or not a user is active. Storing "is_active" as a boolean makes sense but switching that to "deactivated_at" gives you so much more information.
- kijin 5y ago
  last_active_at also makes sense, especially if you'd like some flexibility in deciding what to do with users who have been inactive for a certain amount of time.
koff3 5y ago
I would not do this unless there is a use case. For analysis purposes you can always read the whole change history from audit logs. The solution also only gives you the timestamp when something was set, but not when it was unset.
durnygbur 5y ago
let published_at = new Date() if (published_at) console.log("it's true!") if (!published_at) console.log("it's false!") As a FE dev I haven't had workplace with a codebase allowing above for at least 5 years. No one even asks "shall we us JS ot TS?". Strictly enforced static typing all over. It's not that I like it, just no one asks me.
- woutr_be 5y ago
  What's wrong with this? Checking if a variable is defined/null is not exactly uncommon?
  - kaeruct 5y ago
    In JS, if (!variable) ... coerces the type to boolean, so anything falsy (0, null, undefined, empty string, etc...) will become true. For example if you have a timestamp of 0, it will be counted as false (but is defined and definitely not null)
    - woutr_be 5y ago
      I understand that. But how would strictly enforced typing solve this? Especially when using TypeScript. An incorrect value being given isn’t going to be caught by the compiler.
ximm 5y ago
One important downside: Data protection. This approach of "store it now in case you might need it later" is in direct violation of the principle of data minimisation in GDPR.
- progre 5y ago
  GDPR only applies to personal data though? Like you can't store gender info "in case it's usefull later". I really don't see how a timestamp can be used in that way.
  - globular-toast 5y ago
    Well, since gender is mutable now, I suppose you can't store the date the gender changed "just in case". But yeah, unless you're dealing with people, this doesn't apply.
- imperistan 5y ago
  I think thats only the case when it the data can be used to (help) identify a specific person
  - ximm 5y ago
    No, not exactly. It is the case when this data is related to a single person. E.g. "has this person subscribed to my newsletter" vs "whan has this person subscribed to my newsletter".
bob1029 5y ago
I don't like this. Yes, you can alias the true/false fact to null/non-null datetime value, but this is missing the point of domain modeling. The immediate impact of this decision is probably negligible as long as you did not need to store a nullable boolean fact, as opposed to a non-nullable boolean fact. The broader impact of this decision is that you have endorsed a policy of assuming how things will be used in the future and are not interested in a 100% authentic modeling of the problem domain anymore. In a larger team, these "well wouldn't it be nice if..." design decisions are extremely subjective and can beg many further questions that wind up being distracting. Discipline becomes very important as the complexity of your software project increases. It is easy to collapse the whole house of cards over little incremental things like this. You have to have a stricter policy across the entire team of saying things like "booleans go in as booleans, if you want who, when, why, those are 3 new facts next to the boolean".
- danielheath 5y ago
  I get that there’s a YAGNI aspect to it, but I don’t buy the argument that a timestamp adds complexity over a boolean.
  - bob1029 5y ago
    This all provokes confusion over 2 different types of facts: A) Knowledge of when a specific event occurred. B) If something is true or not. To use cases like "logged_in_at" as the example for why booleans shouldn't be used is essentially a strawman argument. There are many situations in which a boolean fact does not occur in the time domain or have any possible value. Knowledge of certain facts in certain problem domains can be viewed as timeless even if they did come into being at a discrete point in time. For example, regulatory facts that govern entire industries. You probably never care when a specific regulation started to matter for a situation, just that it does or not. All these timestamps would do is confuse downstream users and bloat extracts of data. The biggest problem of all is this statement: > 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. There is nuance to this problem. A and B are both perfectly valid cases and each have their own representations that make the most sense. The discipline is in identifying these cases appropriately and using the correct tool for the job.
hsbauauvhabzb 5y ago
Is there any concept in database engines of an audit trail which logs previous value and a time stamp? I’ve seen the concept using triggers, but never seen an engine native solution
- uyt 5y ago
  Maybe you're thinking of "event sourcing"?
- globular-toast 5y ago
  You can implement an append-only database, where each record is a snapshot of the latest version and a timestamp of when it was updated.
a_c 5y ago
In my experience I would even say having a boolean field in your table is a bad idea, with rare exceptions. e.g. - You may want proper state transition rather than having 4 booleans each representing one state - You may want to normalize the boolean with other metadata (timestamp, as OP suggestion, and author) into separate table,
madsbuch 5y ago
If one does this please be appropriate about semantics / naming. Ie. Don't put a timestamp in a variable called `isTermsAccepted`. Rename it into `termsAcceptedAt`. Generally accept that timestamps and booleans are not the same, but the truth value can be derived from the timestamp.
- bostik 5y ago
  > truth value can be derived from the timestamp Python used to disagree with you: https://lwn.net/Articles/590299/ https://lwn.net/Articles/590299/ (and the bug report with discussion spanning a couple of years: https://bugs.python.org/issue13936 https://bugs.python.org/issue13936)
  - raverbashing 5y ago
    Yeah, glad this was fixed > If I had to do it over again I would definitely never make a time value "falsy".
bachmeier 5y ago
If you're going this route, it's hard to understand why you wouldn't just store all the information you want explicitly in a string. "true [timestamp]" "false [timestamp]" "unset [timestamp]" That's more information than described in the article and it's easier for future you to understand what's going on, without implicit assumptions on the meaning of an undefined variable. Furthermore, you can keep a complete record of all status changes if that's what you want: "false [timestamp] true [timestamp] false [timestamp] true [timestamp]"
- vbsteven 5y ago
  This makes querying harder. You cannot use “is not null” or “= true” for where clauses with this. You’ll need to parse the string value. When going the explicit route I would recommend an is_archived (nullable) bool column combined with an archived_at timestamp column.
  - bachmeier 5y ago
    If you're using sqlite, for instance, you can call the substr function (I was sticking with the article's constraint to not add a new column). There is one thing in favor of storing the full history in a single string - you might not query the full history very often, and you can keep a lot of information around without adding another table. I've occasionally stored the full history of objects (short notes mostly) in an sqlite database as a string in json form. Pretty convenient if you're keeping it there just in case you want to go back in time and don't make a large number of changes.
  - d0100 5y ago
    At this point just create a archived_resources table where you store the resource id, timestamp and user id
goofballlogic 5y ago
Exception i encountered recently was needing ternary state boolean. a set of flags where true and false indicates outcome and undefined indicates "not yet processed". I suppose some sort of 0 timestamp could work but... No, I think I'll stick with boolean.
kown7 5y ago
In this case, why not go straight to append-only databases where every entry has a timestamp? That will be _the_ audit log in your database.
- berkes 5y ago
  This was my thought too. Timestamps are a usefull trick. But also one that allows you to postpone what the domain is really asking: to store a log of events. Maybe even as primary source (aka event sourced).
victorp13 5y ago
As the storage cost is very low, why not use both? Not trying to be obtuse, but I genuinely typically have separate columns in my schema design for booleans and the timestamps of these events flipping from false to true (published, edited). Come to think of it: These events warrant saving them to a separate table altogether; booleans represent a current state - there may be 1:n events like multiple edits.
antender 5y ago
Also, from the point of query optimisation this is a really bad idea. Usually you DO actually care about size of fields in SQL databases, because something like BOOLEAN is usually stored as single byte (or bit in a bitfield) vs 4 bytes or even 8 in case of timestamp. This not only multiplies on disk usage by at least 4 times, but also makes ALL indexes using this field way bigger. Also boolean indexes can be compressed (or stored as bitmaps), while timestamp indexes contain lots of unique values, so they can't be. This is also the reason why serial IDs are way better than UUIDs for internal IDs.
- traceroute66 5y ago
  > This is also the reason why serial IDs are way better than UUIDs for internal IDs. There are three core problems with that: a) Serial IDs are a nightmare for database merges, clustering or anything like that b) Serial IDs won't scale c) Serial IDs require management, whilst UUIDs can be produced anywhere (in DB, in frontend etc) There is the KSUID[1] if people want a time-sortable thing that is near-enough to a UUID. [1] https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid
  - hk1337 5y ago
    You can do both. Use the UUIDs where you need the scalability and serial where you don’t.
  - marcosdumay 5y ago
    > Serial IDs won't scale You mean scaling into several machines? Yes, they do scale. Nothing requires that the values are always increasing and have no holes, so you can slice and cluster them at will (and many DBMS do exactly that).
    - gmfawcett 5y ago
      I'm confused? Serial means "always increasing and having no holes" -- it's one thing after the other. What you're arguing for sounds like just IDs, not serial IDs.
andix 5y ago
I don’t like NULLs. Nullables always get back you in ways you would never expect. That’s why I won’t use a tip like that.
- vbsteven 5y ago
  The problem can be managed somewhat depending on the language and tooling. I have used this advice for archived_at or deleted_at before. In the database it’s a NULLable timestamp column. In application code I would map this field to an Instant? (Kotlin) and a computed isArchived val that checks for presence of the archivedAt field.
usr1106 5y ago
When reading this I miss a HN feature "upvote as a falsehood to be aware of".
MrOxiMoron 5y ago
except when it is actually a nullable Boolean and there is a big difference between the 3 states
codeulike 5y ago
So we're trying to come up with reasons why this is bad advice? uhhhhhh ... something something far future something Y10K problem something.
throw149102 5y ago
This makes me worry about clock skew and asynchronous code in general. For example, a timestamp might lead you to believe things happened in a different order than they did because the timestamping process isn't atomic.
dvfjsdhgfv 5y ago
But these are two very different use cases and just using one for another won't work in many scenarios or will make thing less efficient. It is much, much better to leave the boolean intact for the reasons other people explained and just add another column with the date if you actually need it. This will give you more flexibility while keeping things in order.
defanor 5y ago
As mentioned in other comments, there's a bunch of drawbacks with this approach, but there's a more general term "boolean blindness" [1], which is usually applied to programs (not databases). I find it useful to avoid using booleans where more descriptive types make sense (and can be used), as well as not throwing information away when it may be needed still, but to a reasonable extent. [1] https://www.cs.cmu.edu/~15150/previous-semesters/2012-spring/resources/lectures/09.pdf https://www.cs.cmu.edu/~15150/previous-semesters/2012-spring...
isuckatcoding 5y ago
I don’t see the benefit. This is what changelogs are for.
aasasd 5y ago
I have about the same sentiment in regard to personal notekeeping, and do in general care about preserving metadata. Many times I have consulted the date when I created a note or, say, an entry in the password manager—or when I last changed it. That may inform my decision on what to do with the note next, or at least allows me to contemplate how much is not done in the passing years and how much is yet to not do. ‘Remember The Milk’ and Evernote make it pretty nice and easy by keeping the dates and some other info. (Though of course there's a gotcha that RTM's Android app forgets to implement the display of this metadata.) Not that I recommend these apps currently, especially Evernote. Well, after migrating to Org-mode I have a persistent itch caused by the fact that Org doesn't have modification times for outline items, and implementing them in Emacs is a pain. That's one downside of not separating the view from the model. But the creation time is easy to add, in case someone wonders. Similarly, I love having the archive of deleted notes and completed todos: once in a while I need to figure out what the hell I did to some particular items, or I change my mind on some edits. And on bulk moving or copying, I like to keep record of what I moved from where. (cough unlike HN ahem.)
jgalt212 5y ago
You probably should be storing a vector or stack of timestamps. Empty vector/stack is still falsey, and now you have a surface level history of when things changed.
ajuc 5y ago
Why just booleans though? If you have any configuration in database and you don't store when and who changed it - you're doing it wrong.
zzzeek 5y ago
I'm not really down with assigning meaning to NULL. NULL means "unknown", full stop. This is why SQL doesn't like if you compare to NULL using equality, because nothing "equals" NULL. Similarly wouldn't I want to know when a true value became false? This post seemed very strange in that regard. Count me in as storing a boolean as a boolean (or as mentioned elsewhere, an enumeration) and if I need auditing on that, then I will also implement proper auditing columns and/or tables to suit my needs.
- marcosdumay 5y ago
  > NULL means "unknown", full stop. Hum... Null means whatever the data design says it means. We are talking about mathematics here, not religion. Rules don't come written in stone from the havens. Using it as "not applicable" is even way more common than "unknown".
- jeroenhd 5y ago
  The point still stands outside of Javascript, though. Unless you're dealing with very large databases, retrieving a boolean or doing a null check on a timestamp are pretty close operations, especially if you use some kind of frontend to render the data. Storing a boolean expression ("is published", "marked as read", etc.) as a timestamp can still be valuable. It just happens to be entirely equivalent in Javascript, but in normal, typed languages, the same practice can be used to prepare yourself for debugging a broken application or database later. I don't think this is a practice that you should just universally apply everywhere, but it's worth considering in a lot of cases where people generally tend to use booleans.
neo2006 5y ago
What about all the overhead in testing that using a timestamp instead of boolean will introduce? Specially if you end up not needing it
strogonoff 5y ago
I find timestamps as boolean state flags a sort of gateway into event-driven data architecture land. It becomes very enticing to evolve in that direction.
jacques_chester 5y ago
More effective still is bitemporalism, or even just unitemporalism. Let's do a unitemporal table. Instead of `published_at`, you retain the `is_published` boolean field. On the row you have a `valid_time` timestamp range; alternatively `valid_began` and `valid_ended` timestamps if your database doesn't do ranges. The range shows the time during which the fact is true. At creation you set `[now, Infinity)` to indicate that it is true as of the entry. When it becomes false you change the row to `[then, now)`. Outside of that range, the record is false. Notably this lets you encode the switching back and forth of a value over time with no ambiguity about when something began or ceased to be true. More importantly, it's not limited to bools. Any row can be turned into a unitemporal or bitemporal form. If, as others are rightfully suggesting, you should favour enums, not a problem. Strings? Numbers? Complex types? Embedded XML? All fine in the eyes of temporal tables. Some databases even include SQL:2011 temporal table support for "application time" and "system time". I expect whenever it lands in PostgreSQL it'll reach a far wider audience here at HN.
m4lvin 5y ago
... but then please also update your privacy policy that you are storing this additional data about your users! The only data that cannot be leaked or stolen is what you do not store in the first place.
crazygringo 5y ago
This is diametrically opposed to the advice to never store booleans, but rather store enums. Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.) As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields. So no, don't timestamp it. Stick to enumerated values rather than booleans, which will generally be of far greater benefit. If you need to store a log of actions, then create that explicitly. Otherwise, it seems pretty silly and arbitrary to have booleans record their timestamp but not strings, integers, etc. Edit in response to comments below: of course there are times when you need values that aren't mutually exclusive, so obviously you add another column. It's just that you very often do add another state that is mutually exclusive, and so using an enum keeps your data cleaner, more intuitive, and prevents accidental invalid combinations of booleans as well.
- cle 5y ago
  > As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields. In my experience these states are often not mutually exclusive. Boolean encoding is a superset of enum encoding. Additionally, enums in many programming languages are often over-strict and easy to use in a non-forwards-compatible way. Any advice that starts with "never" or "always" is suspect advice IMO. Study your domain, and decide whether you want to lock yourself into a mutually-exclusive state space and deal with consumers who may consume it in a way that prevents adding new states. Sometimes enums make sense, sometimes they don't.
- ralusek 5y ago
  I have often found that multiple booleans are better than enums. processed: true confirmed: false in progress: false pending: false reserved: true It lets you capture more complicated state, such as above where this was processed but never confirmed, but still resulted in a reservation. Maybe you have an admin portal that lets you create reservations without going through the confirmation process, and now the data can capture the difference. But my actual preferred variant, if the tech stack can support it, is to have things like `confirmations` have their own tables, so you can have a `confirmedByUserId` as well as a confirmation timestamp. That way you can instead have something like computed_processed: boolean(has a related entry in processed table) computed_confirmed: boolean(has a related entry in confirmation table) etc
jackcviers3 5y ago
Converting any non boolean type into a boolean is always a lossy compression that will often need to be reversed later. If/else was invented so that you don't need to perform this compression at the variable level. I also don't like nullable fields in my databases. Anything nullable is representable as some form of coproduct - and can therefore be represented as a relationship to an entity with a property that can be joined to for terms of definition.
thih9 5y ago
Note that this doesn’t keep historical values. E.g.: If we convert ‘synced’ boolean to ‘synced_at’ timestamp and if the sync status changes often, we’re only storing the most recent sync timestamp. In some cases this might be insufficient.
coward76 5y ago
Some of us have an audit log that tells when a record has changed. Others just use event sourcing.
subleq 5y ago
I wrote a django model field that does this https://gist.github.com/gavinwahl/17c07335c8dd1b832911 https://gist.github.com/gavinwahl/17c07335c8dd1b832911

307 points by mot2ba 5y ago

I love the drake meme they embeded for sharing the post!

This seems to only really work in languages that allow null variables/timestamps. I wouldn't really want to have to do comparators to the default value of a timestamp.

choeger 5y ago
The author is talking about databases, not programming languages. I do see a different issue, though: The article indeed seems to make no distinction between an absent value and a default timestamp of 0. That limits your database to more or less "now". You cannot really store things about the past. Someone might take such a pattern and fixate it into some kind of library. If then someone else tries to store data from 1970, things can get ... interesting.
- ghego1 5y ago
  That is a very neat and smart improvement!
- kedean 5y ago
  That does assume your database only allows unix-style timestamps. MariaDB, for example, has "datetime", supporting dates between the year 1000 and 9999, distinct from null/zero. Unfortunately, datetime takes 8 bytes vs the 4 for a timestamp.
ShroudedNight 5y ago
> ...default value of a timestamp. Assuming the timestamp represent a change of state in a contemporary application, I would expect 1970-01-01 0:00:00Z (UNIX epoch +0 seconds) to be unambiguous enough (But that's definitely an engineering constraint to maintain awareness of)
samus 5y ago
PostgreSQL has computed columns. Creating one for every field that returns its `IS NOT NULL`-ness would accommodate such programming languages.
nicoburns 5y ago
Languages with Option or Maybe types instead of null will also work fine. So it works in every langauge except Go?
- rsa25519 5y ago
  Yep. And I assume you could use Time.IsZero() for Go https://stackoverflow.com/a/36234533 https://stackoverflow.com/a/36234533

redact207 5y ago

To quote the author: "it depends" If when something happens needs to fold into your business logic then by all means go for it. If you're moreso doing it as an audit then logging out the event with it's context is going to be more useful.

rsa25519 5y ago
> To quote the author: "it depends" Where do you see this? To quote the article I'm reading: "it doesn’t really depend"
- mwill 5y ago
  Not op but in the first paragraph he links to another post of his that is all about saying "it depends" often >https://changelog.com/posts/good-reason-experienced-devs-say-it-depends https://changelog.com/posts/good-reason-experienced-devs-say... I'm assuming op is referencing that (I agree with you here though that in this case he makes a very good argument for "it doesn't really depend")

xlii 5y ago

It's a very neat idea that I'll consider in the future however I have some concerns about it. One thing is about the database design. I remember lecturer proclaiming that models with many nullable relations is: - bad design - performance risk - might mess with indexing I haven't verified this knowledge in many years, to I'm not sure if that point still stands, also in wake of not optimizing pre-mature this might not be an issue. The other thing is introducing of (needless) complexity to the system. It allows to make unwise decisions which otherwise would not be possible if the flag would remain simple boolean and as such stands against KISS system design principles.

diamondo25 5y ago
Yes, nullable relations are harder to work with, especially with ON DELETE CASCADE and checking if another table contains a row based on a nullable column. However, this is about booleans, so they werent used in relations to begin with. The indexing in this case could be a bit less performant but probably neglegable.
madacol 5y ago
The OP doesn't talk about *nullable relations*, which I agree is a sign of bad desing

davnicwil 5y ago

This is right. I'm a big fan of this sort of embedded audit metadata wherever it makes sense. I do wonder when doing stuff like this though, if this really shouldn't be something that the database gives you for free. I read a few years ago about 'fact based' event stream style databases which store your data as a stream of time ordered ops that can later serve as an audit log, but can be used for even more powerful things such as backups at any point in time, debugging at any point in time, etc. For practical reasons (i.e. just picking a standard postgres setup to get stuff done) I've never dug into any of these systems or played around with them. Anyone know what the latest and greatest is here? Is there anything I can install on top of postgres to give me this functionality today?

terhechte 5y ago
I think Datomic[1] works like that Here's the relevant quote: "Understand how and when changes were made. Datomic stores all history, and lets you query against any point in time. Learn More" [1] https://www.datomic.com/ https://www.datomic.com/
oftenwrong 5y ago
This is often called "event sourcing". The other commenter mentions Datomic, which probably is the 'latest and greatest' form of event sourcing (I've never used it). https://vvvvalvalval.github.io/posts/2018-11-12-datomic-event-sourcing-without-the-hassle.html https://vvvvalvalval.github.io/posts/2018-11-12-datomic-even... If you want something built on postgres, I don't have any specific recommendations, but you can build a simple event sourcing system yourself. I worked somewhere that used event sourcing on postgres, and the core log was basically just a table with aggregate IDs, event sequence numbers, and a JSONB column for the event payload. I recommend starting with just one part of your application if you're going to adopt event sourcing. You will quickly find that there are a lot of new considerations and pitfalls that you don't have with traditional RDBMS usage. Overall, event sourcing is hard to get right, so you should consider the trade-offs carefully. There are easier ways to get audit logs, for example: https://www.pgaudit.org/ https://www.pgaudit.org/ I will also recommend this as a way to start to understand the tricky aspects of event sourcing: https://leanpub.com/esversioning https://leanpub.com/esversioning
5y ago

ghego1 5y ago

TLDR don't use a boolean, leave the field empty (NULL) and set a timestamp when should be true. To be honest a tldr isn't actually needed, the post is both very concise, straight to the point, and convincing. As per the languages I use more often to query DBs (TS, JS, PHP, Python) I don't see any downside. Evaluating if a variable is empty or not, or it's type, is not "bad", compared to evaluating if a variable is true or false. Even in TypeScript in strict mode, evaluating if a variable with type number is empty or not will result in validly typed code, without any noticeable difference compared to evaluating a variable with type boolean.

lars512 5y ago

Sitting in the data science seat, downstream from the application development and trying to gain insights on production data, I completely second this approach. It just gives you more to go on and more ways to validate whether something unexpected is going on.

rurban 5y ago

You might as well version it. Timestamps are relative if different machines set it. Versions are primitive (ie atomic) and always absolute, and usually the best way to treat concurrency.

alephnan 5y ago

This reminds me of tricks in JavaScript such as using !! to convert values to Boolean: it’s clever, “idiomatic” and save a few characters, but it’s not self explanatory to someone not familiar with the idioms

rsa25519 5y ago
Fortunately `published_at == null` is much more intuitive that using `!!` to typecast.

jchw 5y ago

Indeed, this is a bit of wisdom I first encountered when playing with Django and seeing others do it. You can still see it in a couple of the many soft-delete packages available on PyPI, in the form of deleted or deleted_at fields with DateTimeFields. (Though admittedly, I'm pretty out of date on Django these days.) (Though it is worth noting that you sort-of get this for free if you implement a scheme with 'revisioned' data, or a database that simply has that as a feature. But, it's still useful to just have this additional bit of information handy, if nothing else.)

vbsteven 5y ago
I first picked it up from Rails many years ago. Now almost every SQL table I create has created_at, updated_at and possibly deleted_at or archived_at fields. Many frameworks or ORMs have support for automatically setting updated_at. And it might even be done at SQL level but I’m not 100% sure on that.
- sopooneo 5y ago
  I’ve tried that approach of moving users to a “deleted_users” table but ran into the problem of losing the foreign key references to any records pointing at the user.
dsego 5y ago
I've had experience with the soft delete in laravel and I'm not fond of it. Because we had a separate dashboard with manual SQL reports without an ORM to magically filter out all the deleted_at rows. And it gets tedious to remember to filter out the rows with deleted_at timestamps. I would like to see the soft-delete implemented differently, say a second trashcan table for each active table, e.g. users_archive, and the delete operation would move the row there.
- sdfhbdf 5y ago
  Or you could have views with `deleted_at` filtered out: https://learnsql.com/blog/sql-view/ https://learnsql.com/blog/sql-view/

jpswade 5y ago

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.

deleted 5y ago

[deleted]

deleted 5y ago

[deleted]

lazyasciiart 5y ago

Am I missing something, or does this assume I never care about the timestamp for turning a value off?

globular-toast 5y ago
Yeah, I don't get that either. This only seems to work in very specific cases, like where the boolean starts off false, becomes true and can never be false again. So like "user viewed homepage". That seems like a tiny subset of what Booleans are used for but it's presented as being the one, obvious use case.

davidverhasselt 5y ago

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).

jwr 5y ago
That depends on the language. In Clojure and ClojureScript, for example, distinguishing between nil and false is not a problem at all.
- davidverhasselt 5y ago
  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.
johnchristopher 5y ago
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).
joosters 5y ago
Perl got this right decades ago with its 'undefined' status for unset variables, so you can tell the difference between false and undef

nickjj 5y ago

Indeed, I do this pretty much all the time too. One common'ish example not mentioned in the article is storing whether or not a user is active. Storing "is_active" as a boolean makes sense but switching that to "deactivated_at" gives you so much more information.

kijin 5y ago
last_active_at also makes sense, especially if you'd like some flexibility in deciding what to do with users who have been inactive for a certain amount of time.

koff3 5y ago

I would not do this unless there is a use case. For analysis purposes you can always read the whole change history from audit logs. The solution also only gives you the timestamp when something was set, but not when it was unset.

durnygbur 5y ago

let published_at = new Date() if (published_at) console.log("it's true!") if (!published_at) console.log("it's false!") As a FE dev I haven't had workplace with a codebase allowing above for at least 5 years. No one even asks "shall we us JS ot TS?". Strictly enforced static typing all over. It's not that I like it, just no one asks me.

woutr_be 5y ago
What's wrong with this? Checking if a variable is defined/null is not exactly uncommon?
- kaeruct 5y ago
  In JS, if (!variable) ... coerces the type to boolean, so anything falsy (0, null, undefined, empty string, etc...) will become true. For example if you have a timestamp of 0, it will be counted as false (but is defined and definitely not null)
  - woutr_be 5y ago
    I understand that. But how would strictly enforced typing solve this? Especially when using TypeScript. An incorrect value being given isn’t going to be caught by the compiler.

ximm 5y ago

One important downside: Data protection. This approach of "store it now in case you might need it later" is in direct violation of the principle of data minimisation in GDPR.

progre 5y ago
GDPR only applies to personal data though? Like you can't store gender info "in case it's usefull later". I really don't see how a timestamp can be used in that way.
- globular-toast 5y ago
  Well, since gender is mutable now, I suppose you can't store the date the gender changed "just in case". But yeah, unless you're dealing with people, this doesn't apply.
imperistan 5y ago
I think thats only the case when it the data can be used to (help) identify a specific person
- ximm 5y ago
  No, not exactly. It is the case when this data is related to a single person. E.g. "has this person subscribed to my newsletter" vs "whan has this person subscribed to my newsletter".

bob1029 5y ago

I don't like this. Yes, you can alias the true/false fact to null/non-null datetime value, but this is missing the point of domain modeling. The immediate impact of this decision is probably negligible as long as you did not need to store a nullable boolean fact, as opposed to a non-nullable boolean fact. The broader impact of this decision is that you have endorsed a policy of assuming how things will be used in the future and are not interested in a 100% authentic modeling of the problem domain anymore. In a larger team, these "well wouldn't it be nice if..." design decisions are extremely subjective and can beg many further questions that wind up being distracting. Discipline becomes very important as the complexity of your software project increases. It is easy to collapse the whole house of cards over little incremental things like this. You have to have a stricter policy across the entire team of saying things like "booleans go in as booleans, if you want who, when, why, those are 3 new facts next to the boolean".

danielheath 5y ago
I get that there’s a YAGNI aspect to it, but I don’t buy the argument that a timestamp adds complexity over a boolean.
- bob1029 5y ago
  This all provokes confusion over 2 different types of facts: A) Knowledge of when a specific event occurred. B) If something is true or not. To use cases like "logged_in_at" as the example for why booleans shouldn't be used is essentially a strawman argument. There are many situations in which a boolean fact does not occur in the time domain or have any possible value. Knowledge of certain facts in certain problem domains can be viewed as timeless even if they did come into being at a discrete point in time. For example, regulatory facts that govern entire industries. You probably never care when a specific regulation started to matter for a situation, just that it does or not. All these timestamps would do is confuse downstream users and bloat extracts of data. The biggest problem of all is this statement: > 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. There is nuance to this problem. A and B are both perfectly valid cases and each have their own representations that make the most sense. The discipline is in identifying these cases appropriately and using the correct tool for the job.

hsbauauvhabzb 5y ago

Is there any concept in database engines of an audit trail which logs previous value and a time stamp? I’ve seen the concept using triggers, but never seen an engine native solution

uyt 5y ago
Maybe you're thinking of "event sourcing"?
globular-toast 5y ago
You can implement an append-only database, where each record is a snapshot of the latest version and a timestamp of when it was updated.

a_c 5y ago

In my experience I would even say having a boolean field in your table is a bad idea, with rare exceptions. e.g. - You may want proper state transition rather than having 4 booleans each representing one state - You may want to normalize the boolean with other metadata (timestamp, as OP suggestion, and author) into separate table,

madsbuch 5y ago

If one does this please be appropriate about semantics / naming. Ie. Don't put a timestamp in a variable called `isTermsAccepted`. Rename it into `termsAcceptedAt`. Generally accept that timestamps and booleans are not the same, but the truth value can be derived from the timestamp.

bostik 5y ago
> truth value can be derived from the timestamp Python used to disagree with you: https://lwn.net/Articles/590299/ https://lwn.net/Articles/590299/ (and the bug report with discussion spanning a couple of years: https://bugs.python.org/issue13936 https://bugs.python.org/issue13936)
- raverbashing 5y ago
  Yeah, glad this was fixed > If I had to do it over again I would definitely never make a time value "falsy".

bachmeier 5y ago

If you're going this route, it's hard to understand why you wouldn't just store all the information you want explicitly in a string. "true [timestamp]" "false [timestamp]" "unset [timestamp]" That's more information than described in the article and it's easier for future you to understand what's going on, without implicit assumptions on the meaning of an undefined variable. Furthermore, you can keep a complete record of all status changes if that's what you want: "false [timestamp] true [timestamp] false [timestamp] true [timestamp]"

vbsteven 5y ago
This makes querying harder. You cannot use “is not null” or “= true” for where clauses with this. You’ll need to parse the string value. When going the explicit route I would recommend an is_archived (nullable) bool column combined with an archived_at timestamp column.
- bachmeier 5y ago
  If you're using sqlite, for instance, you can call the substr function (I was sticking with the article's constraint to not add a new column). There is one thing in favor of storing the full history in a single string - you might not query the full history very often, and you can keep a lot of information around without adding another table. I've occasionally stored the full history of objects (short notes mostly) in an sqlite database as a string in json form. Pretty convenient if you're keeping it there just in case you want to go back in time and don't make a large number of changes.
- d0100 5y ago
  At this point just create a archived_resources table where you store the resource id, timestamp and user id

goofballlogic 5y ago

Exception i encountered recently was needing ternary state boolean. a set of flags where true and false indicates outcome and undefined indicates "not yet processed". I suppose some sort of 0 timestamp could work but... No, I think I'll stick with boolean.

kown7 5y ago

In this case, why not go straight to append-only databases where every entry has a timestamp? That will be _the_ audit log in your database.

berkes 5y ago
This was my thought too. Timestamps are a usefull trick. But also one that allows you to postpone what the domain is really asking: to store a log of events. Maybe even as primary source (aka event sourced).

victorp13 5y ago

As the storage cost is very low, why not use both? Not trying to be obtuse, but I genuinely typically have separate columns in my schema design for booleans and the timestamps of these events flipping from false to true (published, edited). Come to think of it: These events warrant saving them to a separate table altogether; booleans represent a current state - there may be 1:n events like multiple edits.

antender 5y ago

Also, from the point of query optimisation this is a really bad idea. Usually you DO actually care about size of fields in SQL databases, because something like BOOLEAN is usually stored as single byte (or bit in a bitfield) vs 4 bytes or even 8 in case of timestamp. This not only multiplies on disk usage by at least 4 times, but also makes ALL indexes using this field way bigger. Also boolean indexes can be compressed (or stored as bitmaps), while timestamp indexes contain lots of unique values, so they can't be. This is also the reason why serial IDs are way better than UUIDs for internal IDs.

traceroute66 5y ago
> This is also the reason why serial IDs are way better than UUIDs for internal IDs. There are three core problems with that: a) Serial IDs are a nightmare for database merges, clustering or anything like that b) Serial IDs won't scale c) Serial IDs require management, whilst UUIDs can be produced anywhere (in DB, in frontend etc) There is the KSUID[1] if people want a time-sortable thing that is near-enough to a UUID. [1] https://github.com/segmentio/ksuid https://github.com/segmentio/ksuid
- hk1337 5y ago
  You can do both. Use the UUIDs where you need the scalability and serial where you don’t.
- marcosdumay 5y ago
  > Serial IDs won't scale You mean scaling into several machines? Yes, they do scale. Nothing requires that the values are always increasing and have no holes, so you can slice and cluster them at will (and many DBMS do exactly that).
  - gmfawcett 5y ago
    I'm confused? Serial means "always increasing and having no holes" -- it's one thing after the other. What you're arguing for sounds like just IDs, not serial IDs.

andix 5y ago

I don’t like NULLs. Nullables always get back you in ways you would never expect. That’s why I won’t use a tip like that.

vbsteven 5y ago
The problem can be managed somewhat depending on the language and tooling. I have used this advice for archived_at or deleted_at before. In the database it’s a NULLable timestamp column. In application code I would map this field to an Instant? (Kotlin) and a computed isArchived val that checks for presence of the archivedAt field.

usr1106 5y ago

When reading this I miss a HN feature "upvote as a falsehood to be aware of".

MrOxiMoron 5y ago

except when it is actually a nullable Boolean and there is a big difference between the 3 states

codeulike 5y ago

So we're trying to come up with reasons why this is bad advice? uhhhhhh ... something something far future something Y10K problem something.

throw149102 5y ago

This makes me worry about clock skew and asynchronous code in general. For example, a timestamp might lead you to believe things happened in a different order than they did because the timestamping process isn't atomic.

dvfjsdhgfv 5y ago

But these are two very different use cases and just using one for another won't work in many scenarios or will make thing less efficient. It is much, much better to leave the boolean intact for the reasons other people explained and just add another column with the date if you actually need it. This will give you more flexibility while keeping things in order.

defanor 5y ago

As mentioned in other comments, there's a bunch of drawbacks with this approach, but there's a more general term "boolean blindness" [1], which is usually applied to programs (not databases). I find it useful to avoid using booleans where more descriptive types make sense (and can be used), as well as not throwing information away when it may be needed still, but to a reasonable extent. [1] https://www.cs.cmu.edu/~15150/previous-semesters/2012-spring/resources/lectures/09.pdf https://www.cs.cmu.edu/~15150/previous-semesters/2012-spring...

isuckatcoding 5y ago

I don’t see the benefit. This is what changelogs are for.

aasasd 5y ago

I have about the same sentiment in regard to personal notekeeping, and do in general care about preserving metadata. Many times I have consulted the date when I created a note or, say, an entry in the password manager—or when I last changed it. That may inform my decision on what to do with the note next, or at least allows me to contemplate how much is not done in the passing years and how much is yet to not do. ‘Remember The Milk’ and Evernote make it pretty nice and easy by keeping the dates and some other info. (Though of course there's a gotcha that RTM's Android app forgets to implement the display of this metadata.) Not that I recommend these apps currently, especially Evernote. Well, after migrating to Org-mode I have a persistent itch caused by the fact that Org doesn't have modification times for outline items, and implementing them in Emacs is a pain. That's one downside of not separating the view from the model. But the creation time is easy to add, in case someone wonders. Similarly, I love having the archive of deleted notes and completed todos: once in a while I need to figure out what the hell I did to some particular items, or I change my mind on some edits. And on bulk moving or copying, I like to keep record of what I moved from where. (cough unlike HN ahem.)

jgalt212 5y ago

You probably should be storing a vector or stack of timestamps. Empty vector/stack is still falsey, and now you have a surface level history of when things changed.

ajuc 5y ago

Why just booleans though? If you have any configuration in database and you don't store when and who changed it - you're doing it wrong.

zzzeek 5y ago

I'm not really down with assigning meaning to NULL. NULL means "unknown", full stop. This is why SQL doesn't like if you compare to NULL using equality, because nothing "equals" NULL. Similarly wouldn't I want to know when a true value became false? This post seemed very strange in that regard. Count me in as storing a boolean as a boolean (or as mentioned elsewhere, an enumeration) and if I need auditing on that, then I will also implement proper auditing columns and/or tables to suit my needs.

marcosdumay 5y ago
> NULL means "unknown", full stop. Hum... Null means whatever the data design says it means. We are talking about mathematics here, not religion. Rules don't come written in stone from the havens. Using it as "not applicable" is even way more common than "unknown".
jeroenhd 5y ago
The point still stands outside of Javascript, though. Unless you're dealing with very large databases, retrieving a boolean or doing a null check on a timestamp are pretty close operations, especially if you use some kind of frontend to render the data. Storing a boolean expression ("is published", "marked as read", etc.) as a timestamp can still be valuable. It just happens to be entirely equivalent in Javascript, but in normal, typed languages, the same practice can be used to prepare yourself for debugging a broken application or database later. I don't think this is a practice that you should just universally apply everywhere, but it's worth considering in a lot of cases where people generally tend to use booleans.

neo2006 5y ago

What about all the overhead in testing that using a timestamp instead of boolean will introduce? Specially if you end up not needing it

strogonoff 5y ago

I find timestamps as boolean state flags a sort of gateway into event-driven data architecture land. It becomes very enticing to evolve in that direction.

jacques_chester 5y ago

More effective still is bitemporalism, or even just unitemporalism. Let's do a unitemporal table. Instead of `published_at`, you retain the `is_published` boolean field. On the row you have a `valid_time` timestamp range; alternatively `valid_began` and `valid_ended` timestamps if your database doesn't do ranges. The range shows the time during which the fact is true. At creation you set `[now, Infinity)` to indicate that it is true as of the entry. When it becomes false you change the row to `[then, now)`. Outside of that range, the record is false. Notably this lets you encode the switching back and forth of a value over time with no ambiguity about when something began or ceased to be true. More importantly, it's not limited to bools. Any row can be turned into a unitemporal or bitemporal form. If, as others are rightfully suggesting, you should favour enums, not a problem. Strings? Numbers? Complex types? Embedded XML? All fine in the eyes of temporal tables. Some databases even include SQL:2011 temporal table support for "application time" and "system time". I expect whenever it lands in PostgreSQL it'll reach a far wider audience here at HN.

m4lvin 5y ago

... but then please also update your privacy policy that you are storing this additional data about your users! The only data that cannot be leaked or stolen is what you do not store in the first place.

crazygringo 5y ago

This is diametrically opposed to the advice to never store booleans, but rather store enums. Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.) As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields. So no, don't timestamp it. Stick to enumerated values rather than booleans, which will generally be of far greater benefit. If you need to store a log of actions, then create that explicitly. Otherwise, it seems pretty silly and arbitrary to have booleans record their timestamp but not strings, integers, etc. Edit in response to comments below: of course there are times when you need values that aren't mutually exclusive, so obviously you add another column. It's just that you very often do add another state that is mutually exclusive, and so using an enum keeps your data cleaner, more intuitive, and prevents accidental invalid combinations of booleans as well.

cle 5y ago
> As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields. In my experience these states are often not mutually exclusive. Boolean encoding is a superset of enum encoding. Additionally, enums in many programming languages are often over-strict and easy to use in a non-forwards-compatible way. Any advice that starts with "never" or "always" is suspect advice IMO. Study your domain, and decide whether you want to lock yourself into a mutually-exclusive state space and deal with consumers who may consume it in a way that prevents adding new states. Sometimes enums make sense, sometimes they don't.
ralusek 5y ago
I have often found that multiple booleans are better than enums. processed: true confirmed: false in progress: false pending: false reserved: true It lets you capture more complicated state, such as above where this was processed but never confirmed, but still resulted in a reservation. Maybe you have an admin portal that lets you create reservations without going through the confirmation process, and now the data can capture the difference. But my actual preferred variant, if the tech stack can support it, is to have things like `confirmations` have their own tables, so you can have a `confirmedByUserId` as well as a confirmation timestamp. That way you can instead have something like computed_processed: boolean(has a related entry in processed table) computed_confirmed: boolean(has a related entry in confirmation table) etc

jackcviers3 5y ago

Converting any non boolean type into a boolean is always a lossy compression that will often need to be reversed later. If/else was invented so that you don't need to perform this compression at the variable level. I also don't like nullable fields in my databases. Anything nullable is representable as some form of coproduct - and can therefore be represented as a relationship to an entity with a property that can be joined to for terms of definition.

thih9 5y ago

Note that this doesn’t keep historical values. E.g.: If we convert ‘synced’ boolean to ‘synced_at’ timestamp and if the sync status changes often, we’re only storing the most recent sync timestamp. In some cases this might be insufficient.

coward76 5y ago

Some of us have an audit log that tells when a record has changed. Others just use event sourcing.

subleq 5y ago

I wrote a django model field that does this https://gist.github.com/gavinwahl/17c07335c8dd1b832911 https://gist.github.com/gavinwahl/17c07335c8dd1b832911