Date vs. Millisecond | for scalability, save, search and get time in Java + MySQL (or other Db's)
What's the most beneficial in Java and DB for DateTime? (Using JodaTime as date)
(DateTime Object (Java) + TIMESTAMP (DB)) VS (milliseconds long (Java) + BIGINT (DB)
for using DateTime information in a Java web application supported by an underlying database
Interesting areas
- manipulating, processing and using memory in Java
- savings using effective storage space in MySQL database
- ease of transferring the BIGINT / TIMESTAMP column to other databases
- ease of database search for BIGINT / TIMESTAMP or between two BIGINT / TIMESTAMPs
eg. Let's say I had an event with start and end DateTime. Is it faster to search for events by date using BIGINT in the DB than TIMESTAMPS
I can replace the base DB as there are scalability and search issues.
Would keep DateTime as TIMESTAMP in DB DB problems when migrating to another DB like Oracle?
I am currently using Joda DateTime in java and then store the millisecond of that value.
When retrieving it, I convert the milliseconds back to a DateTime object and display it.
source to share
There are two questions here. First, what abstraction should be used in Java to represent time? Joda Time is definitely better than java.util.Date. If you're wondering if it's easy to use long - I guess you can't avoid it if you need to do any kind of manipulation or date matching. So it's Joda's time.
And then it is best to use TIMESTAMP for this in MySQL, as it will be almost identical in size 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 they have to be mapped to a date type.
I can't imagine failing to migrate the date type, represented correctly as a date in your schema, to another database if you need to. I can imagine problems if you treat the date type as bigint, which is less correct.
So, just pick the most correct types here. I doubt that in any case, the gains from the winnings depend on the choice of the less suitable type.
source to share
Of course it depends on what you want to do with the data, but I would recommend using a native DB type for time and date (whatever that might be) for storage in the DB. This is the standard for databases, and most date / time functions in a database expect data in this form.
A 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. Thus, while it is suitable for dates close to "now" (for example, creation / modification dates), it is not suitable for possible historical dates such as date of birth. Therefore, use TIMESTAMP if you are completely sure that you will never need a historical date.
source to share