Is it Java best practice to store dates as longs in your database?

I can't speak for it in relation to MongoDB, but in SQL database, no, it's not best practice. That doesn't mean there might not be the occasional use case, but "best practice," no.

Store them as dates, retrieve them as dates. Your best bet is to set up your database to store them as UTC (loosely, "GMT") so that the data is portable and you can use different local times as appropriate (for instance, if the database is used by geographically diverse users), and handle any conversions from UTC to local time in the application layer (e.g., via Calendar or a third-party date library).

Storing dates as numbers means your database is hard to report against, run ad-hoc queries against, etc. I made that mistake once, it's not one I'll repeat without a really good reason. :-)

It very much depends on:

• What database you're using and its date/time support
• Your client needs (e.g. how happy are you to bank on the idea that you'll always be using Java)
• What information you're really trying to represent
• Your diagnostic tools

The third point is probably the most important. Think about what the values you're trying to store really mean. Even though you're clearly not using Noda Time, hopefully my user guide page on choosing which Noda Time type to use based on your input data may help you think about this clearly.

If you're only ever using Java, and your database doesn't have terribly good support for date/time types, and you're only trying to represent an "instant in time" (rather than, say, an instant in a particular time zone, or a local date/time with an offset, or just a local date/time, or just a date...), and you're comfortable writing diagnostic tools to convert your data into more human readable forms - then storing a long is reasonable. But that's a pretty long list of "if"s.

If you want to be able to perform date manipulation in the database - e.g. asking for all values which occur on the first day of the month - then you should probably use a date/time type, being careful around time zones. (My experience is that most databases are at least shocking badly documented when it comes to their date/time types.)

In general, you should use whatever type is able to meet all your requirement and is the most natural representation for that particular environment. So in a database which has a date/time type which doesn't give you issues when you interact with it (e.g. performing arbitrary time zone conversions in an unrequested way), use that type. It will make all kinds of things easier.

The advantage of using a more "primitive" representation (e.g. a 64 bit integer) is precisely that the database won't mess around with it. You're effectively hiding the meaning of the data from the databae, with all the normal pros and cons (mostly cons) of that approach.

It depends on various aspects. When using the standard "seconds since epoch", and someone uses only integer precision, their dates are limited to the 1970-2038 year range.

But there also is some precision issue. For example, unix time ignores leap seconds. Every day is defined to have the same number of seconds. So when computing time deltas between unix time, you do get some error.

But the more important thing is that you assume all your dates to be completely known, as your representation does not have the possibility to half only half-specified dates. In reality, there is a lot of events you do not know at a second (or even ms) precision. So it is a feature if a representation allows specifing e.g. only a day precision. Ideally, you would store dates with their precision information.

Furthermore, say you are building a calendar application. There is time, but there also is local time. Quite often, you need both information available. When scheduling overlaps, you of course can do this best in a synchronized time, so longs will be good here. If you however do also want to ensure you are not scheduling events outside of 9-20 h local time, you also always need to preserve timezone information. For anything that does span more than one location, you really need to include the time zone in your date representation. Assuming that you can just convert all dates you see to your current local time is quite naive.

Note that dates in SQL can lead to odd situations. One of my favorites is the following MySQL absurdity:

SELECT * FROM Dates WHERE date IS NULL AND date IS NOT NULL;

may return records that have the date 0000-00-00 00:00:00, although this violates the popular understanding of logic.