Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You might as well timestamp it

changelog.com

Post reply on HN
Print view View original

91–100 of 205 posts

Re: You might as well timestamp it

#91

Post by cmsefton »

kijin wrote: post #80
cmsefton wrote: post #52

Earlier quoted context omitted.

The other thing I don't like about this is that it's great at tracking the switch to "true" (the timestamp value), but when you want to go back to "false", you have to wipe out the timestamp, and you then have no idea when it was unpublished or unhidden or whatever. Now you need a second column to track that as I see it, and you start getting into weird territory. IMO it's better to keep the Boolean, and just introdu…

If you need to maintain a proper audit trail for every state change, you need a separate table for that. 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.

Yeah, that's true, I would prefer tracking things separately, which is another reason I don't particularly like the proposed idea.

Re: You might as well timestamp it

#92

Post by forkerenok »

antender wrote: post #68

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 compress…

I think this is still workable with an expression/functional index, where the indexed expression is "happened_at IS NOT NULL".

Re: You might as well timestamp it

#93

Post by vbsteven »

jpswade wrote: post #70
mewse wrote: post #26

Earlier quoted context omitted.

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 t…

It’s a good question, but if you care about the audit trail, then perhaps event sourcing is a better choice. So yes, this only assumes you care about when the on state happened and you don’t care about the history.

Eventsourcing is a possible solution for audit trails but comes with additional complexity.

If you just want to add audits of what changed when then you can also get there with a separate audit table that just stores timeframe, changed fields with new values and who/what made the change. Some frameworks have support for this out of the box or with a small library.

Re: You might as well timestamp it

#94

Post by cdirkx »

thirdlamp wrote: post #90
cdirkx wrote: post #86

Earlier quoted context omitted.

The obvious solution is to encode it like this: `null` is `null, `yes` is the timestamp when it was set and `false` is a timestamp somewhere in the future. Now t 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...

I can’t tell if you’re being ironic or not

Yeah it's mostly a reference to Y2K and Y2038 [1], using representations that seem clever and work now, but will lead to bugs in the future because nobody thinks their software will be around for that long.

[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

Re: You might as well timestamp it

#95

Post by vbsteven »

jchw wrote: post #16

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 'revision…

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.

Re: You might as well timestamp it

#97

Post by vbsteven »

andix wrote: post #75

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.

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.

Re: You might as well timestamp it

#98

Post by hnedeotes »

danielheath wrote: post #51
bob1029 wrote: post #45

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…

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.

- "Wanna get laid?"

- "Jan 23, 2020"

Sure, I mean. It's Javascript after all.

Re: You might as well timestamp it

#99

Post by vbsteven »

bachmeier wrote: post #50

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 recor…

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.

Re: You might as well timestamp it

#100

Post by doodpants »

davidverhasselt wrote: post #23

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…

Long time readers of The Daily WTF know that the canonical tri-state boolean is { True, False, FileNotFound } [1]

[1] https://thedailywtf.com/articles/What_Is_Truth_0x3f_

Post reply on HN