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

121–130 of 205 posts

Re: You might as well timestamp it

#121

Post by RedShift1 »

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.

Re: You might as well timestamp it

#122

Post by PebblesRox »

isbvhodnvemrwvn wrote: post #114
tuukkah wrote: post #59

Earlier quoted context omitted.

Also known as *bool, "Maybe Bool", "Boolean?", "Optional " etc.

Well, in browsers there's `HTMLMediaElement.canPlayType()` which returns one of the following strings: - "probably" - "maybe" - "" Reference: https://developer.mozilla.org/en-US/docs/Web/API/HTMLMediaEl...

That's like my washing machine which has a Normal setting, a Standard setting, a Wash setting...

Re: You might as well timestamp it

#123

Post by defaultname »

jpswade wrote: post #19

I've yet to find a case where using a timestamp over a boolean hasn't been the better option. 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…

Every change you can make in a database becomes an event by this definition. It's just as useful to know when every change happens in your database, and by who.

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.

Re: You might as well timestamp it

#124

Post by ExtraE »

isbvhodnvemrwvn wrote: post #114
tuukkah wrote: post #59

Earlier quoted context omitted.

Also known as *bool, "Maybe Bool", "Boolean?", "Optional " etc.

Well, in browsers there's `HTMLMediaElement.canPlayType()` which returns one of the following strings: - "probably" - "maybe" - "" Reference: https://developer.mozilla.org/en-US/docs/Web/API/HTMLMediaEl...

Wat

Re: You might as well timestamp it

#126

Post by hallway_monitor »

andix wrote: post #76
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…

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.

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.

Re: You might as well timestamp it

#127

Post by kedean »

choeger wrote: post #6
dashwav wrote: post #2

This seems to only really work in languages that allow null variables/timestamps. I wouldn't really want to have to do comparators to the default value of a timestamp.

The author is talking about databases, not programming languages. 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…

That does assume your database only allows unix-style timestamps. MariaDB, for example, has "datetime", supporting dates between the year 1000 and 9999, distinct from null/zero.

Unfortunately, datetime takes 8 bytes vs the 4 for a timestamp.

Re: You might as well timestamp it

#128

Post by jimktrains2 »

andix wrote: post #76
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…

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.

Re: You might as well timestamp it

#129

Post by hallway_monitor »

alpaca128 wrote: post #103
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…

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.

Re: You might as well timestamp it

#130

Post by samhh »

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.

There are many application states in which `null` could mean "not got the data yet" and/or "there is no data".

Just use an enum, it's much more expressive.

Post reply on HN