TE
科技回声
首页24小时热榜最新最佳问答展示工作
GitHubTwitter
首页

科技回声

基于 Next.js 构建的科技新闻平台,提供全球科技新闻和讨论内容。

GitHubTwitter

首页

首页最新最佳问答展示工作

资源链接

HackerNews API原版 HackerNewsNext.js

© 2025 科技回声. 版权所有。

You might as well timestamp it

307 点作者 mot2ba大约 4 年前

47 条评论

davidverhasselt大约 4 年前
There is a downside which I&#x27;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 &quot;null&quot; value because that equals `false` in timestamp-as-boolean).<p>Having a distinction between `null` and `false` can be handy for values that are optional or have a dynamic default. If it&#x27;s `null` you know it is not explicitly set and could use a fallback value. If it&#x27;s false you know it&#x27;s explicitly set as false.<p>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.<p>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&#x27;s value.<p>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&#x27;d want a boolean instead of a timestamp. Ironically OP missed this nuance (or they&#x27;ll pull a no-true-scotsman).
评论 #26924112 未加载
评论 #26923664 未加载
评论 #26923757 未加载
评论 #26925823 未加载
评论 #26925844 未加载
评论 #26924038 未加载
评论 #26923398 未加载
评论 #26924964 未加载
评论 #26923468 未加载
评论 #26923286 未加载
评论 #26923314 未加载
评论 #26923295 未加载
评论 #26923284 未加载
评论 #26928189 未加载
评论 #26925189 未加载
评论 #26925184 未加载
评论 #26923739 未加载
antender大约 4 年前
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&#x27;t be. This is also the reason why serial IDs are way better than UUIDs for internal IDs.
评论 #26923731 未加载
评论 #26923895 未加载
评论 #26924605 未加载
评论 #26924335 未加载
crazygringo大约 4 年前
This is diametrically opposed to the advice to never store booleans, but rather store enums.<p>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 &quot;reserved&quot;, &quot;pending&quot;, &quot;in progress&quot;, &quot;confirmed&quot;, &quot;processed&quot;, etc.)<p>As long as these states are mutually exclusive, it&#x27;s far more elegant to add another enum value rather than new fields.<p>So no, don&#x27;t timestamp it. Stick to enumerated values rather than booleans, which will generally be of far greater benefit.<p>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.<p>Edit in response to comments below: of course there are times when you need values that <i>aren&#x27;t</i> mutually exclusive, so obviously you add another column. It&#x27;s just that you very often <i>do</i> add another state that <i>is</i> mutually exclusive, and so using an enum keeps your data cleaner, more intuitive, and prevents accidental invalid combinations of booleans as well.
评论 #26926070 未加载
评论 #26926177 未加载
评论 #26926269 未加载
评论 #26926186 未加载
bob1029大约 4 年前
I don&#x27;t like this. Yes, you can alias the true&#x2F;false fact to null&#x2F;non-null datetime value, but this is missing the point of domain modeling.<p>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.<p>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 &quot;well wouldn&#x27;t it be nice if...&quot; design decisions are <i>extremely</i> subjective and can beg many further questions that wind up being distracting.<p>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 &quot;booleans go in as booleans, if you want who, when, why, those are 3 new facts next to the boolean&quot;.
评论 #26923457 未加载
评论 #26923440 未加载
评论 #26923473 未加载
评论 #26924084 未加载
评论 #26925058 未加载
madsbuch大约 4 年前
If one does this <i>please</i> be appropriate about semantics &#x2F; naming. Ie. Don&#x27;t put a timestamp in a variable called `isTermsAccepted`. Rename it into `termsAcceptedAt`.<p>Generally accept that timestamps and booleans are not the same, but the truth value can be derived from the timestamp.
评论 #26923474 未加载
jacques_chester大约 4 年前
More effective still is <i>bitemporalism</i>, or even just unitemporalism.<p>Let&#x27;s do a unitemporal table. Instead of `published_at`, you retain the `is_published` boolean field. On the <i>row</i> you have a `valid_time` timestamp range; alternatively `valid_began` and `valid_ended` timestamps if your database doesn&#x27;t do ranges.<p>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.<p>Notably this lets you encode the switching back and forth of a value over time with no ambiguity about when something began <i>or ceased</i> to be true. More importantly, it&#x27;s not limited to bools. <i>Any</i> 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.<p>Some databases even include SQL:2011 temporal table support for &quot;application time&quot; and &quot;system time&quot;. I expect whenever it lands in PostgreSQL it&#x27;ll reach a far wider audience here at HN.
davnicwil大约 4 年前
This is right. I&#x27;m a big fan of this sort of embedded audit metadata wherever it makes sense.<p>I do wonder when doing stuff like this though, if this really shouldn&#x27;t be something that the database gives you for free.<p>I read a few years ago about &#x27;fact based&#x27; 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.<p>For practical reasons (i.e. just picking a standard postgres setup to get stuff done) I&#x27;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?
评论 #26923204 未加载
评论 #26925299 未加载
评论 #26926595 未加载
nickjj大约 4 年前
Indeed, I do this pretty much all the time too.<p>One common&#x27;ish example not mentioned in the article is storing whether or not a user is active. Storing &quot;is_active&quot; as a boolean makes sense but switching that to &quot;deactivated_at&quot; gives you so much more information.
评论 #26923667 未加载
jpswade大约 4 年前
I&#x27;ve yet to find a case where using a timestamp over a boolean hasn&#x27;t been the better option.<p>This is because turning a boolean on is an event so it&#x27;ll always have a timestamp.<p>Sometimes it&#x27;s useful to know when this event happened.<p>The only exception I can think of is if for some reason you&#x27;re trying to save on bytes, which in this day and age, especially true for web applications, this is practically never the case.
评论 #26923289 未加载
评论 #26923302 未加载
评论 #26924615 未加载
defanor大约 4 年前
As mentioned in other comments, there&#x27;s a bunch of drawbacks with this approach, but there&#x27;s a more general term &quot;boolean blindness&quot; [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.<p>[1] <a href="https:&#x2F;&#x2F;www.cs.cmu.edu&#x2F;~15150&#x2F;previous-semesters&#x2F;2012-spring&#x2F;resources&#x2F;lectures&#x2F;09.pdf" rel="nofollow">https:&#x2F;&#x2F;www.cs.cmu.edu&#x2F;~15150&#x2F;previous-semesters&#x2F;2012-spring...</a>
jchw大约 4 年前
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&#x27;m pretty out of date on Django these days.)<p>(Though it is worth noting that you sort-of get this for free if you implement a scheme with &#x27;revisioned&#x27; data, or a database that simply has that as a feature. But, it&#x27;s still useful to just have this additional bit of information handy, if nothing else.)
评论 #26924154 未加载
评论 #26923931 未加载
bachmeier大约 4 年前
If you&#x27;re going this route, it&#x27;s hard to understand why you wouldn&#x27;t just store all the information you want explicitly in a string.<p>&quot;true [timestamp]&quot; &quot;false [timestamp]&quot; &quot;unset [timestamp]&quot;<p>That&#x27;s more information than described in the article and it&#x27;s easier for future you to understand what&#x27;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&#x27;s what you want:<p>&quot;false [timestamp] true [timestamp] false [timestamp] true [timestamp]&quot;
评论 #26923992 未加载
koff3大约 4 年前
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.
a_c大约 4 年前
In my experience I would even say having a boolean field in your table is a bad idea, with rare exceptions.<p>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,
lars512大约 4 年前
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.
kown7大约 4 年前
In this case, why not go straight to append-only databases where every entry has a timestamp?<p>That will be _the_ audit log in your database.
评论 #26924347 未加载
alephnan大约 4 年前
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
评论 #26923342 未加载
zzzeek大约 4 年前
I&#x27;m not really down with assigning meaning to NULL. NULL means &quot;unknown&quot;, full stop. This is why SQL doesn&#x27;t like if you compare to NULL using equality, because nothing &quot;equals&quot; NULL.<p>Similarly wouldn&#x27;t I want to know when a true value became false? This post seemed very strange in that regard.<p>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&#x2F;or tables to suit my needs.
评论 #26924901 未加载
评论 #26925259 未加载
dashwav大约 4 年前
This seems to only really work in languages that allow null variables&#x2F;timestamps. I wouldn&#x27;t really want to have to do comparators to the default value of a timestamp.
评论 #26923175 未加载
评论 #26923214 未加载
评论 #26923329 未加载
评论 #26923291 未加载
aasasd大约 4 年前
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.<p>‘Remember The Milk’ and Evernote make it pretty nice and easy by keeping the dates and some other info. (Though of course there&#x27;s a gotcha that RTM&#x27;s Android app forgets to implement the display of this metadata.) Not that I recommend these apps currently, especially Evernote.<p>Well, after migrating to Org-mode I have a persistent itch caused by the fact that Org doesn&#x27;t have modification times for outline items, and implementing them in Emacs is a pain. That&#x27;s one downside of not separating the view from the model. But the creation time is easy to add, in case someone wonders.<p>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. (<i>cough</i> unlike HN <i>ahem</i>.)
andix大约 4 年前
I don’t like NULLs. Nullables always get back you in ways you would never expect.<p>That’s why I won’t use a tip like that.
评论 #26923969 未加载
goofballlogic大约 4 年前
Exception i encountered recently was needing ternary state boolean. a set of flags where true and false indicates outcome and undefined indicates &quot;not yet processed&quot;. I suppose some sort of 0 timestamp could work but... No, I think I&#x27;ll stick with boolean.
ghego1大约 4 年前
TLDR don&#x27;t use a boolean, leave the field empty (NULL) and set a timestamp when should be true.<p>To be honest a tldr isn&#x27;t actually needed, the post is both very concise, straight to the point, and convincing.<p>As per the languages I use more often to query DBs (TS, JS, PHP, Python) I don&#x27;t see any downside. Evaluating if a variable is empty or not, or it&#x27;s type, is not &quot;bad&quot;, 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.
throw149102大约 4 年前
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&#x27;t atomic.
xlii大约 4 年前
It&#x27;s a very neat idea that I&#x27;ll consider in the future however I have some concerns about it.<p>One thing is about the database design. I remember lecturer proclaiming that models with many nullable relations is:<p>- bad design<p>- performance risk<p>- might mess with indexing<p>I haven&#x27;t verified this knowledge in many years, to I&#x27;m not sure if that point still stands, also in wake of not optimizing pre-mature this might not be an issue.<p>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.
评论 #26923237 未加载
评论 #26923242 未加载
jackcviers3大约 4 年前
Converting any non boolean type into a boolean is always a lossy compression that will often need to be reversed later. If&#x2F;else was invented so that you don&#x27;t need to perform this compression at the variable level.<p>I also don&#x27;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.
victorp13大约 4 年前
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.
durnygbur大约 4 年前
<p><pre><code> let published_at = new Date() if (published_at) console.log(&quot;it&#x27;s true!&quot;) if (!published_at) console.log(&quot;it&#x27;s false!&quot;) </code></pre> As a FE dev I haven&#x27;t had workplace with a codebase allowing above for at least 5 years. No one even asks &quot;shall we us JS ot TS?&quot;. Strictly enforced static typing all over. It&#x27;s not that I like it, just no one asks me.
评论 #26923498 未加载
dvfjsdhgfv大约 4 年前
But these are two very different use cases and just using one for another won&#x27;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.
lazyasciiart大约 4 年前
Am I missing something, or does this assume I never care about the timestamp for turning a value off?
评论 #26923643 未加载
coward76大约 4 年前
Some of us have an audit log that tells when a record has changed. Others just use event sourcing.
MrOxiMoron大约 4 年前
except when it is actually a nullable Boolean and there is a big difference between the 3 states
usr1106大约 4 年前
When reading this I miss a HN feature &quot;upvote as a falsehood to be aware of&quot;.
subleq大约 4 年前
I wrote a django model field that does this <a href="https:&#x2F;&#x2F;gist.github.com&#x2F;gavinwahl&#x2F;17c07335c8dd1b832911" rel="nofollow">https:&#x2F;&#x2F;gist.github.com&#x2F;gavinwahl&#x2F;17c07335c8dd1b832911</a>
redact207大约 4 年前
To quote the author: &quot;it depends&quot;<p>If when something happens needs to fold into your business logic then by all means go for it.<p>If you&#x27;re moreso doing it as an audit then logging out the event with it&#x27;s context is going to be more useful.
评论 #26923169 未加载
thih9大约 4 年前
Note that this doesn’t keep historical values.<p>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.<p>In some cases this might be insufficient.
rurban大约 4 年前
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.
m4lvin大约 4 年前
... but then please also update your privacy policy that you are storing this additional data about your users!<p>The only data that cannot be leaked or stolen is what you do not store in the first place.
hsbauauvhabzb大约 4 年前
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
评论 #26923564 未加载
评论 #26923591 未加载
jgalt212大约 4 年前
You probably should be storing a vector or stack of timestamps. Empty vector&#x2F;stack is still falsey, and now you have a surface level history of when things changed.
strogonoff大约 4 年前
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.
ajuc大约 4 年前
Why just booleans though? If you have any configuration in database and you don&#x27;t store when and who changed it - you&#x27;re doing it wrong.
codeulike大约 4 年前
So we&#x27;re trying to come up with reasons why this is bad advice? uhhhhhh ... something something far future something Y10K problem something.
neo2006大约 4 年前
What about all the overhead in testing that using a timestamp instead of boolean will introduce? Specially if you end up not needing it
ximm大约 4 年前
One important downside: Data protection. This approach of &quot;store it now in case you might need it later&quot; is in direct violation of the principle of data minimisation in GDPR.
评论 #26923633 未加载
评论 #26923583 未加载
isuckatcoding大约 4 年前
I don’t see the benefit. This is what changelogs are for.
perspicace大约 4 年前
I love the drake meme they embeded for sharing the post!