Hacker News Reader

Comment by happythought | Hacker News Reader

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.

View on HN

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.

Reply on news.ycombinator.com