3 ms·

One awful thing I've seen a few times is people who put data into a database in a format meant to make it easier for working with a particular platform, such as

One awful thing I've seen a few times is people who put data into a database in a format meant to make it easier for working with a particular platform, such as serialized Java dates (or even dates as longs). It completely ties you to one implementation and precludes using the data for other things such as reporting and alerts or even ad hoc queries.

grogers 5y ago
You can always convert in the DB when selecting if you want the date. Dates/times in general are kind of horrible in all DBs and programming languages so I don't think using unix seconds (or milliseconds, etc) is obviously wrong. It's kind of like storing strings as plain bytes in the DB, it's not what you'd do by default but it's not completely wrong.
- bshipp 5y ago
  I tend to agree that dates and times are awful to work with but storing everything as a Unix timestamp still seems to be the easiest way to avoid most of the headaches, in my experience. there's nothing worse than trying to reconcile a database filled with naive dates with no associated time zone information. is it daylight savings? is it in this timezone or did they enter it while traveling and their computer automatically updated the time? The simplest way to minimize these issues, in my experience, is to put the logic for converting local time into UTC in the program and only store the Unix stamps in the DB.
AtlasBarfed 5y ago
So you don't like serialized java dates, which presumably are some standard at least. You don't like longs / epoch. So..... what do you like? Date formatting/conversion/serialization is not standardized across databases. And it's generally hair pulling (at least the Oracle version was last I checked which was a long time ago). Longs or ISO-8601 strings? No DB-specific crud. Yes please.
- forinti 5y ago
  At least you can compare longs and index them. That's not too bad. But I prefer to just use the native date type; it allows you to use the built-in date functions.

grogers 5y ago

You can always convert in the DB when selecting if you want the date. Dates/times in general are kind of horrible in all DBs and programming languages so I don't think using unix seconds (or milliseconds, etc) is obviously wrong. It's kind of like storing strings as plain bytes in the DB, it's not what you'd do by default but it's not completely wrong.

bshipp 5y ago
I tend to agree that dates and times are awful to work with but storing everything as a Unix timestamp still seems to be the easiest way to avoid most of the headaches, in my experience. there's nothing worse than trying to reconcile a database filled with naive dates with no associated time zone information. is it daylight savings? is it in this timezone or did they enter it while traveling and their computer automatically updated the time? The simplest way to minimize these issues, in my experience, is to put the logic for converting local time into UTC in the program and only store the Unix stamps in the DB.

AtlasBarfed 5y ago

So you don't like serialized java dates, which presumably are some standard at least. You don't like longs / epoch. So..... what do you like? Date formatting/conversion/serialization is not standardized across databases. And it's generally hair pulling (at least the Oracle version was last I checked which was a long time ago). Longs or ISO-8601 strings? No DB-specific crud. Yes please.

forinti 5y ago
At least you can compare longs and index them. That's not too bad. But I prefer to just use the native date type; it allows you to use the built-in date functions.