Hacker News new | comments | show | ask | jobs | submit login

> When storing time, store Unix time. It's a single number.

Don't do this in your database. Use your datetime types. Please. You might save some work with your timezone but you're not going to be able to use intervals etc in a smart way.

This would be even dumber with PostgreSQL, which has much more robust date/time functionality (being able to use the - operator with datetimes and intervals, for example) and completely sane timezone support.

Why can't you use intervals in a smart way? Single number is the easiest and fastest method to use for intervals.

The only true problem with storing unix time is leap seconds and relativistic effects, which can both be safely ignored for most time keeping usages.

PostgreSQL intervals are smarter than just number of seconds between two datetimes. They can represent "1 month" or "1 year" which can't be represented as a number of seconds. Also, intervals can be used between dates or times, not just datetimes.

Exactly right; date arithmetic is horrible if you only have seconds to work with.

Even just saying "24 hours from now" -- as your user would see it -- you'll sometimes get the wrong answer if you just add on 24 hours worth of seconds... suppose they have a DST shift tonight?

What if you need to know how many days remain from today to March 1st of this year? ...well, are we in a leap year?

Postgres datetime doesn't solve all of the problems that crop up, but it's certainly better than using unix time.

24 hours from now is no problem if you're using unix time. Just add 24 hours worth of seconds -- when you convert back to the user's time, your conversion logic will take care of timezones. This is also incredibly important if serving an app over the internet as your user may have just flown from USA to Japan. Storing unix time solves this in the easiest way.

Same for working out how many days remain - you convert March 1st to a unix date, get an interval in seconds, and then convert that into a human readable format on display.

This isn't some revelation - unix time has been used successfully for decades now.

EDIT: Also, Postgresql uses a number very similar to unix time in the actual storage of the date - it just handles the pretty display for you. So you're arguing for using the same thing whether you say use a postgre datetime or a unix date number.

> [Postgres] just handles the pretty display for you

It's rather more than that, though I don't have the time to kill digging into it now.

And personally I mostly seem to end up doing more complicated date processing in code, not queries; but there everything needs to be a date immediately (not unix time or similar) for most purposes -- then I can use complicated libraries written by others to let me do "simple" things with dates, like rolling months.

Ah, also: > you convert March 1st to a unix date Then that's where the complicated logic goes. Basically, you need that somewhere, and it's non-trivial (leap year calc is the least of it).

My point isn't that unix dates aren't useful for storage, but that they aren't useful by themselves for calculation.

Sometimes I think Mysql has corrupted everyone's brain.

Postgresql and every other database that I'm aware of stores dates internally as a 4 or 8 bit number anyway, so you aren't saving anything by using an (hopefully) bigint instead of a datetime column.

"Sometimes I think Mysql has corrupted everyone's brain."

Unsurprising, given how cavalier MySQL is with all the other data it "stores".

I hope you meant 4 or 8 bytes not bits....

Yes, bytes. I suppose most everyone wants to use dates more than 256 seconds past Jan 1, 1970. cue embarrassment

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact