Date vs Milliseconds | for scalablility, saving, searching and obtaining time in Java + MySQL(or other Db's)

Question

Which is the most beneficial in Java and a DB for DateTime? (Using JodaTime as Date)

(DateTime object (Java) + TIMESTAMP (DB) ) VS (Milliseconds long (Java) + BIGINT(DB)

for the use of DateTime information in Java Web application backed by an underlying Database

Areas of interest

• manipulating, processing and memory usage in Java
• saving using efficient storage space in a MySQL database
• ease of porting a BIGINT/TIMESTAMP column to other DBs
• ease of searching the DB for a BIGINT/TIMESTAMP or between two BIGINTs/TIMESTAMPs

E.g. Say I had an event with a start & end DateTime. Is it faster to search for events on dates using BIGINT in the DB than TIMESTAMPS

I might be swapping the underlying DB as scalability and retrieval issues arise.

Would saving the DateTime as a TIMESTAMP in a MySQL DB lead problems when porting to another DB like Oracle?

---

I currently use the Joda DateTime in java then storing the millisecond of that value.

When retrieving it, I convert the milliseconds back to a DateTime object and display it.

Answer 1

Of course it depends on what you want to do with the data, but I would recommend using the DB's native type for time/date (whatever that may be) for storage in the DB. That is the standard for databases, and most date/time functions in DBs expect data in that form.

Special note about MySQL:

While you can use TIMESTAMP for date/time values, be aware that TIMESTAMP cannot record dates earlier than 1970-01-01. So while it's ok for dates close to "now" (such as creation/modification dates), it is not appropriat for possibly historical dates such as date of birth. So only use TIMESTAMP if you are completely certain you will never need historical dates.

Answer 2

I’m always using the “milliseconds since 1970” approach. This way I don’t have to worry about which timezone the date belongs to because the date in the database is always UTC.

Answer 3

There are really two questions here. First, what abstraction should you use in Java to represent time? Joda Time is definitely better than java.util.Date. If you are wondering whether to simply use a long -- I imagine you can't get away with that if you need to do any date manipulation or comparison. So, Joda Time.

And then it is definitely best to use TIMESTAMP for this in MySQL as it will be nearly identical storage-wise and MySQL will treat the value appropriately, as a date, when you want to use date functions on the column. JDBC drivers will also understand that it should be mapped to a date type.

I can't imagine you will have trouble porting a date type, represented correctly as a date in your schema, to another database, should you need to. I can imagine problems if you treat the date type as a bigint, which is less correct.

So, simply choose the most correct types here. I doubt there is any performance win available from choosing a less suitable type anyway.