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

Question

My reason for this is that dates stored as date objects in any database are usually written in a specific format, which can be very different from what you need to present to the user in the interface. I also think this is especially useful if your application retrieves information from different types of data stores. A good example would be the difference between a MongoDB object and an SQL date.

However, I do not know if this is recommended practice. Should I store dates as longitudes (time in milliseconds) or as date objects?

Answer 1

I can’t talk about this in relation to MongoDB, but there is no SQL in the database, this is not the best practice. This does not mean that there may be an occasional use case, but "best practice", no.

Save them as dates, retrieve them as dates. It’s best to set up your database to store them in UTC (free, “GMT”) so that the data is portable and you can use different local times (for example, if the database is used by geographically diverse users) and handle any conversions with UTC according to local time at the application level (for example, via Calendar or a third-party date library).

Saving dates as numbers means that your database is hard to report, run special queries, etc. I made this mistake once, this is not one, I will repeat it without a good reason. :-)

Answer 2

It really depends on:

• What database do you use and its support by date / time.
• Your client needs (for example, how happy you are that you are confident that you will always use Java)
• What information are you really trying to present
• Your diagnostic tools

The third point is probably the most important. Think about what the values ​​you are trying to save really mean. Although you are clearly not using Noda Time, I hope that my page on choosing the type of Noda time to use based on your input can help you think about it clearly.

If , you are only using Java, and your database does not have terribly good support for date and time types, and you are only trying to represent a “point in time” (rather than, say, instant time in a specific time zone or local date / time with an offset or only a local date / time or just a date ...), and it’s more convenient for you to write diagnostic tools to convert your data into more human-readable forms - then saving long is reasonable. But this is a pretty long list of "if" s.

If you want to be able to manipulate dates in a database — for example, by querying all the values ​​that occur on the first day of the month — then you should probably use a date / time type, carefully tracking time zones. (My experience is that most databases are at least shockingly poorly documented when it comes to their date / time types.)

In general, you should use any type that is able to satisfy all your requirements and is the most natural representation for this particular environment. Thus, in a database that has a date / time type that does not give you problems when interacting with it (for example, arbitrary conversion of time zones to an unsolicited path), use this type. It will make all kinds of things easier.

The advantage of using a more “primitive” representation (for example, an integer of 64 bits) is that the database will not interfere with it. You effectively hide the meaning of the data from the databae, with all the normal pluses and minuses (mostly minuses) of this approach.

Answer 3

It depends on various aspects. When using the standard "seconds from the era", and someone uses only the whole precision, their dates are limited to the range of 1970-2038.

But there is a problem with accuracy. For example, unix time ignores seconds of a jump. Each day is defined as an equal number of seconds. Therefore, when calculating the time deltas between unix time, you get some error.

But all the more important is the fact that you assume that all your dates will be fully known , since your idea does not have the ability to use half of only half of the given dates. In fact, there are many events that you do not know to the nearest second (or even ms). Thus, this is a function if the view allows you to specify, for example, only the accuracy of the day. Ideally, you will keep dates with their accurate information.

Also, let's say you create a calendar application. There is time, but there is also local time. Quite often, you need both available information. When planning your floors, you can of course do it best in synchronized time, so it will be fine here. If you, however, also want you to not plan events outside of 9-20 hours local time, you also always need to save time zone information . For anything that spans more than one place, you really need to include the time zone in your date view. Assuming you can just convert all the dates that you see in the current local time, are pretty naive.

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

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

can return records with a date of 0000-00-00 00:00:00 , although this violates the popular understanding of logic.

Answer 4

Since this question is marked by MongoDB: MongoDB does not store dates in String or not, they actually save it as long ( http://www.mongodb.org/display/DOCS/Dates ):

The BSON date value stores the number of milliseconds since the Unix era (January 1, 1970) as a 64-bit integer. v2.0 +: this number is signed, so dates until 1970 are stored as negative numbers.

Since MongoDB has no direct plans for using the complex date processing functions (for example, getting only a year for queries, etc.) that SQL has in a standard query, there is no real drawback, this can reduce the size of your indexes and storage.

It should be noted here that the aggregation structure: http://docs.mongodb.org/manual/reference/aggregation/#date-operators there are strange and wonderful things that you can only use using the supported BSON date type in MongoDB, however, as for this depends on your requests.

Do you consider yourself necessary in the functions of the aggregation structure? Or will there be a sick extra object overhead?

My personal opinion is that the BSON date type is such a small object that, for storing a document without it, will impede the entire system and its future compatibility for no apparent reason. So yes, I would use the BSON date type, not the long one, and I consider it a good practice to do this.

Answer 5

I do not consider it my best practice to keep dates for so long, because that would mean that you would not be able to fulfill any specific requests by date. eg:

 where date between 

We also cannot easily get the date of the month from the table using SQL queries.

It is better to use a single date format converter in the java layer and convert the date to it and use the same format throughout the application.

Answer 6

IMHO, storing dates in a database would be best if you can use strings. Therefore, avoid unnecessary data going up and down to the server if you do not need all the fields in the calendar. There is a lot of data on the calendar, and each instance of Calender is also very heavy.

So, store it as a String, only the data you need and convert it back to Calendar when you need it, and use it.