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

161–170 of 205 posts

Re: You might as well timestamp it

#161

Post by karmakaze »

RedShift1 wrote: post #121
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…

Storing both boolean and timestamp with the correct constraints seems like a better solution to me.

This is the typical solution for MySQL. There are indexable virtual generated columns but don't seem to have been used/around enough to be battle tested.

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

Re: You might as well timestamp it

#162

Post by crocarneiro »

Spivak wrote: post #157
m_mueller wrote: post #147

Earlier quoted context omitted.

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.

He meant that in some SQL Dialects you cannot check FIELD = NULL, you have to use FIELD IS (NOT) NULL (like Oracle). Therefore you cannot use FIELD inside an IN clause either.

Re: You might as well timestamp it

#163

Post by gmfawcett »

marcosdumay wrote: post #149
traceroute66 wrote: post #82

Earlier quoted context omitted.

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

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

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.

Re: You might as well timestamp it

#164

Post by notatoad »

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…

OP missed that nuance, and so will a lot of other developers you work with. Relying on a difference between null and false is one of those things that seems clever when you first do it, but then three years later your project is scattered with comments like "// be very careful checking this, it must be false here null means something else" after you or some other dev you're working with confuses falsy for false

Re: You might as well timestamp it

#165

Post by cratermoon »

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…

I've been in software development for 27 years, and I've never seen a case where a boolean column or variable being null was anything but a terrible, confusing, fragile, hack.

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
    >

Re: You might as well timestamp it

#166

Post by chriswarbo »

jimktrains2 wrote: post #128
andix wrote: post #76

Earlier quoted context omitted.

Tri-state booleans are also very ugly. What happens if you suddenly need a 4th state? Use enums (or any equivalent) for states that are non-boolean.

Null is a signifier that a value isn't know. Yes, if you're using null to mean something else you should use another type, but nullable-booleans (and nullable fields in general) can be extremely useful. For instance, if you're collecting information, but wont know everything at creation time, the unknown values are null.

> For instance, if you're collecting information, but wont know everything at creation time, the unknown values are null.

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.

Re: You might as well timestamp it

#167

Post by ThinkBeat »

hallway_monitor wrote: post #129
alpaca128 wrote: post #103

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

If you go with nullable Boolean in the database schema, do you give it a type of nullable boolean in the code?

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.

Re: You might as well timestamp it

#168

Post by crazygringo »

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.

Re: You might as well timestamp it

#169

Post by jackcviers3 »

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.

Re: You might as well timestamp it

#170

Post by sdfhbdf »

dsego wrote: post #106
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'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 t…

Or you could have views with `deleted_at` filtered out:

https://learnsql.com/blog/sql-view/

Post reply on HN