You might as well timestamp it
changelog.com
changelog.com
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).
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).
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.
Fields where there isn't an discrete event don't work. E.g. is_dog_owner can become adopted_dog_at, but is_dog_lover can't become loved_dogs_at.
I'd actually even argue that this is not storing state directly. You derive state from knowing an event has occured in the past: deleted_at (event) => is_deleted (state).
* 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
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.
- "probably"
- "maybe"
- ""
Reference: https://developer.mozilla.org/en-US/docs/Web/API/HTMLMediaEl...
AFAIR I just said fuck it and made playback as permissive as possible (i.e., only prevent media playback if canPlayType returned ""). I don't know how advisable that is but the bug got off my back anyway.
I dunno what makes this so difficult, why we can't get at least a definite "yes" even in 2021.
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.
The fact that an equality check can be made by running a comparison function is useful, but that's not all the method does.
For other methods in much C code, a common mindset is that a method returning a value will return the error code, with error code details in errno. The error code for success is 0, which is fitting of course.
Had C implemented booleans, this problem would never have been a problem, because if(int) wouldn't have been a legal expression, but sadly booleans are implemented as integers in the language instead. I strongly dislike languages that do allow implicit casting from integers and such to a boolean, if(var!=0) is much more readable because of the explicit boolean expression.
Use enums (or any equivalent) for states that are non-boolean.
I find the idea of 'creating unknown values' to be self-contradictory. I find it much more logical to define a PartialFoo containing only the parts we know up-front, and a fill in the rest later using a function PartialFoo -> Foo.
If we need multiple steps, we can put some Optional fields in the PartialFoo, to avoid lots of intermediate types. This is still annoying, but it keeps all of the 'intialisation headaches' separate from the Foo type itself; so code dealing with Foo doesn't have to care about null checks, missing fields, etc.
You juat described the purpose of SQL's NULL.
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...
[1] https://en.wikipedia.org/wiki/Year_2038_problem
> The latest time since 1 January 1970 that can be stored using a signed 32-bit integer is 03:14:07 on Tuesday, 19 January 2038
> MySQL database's built-in functions like UNIX_TIMESTAMP() will return 0 after 03:14:07 UTC on 19 January 2038
Positive = timestamp
-1 = false
0 = null
> 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.
Just use an enum, it's much more expressive.
Edit: grammar
Meanwhile if you see a `None` value you know for a fact that it was set by your software and if you actually encounter a `null` you know that something went horribly wrong.
Strong type systems and a few overheads in favor of better bug detection/prevention are popular for a reason.
In which case null would either mean the value on the recordin the database is null, or that that for whatever reasons that parameter was not read from the database.
You will need two types of null.
All of this goes away with an enum or similar solution.
You can decompose the nullable boolean in the database when you read it , but again your task would be easier to treat it like an enum.
Ditto for database design too
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
>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)
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).
Databases have the concept of sequences, that are closer to your definition, but make no promises about holes (they normally don't generate holes by themselves, but there is no way to guarantee you won't lose numbers upon usage). It is common to use sequences to feed serial IDs, but not all DBMS do that and it's not a requirement in any way.
*To fit into a 64-bit number space, Snowflake IDs and its derivatives require coordination to avoid collisions, which significantly increases the deployment complexity and operational burden.*
I would argue that doubling your key-space in the case of KSUIDs may have just as much of an impact as coordinating node ids in the case of snowflake (and in fact the snowflake technique only runs into coordination problems when you're at pretty extreme scale).
As for the trouble, I agree, it's a bit more involved.
Using just the nullable timestamp only lets you index on one of the boolean states: an index on (ts, col2) works for WHERE ts IS NULL ORDER BY col2, but WHERE ts IS NOT NULL ORDER BY col2 doesn't work well (without using generated boolean, etc).
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.
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.
processed: true
confirmed: false
in progress: false
pending: false
reserved: true
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)
etcIn order to avoid impossible states, I'll rely on something like hooks or triggers to make sure that setting one value also updates all dependent values.
In other words, setting processed to true will trigger something which always sets in_progress and pending to false.
It's not at all uncommon that I will have chosen an enum for a situation in which I thought I was dealing with a finite state machine, only to unearth new domain contingencies that made me realize that I actually need a more nuanced and flexible model. This is why I prefer booleans, particularly the computed/derived booleans when possible (as I mentioned in my original comment). I'd rather have a model capable of capturing the actual complexity of the state than trying to force a finite state machine that might result in a loss of information, even if that means that I'm forced to rely on declarative hooks/triggers to ensure data integrity.
JSON, while it has a very limited set of built-in types and (outside of add-ons like JSON-schema) no type definition mechanism, isn’t “stringly-typed”.
Status is an information you display, but it's not the actual data. Status fields that contains multiple information and features in a single value creates confusing and complex logic for no good reason.
Booleans are way better to store data, and each one can bear a single different concern.
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".
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.
Of course there are nuances and exceptions to every generalization. OP seems to be saying that most boolean flags we care about on a day-to-day basis can benefit from being placed in a time domain. If you're aware of an exception, you can just point it out. There's no need to dismiss the entire argument.
Returning to your example, you probably do care when a certain regulation started to apply to a certain industry, because factories built and products sold before that date may not comply, and may not even be required to comply, with said regulation. You probably also care when it stops applying. Laws are not rules of nature; they change all the time and often come with expiry dates. If you store every regulation with a few timestamps, it's going to be pretty straightforward to find out which entities need to be in compliance but currently aren't, etc.
Besides, the cost of keeping a few unnecessary integer columns around in your database is often negligible compared to the cost of updating the schema and notifying everyone who uses your API several years down the road when you realize that you need it after all. Disk is cheap. RAM is cheap. CPU is cheap. Updating enterprise software is not.
- "Jan 23, 2020"
Sure, I mean. It's Javascript after all.
Any trick you play with a fixed number of scalar columns will only let you access the timestamp of the last change of the same type. This won't be particularly useful when there's an edit war among moderators who unpublish and republish the same thing over and over.
A model is an approximation. There is no such thing as "100% authentic modeling" for anything non-trivial, and insisting on it grows models that aren't particularly useful. They might _seem_ simple at first glance because of their "purity", but a) they're not _actually 100% accurate, and b) are usually very fragile on revision, becoming, ironically, quite complex.
> "booleans go in as booleans, if you want who, when, why, those are 3 new facts next to the boolean".
...you have now replicated the information 3 times in a way that includes risk of going out of sync. Congratulations.
If you truly believe this to be the case, then you have not tread far enough into the forest of SQL, 3NF, BCNF, and the relational calculus. It is possible to use math to prove that a problem domain is modeled appropriately. With SQL and views, you can construct extremely high-order representations of domains that would otherwise be viewed as pure magic by any outside onlookers. The only way any of this becomes possible is if you have solid foundations and the courage to produce exceptionally clean models.
Yes, you will definitely screw it up a few times. We started over 4-5 times. Plan to iterate. Start with your domain modeling. You can do this shit in excel. No one gets too salty when you have to throw away a spreadsheet.
...no it doesn't. I've deleted the claim that you don't know what a model is from the previous comment, but now it's clear: you don't know what a model is.
> If you truly believe this to be the case, then you have not tread far enough into the forest of SQL, 3NF, BCNF, and the relational calculus.
...you're mixing models with mathematical theories in there, and none of these are "100% authentic models" of anything. Unless by authentic you mean "not copied", I suppose.
> It is possible to use math to prove that a problem domain is modeled appropriately.
...appropriately doesn't mean 100% accurate (which is _I think_ the word you were looking for). That's not how models work.
> The only way any of this becomes possible is if you have solid foundations and the courage to produce exceptionally clean models.
"Clean" doesn't imply "accurate" at all. It does sometimes help with being useful or easy to implement, but it usually _sacrifices_ accuracy. Like the two main and often contrary properties of models are "accuracy" and "usefulness." That's, like, first five pages of any epistemology handbook.
> You can do this shit in excel.
If you start in excel, you ain't getting anywhere. And this is not, by the way, a dunk on Excel.
At the end of the day, as far as computers go what is actually the difference between (TRUE, FALSE) and (MMDDYYTTTTTT/Null) - assuming your environment and storage engine support it.
You’re encoding far more meaning by using a date than using TRUE. Instead of two columns you now have one.
You can have a rock solid system without any room for misinterpretation and confusion while also using this approach. I do it all the time.
The date something occurs in the real-world can be different from when the data entry was done. If you blindly convert Booleans into timestamps without that differentiation, you’ll end up with misleading data.
Generally accept that timestamps and booleans are not the same, but the truth value can be derived from the timestamp.
Python used to disagree with you: https://lwn.net/Articles/590299/ (and the bug report with discussion spanning a couple of years: https://bugs.python.org/issue13936)
> If I had to do it over again I would definitely never make a time value "falsy".
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.
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?
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"
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/
I will also recommend this as a way to start to understand the tricky aspects of event sourcing: https://leanpub.com/esversioning
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.
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.
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?
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.
So yes, this only assumes you care about when the on state happened and you don’t care about the history.
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.
Turning it off too but we don't have a timestamp :/.
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.
[1] https://www.cs.cmu.edu/~15150/previous-semesters/2012-spring...
(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.)
"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]"
When going the explicit route I would recommend an is_archived (nullable) bool column combined with an archived_at timestamp 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.
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,
That will be _the_ audit log in your database.
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).
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.
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.
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".
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.
Unfortunately, datetime takes 8 bytes vs the 4 for 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)
‘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.)
That’s why I won’t use a tip like that.
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.
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.
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.
let published_at = new Date()
if (published_at) console.log("it's true!")
if (!published_at) console.log("it's false!")
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.
Where do you see this? To quote the article I'm reading: "it doesn’t really depend"
>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")
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.
The only data that cannot be leaked or stolen is what you do not store in the first place.