I love the drake meme they embeded for sharing the post!
You might as well timestamp it
151–160 of 205 posts
Re: You might as well timestamp it
#152This 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 t…
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-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
Re: You might as well timestamp it
#153There 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…
I'm struggling to think of an example following the OP's example - discrete events that may have not occured yet - where you'd need to differentiate between a certain `false` and an uncertain `null`.
A question like "Do you have any allergies?" probably requires ternary logic: yes/no/unset
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.
Re: You might as well timestamp it
#154Earlier quoted context omitted.
What you describe calls for an Enum, not a nullable boolean which is just another way of passing a hardcoded magic value carrying an implicit meaning. > 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 c…
Your statement is false. Many use cases call for optional bools. The great thing is there is no implicit meaning - it's True, False or Unset. It is true that nulls can be abused but in this case, far more elegant than an enum.
There is no way for you to make sure that `null` was "set" intentionally and not due to a bug or other kind of failure.
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.
Re: You might as well timestamp it
#155There 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…
0 for never set, timestamp for set time, negate the timestamp for unset time
dates between October 4th and 15th 1582 for special values
Re: You might as well timestamp it
#156More 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.
Re: You might as well timestamp it
#157Earlier quoted context omitted.
If you are using a nullable bool, you do not need a fourth state. True, False, Unset. Very useful in a lot of cases. It's actually not ugly but very elegant.
I don't think it's elegant at all. Especially in SQL dialects where you need to check null state separately from false. So much bug potential...
With an enum you’d have to check the unset state separate as well.
Re: You might as well timestamp it
#158Earlier quoted context omitted.
Also known as *bool, "Maybe Bool", "Boolean?", "Optional " etc.
yeah, but if you really need to store 3 states, why not make 1 first-class concept instead of awkwardly combining two?
Because a nullable bool has literally the same semantics as Maybe. Why invent it again but in your app this time?
Re: You might as well timestamp it
#159... 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.
Re: You might as well timestamp it
#160Earlier quoted context omitted.
What you describe calls for an Enum, not a nullable boolean which is just another way of passing a hardcoded magic value carrying an implicit meaning. > 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 c…
Your statement is false. Many use cases call for optional bools. The great thing is there is no implicit meaning - it's True, False or Unset. It is true that nulls can be abused but in this case, far more elegant than an enum.
[deleted]