6 ms·
> 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
by lysol 14y ago
> 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.
- RyanZAG 14y agoWhy 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.
- ianburrell 14y agoPostgreSQL 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.
- jtheory 14y agoExactly 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.
- RyanZAG 14y ago24 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.
- jtheory 14y ago> [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.
- nathanstitt 14y agoSometimes 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.
- jfb 14y ago"Sometimes I think Mysql has corrupted everyone's brain." Unsurprising, given how cavalier MySQL is with all the other data it "stores".
- justincormack 14y agoI hope you meant 4 or 8 bytes not bits....
- nathanstitt 14y agoYes, bytes. I suppose most everyone wants to use dates more than 256 seconds past Jan 1, 1970. cue embarrassment