Hacker News Reader

Comment by lisper | Hacker News Reader

> My current preferred strategy for dealing with this (at least for any table smaller than a few GBs) is to dump the entire table contents to a git repository on a schedule.

How does that solve the problem? All this would do is give you snapshots of the state of the DB at particular points in time.

The only way to know who changed what when is to keep track of those changes as they are made, and the best way to do that is to stick the changes in an eventlog table.

View on HN

A developer joined a team I was on once. The boss was infamous for hiring anyone with a pulse and assigning a small client project. Sink or swim.

As the senior dev it was my job to provide info as needed and subtly assess the new, potential rockstar code ninjas.

Anyway, this new guy says "I store all my dates in sql as text in iso format so I can easily sort them".

That is... almost not horrible?

If he had said anything other than "in sql" it would be the correct answer.

"I store all dates in spreadsheets in ISO format"

"I prepend ISO dates to marketing campaign names so I can easily sort them in the horrible web frontends that doesn't have a sort by date option"

"I start all my blog entries with ISO dates..."

I mean, just... so close!

Why is this bad?

Sql databases usually come with their own date types that are implemented with integers behind the curtains. They take up less space and are easier to sort than text fields.

EDIT: also I wouldn’t consider this egregious. If the Senior explains and the person was happy to learn something then that’s a good outcome. If they are stubborn about it, then that wouldn’t be great.

But which very likely store less information than an ISO timestamp does. (The integers stored are usually POSIX timestamps.) So, you might have to store, e.g., timezone information elsewhere. (And that might also be a good thing. It might not. It depends. Without knowing more, the DB's type is probably the right choice.)

I've seen far worse things than this done to a SQL database though…

Except that ISO-8601 doesn’t include anything as useful as a political time zone, just an offset from UTC. That’d be good if UTC offset was useful for anything, but instead it just adds to the confusion.

UTC offset is way more useful than those 3-4 character timezone abbreviations.

Just have a look at the list from Wikipedia [1] and notice how many repeats there are.

As an example AMT could be either UTC+4 or UTC-4.

And this doesn't even includes the various local acronyms that people use around the world.

[1] https://en.wikipedia.org/wiki/List_of_time_zone_abbreviation...

Political zones are not the same thing as the abbreviated zones.

IANA time zone identifiers are fully-qualified and unambiguous like America/Los_Angeles or Asia/Kolkata.

Thanks, I have never heard the term “political zones” before, so I made an assumption.

I didn't downvote you, but just for reference the time zone abbreviations you are supposed to use are in the tzdata db, and are listed https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

If you don't use these, you will inevitably be somewhat wrong due to leap issues, political changes in time, or the like.

A lot of this was really cleared up for me by Evan Czaplicki's documentation for the Elm time library: https://github.com/elm/time

At least in Postgresql, it is standard practice to use timestamptz type, which saves timestamp together with a time zone. Not only it uses less space on disc, but you can do a lot of operations with it natively in database itself: sort in various ways, compare to NOW(), construct time intervals and even ensure data validity with checks.

Timestamptz doesn't store a time zone in postgres. It converts all timestamps to utc. It's a common misconception.

> But which very likely store less information than an ISO timestamp does.

If you are trying to store dates and not timestamps, that’s what you want, otherwise equality tests might not work right.

> (The integers stored are usually POSIX timestamps.)

For date types, if they exist distinctly, usually not.

I'm going to guess because sql has rich datetime support. At least the majority of modern databases do.

You can then do things like greater than date or between X date and y date and get some use out of that column..

> I store all my dates in sql as text in iso format so I can easily sort them

Strong SQLite vibes... (SQLite doesn't have a date format, it has builtin functions that accept ISO format text or unix timestamps)

As a Unix person, aren't dates just number? Why is sort complex? Just parse input and render timestamps

Time zone information makes this a bit tricky.

It's pretty miserable when you're trying to look up a date, and all you get are numbers that requires a conversion step to be readable.

Assuming by number you mean epoch seconds/milliseconds, a number can store the instant in time, but not a TIMEZONE. Obviously you could do bit magic if you shift it over (so you don't mess up sorting), but that's true for any data and a sufficiently large number type.

That's a good point. We were talking MS SQL but yes, that would make way more sense.

I once saw an app that had a text column as date, then 3 other columns for day month and year so they could sort by date.

I remember one design I reviewed that wanted to use XML as something to be queried using simple text comparison (this was before some databases adding explicit support for XML).

Not XML, but, the classic 'never parse html with regex'

https://stackoverflow.com/questions/1732348/regex-match-open...

Meh, I don't see much wrong with this honestly. There are some cases where using text as the type is the only way. Bet you the junior dev worked with a lot of messy data in the past, where it wasn't always possible to do a direct datestr -> SQL date type.

Just point him in the right direction and move on.

Yeah, I did explain why that wasn't "how we did it", and tried not to sound like a know it all (I am definitely not this).

I am way less judgemental now and a lot of the comments here are making me rethink what I thought about him saying this. He had a lot of other issues and resigned after a couple of days so I guess we'll never know what he was capable of.

Hah, amazing and thanks for sharing. I think it's mostly that you sounded like you were holding a petty grudge over some small technical thing that can make sense. Maybe he resigned because he felt overly judged?

No, he had a day drinking and absentee problem. My boss talked with him, offered him another chance, but he resigned. He really didn't like following requirements either but I think it was just a symptom of his overall problems. He was going through something and we were just a stop on his journey.

Fair enough. Cheers.

Sounds like someone who's realised how crappy most databases' time handling is.

I did that in DB2 or Oracle (or both, don’t remember - it was 15 years ago and the project used both) because I needed millisecond time resolution and the database date time field did not support milliseconds.

Among the option of storing (date field, millisecond field) and (date as iso8601 text field with millisecond without time zone) I chose the latter, and I can’t tell for sure but it was almost surely the better choice.

Surely you can do both, so you can still perform sane sorting/indexing on "real" timestamps and then use the extra precision string timestamp for drilling down?

Iso8601 sorts just as well.

I saw no benefit in storing two copies. That’s recipe for problems - e.g. “update” code that doesn’t update both, or somehow updates them differently (which one is right?). You can probably have t-sql or some “check” constraint to enforce equivalence, but ... why?

For most practical uses, an ISO8601 textual representation is as good, though a little less space efficient.

Reply on news.ycombinator.com