Java Date and Time with Mysql

Question

I am working with Mysql x JAVA

What would be the best way to work with dates and times? Separate (Date, Time), together (Datetime, timestamp)?

And in JAVA, whereas I’m using a Rest json API and a PARCELABLE class to write to an Sqlite database and then manipulate the data.

And enabling future changes such as time zone, GMT, other regions.

Answer 1

The best way to work with dates and times depends a lot on how this information will be used and what it is for.

For example, if you are going to record the date of birth in a simple user registration, you may only need the date (day, month and year), and you don’t even have to worry about the time. But if it is a maternity system, or any other that needs the exact day and time of birth, then it will be necessary to record the date and time, and probably the place (or Timezone itself).

If you need to record the exact moment something happened, then the timestamp (or UTC date and time) would be the most indicated. Local dates and times may change, such as daylight saving time, which causes times to be skipped or repeated. The instant in UTC/timestamp represents a specific point in the timeline and there is no ambiguity, no problems with daylight saving time (or any other change in time zone).

For example, when daylight saving time ends in Brazil: at midnight, the clocks are put back an hour, back to 11 pm. If you have this date with the time of 11 pm, how do you know if it refers to the time before or after daylight savings time ended? You can use UTC or the corresponding timestamp, or offset (the difference with UTC - in the case of Brasilia time, are 3 hours behind UTC in normal time (-03:00) and 2 hours behind UTC in daylight saving time (-02:00)). But for these cases, I think the best is to record in UTC, and then you convert to other timezones, as needed (show to the user, for example).

---

Although there is a general recommendation to "always use UTC", there is one case where it is not the most recommended: when you want to record future events.

Example: let’s assume that we are in 2016, and a user registered an event for 31 October 2018, at 10 am, Brasilia time.

In 2016, the summer time rule said that it begins on the third Sunday in October. That is, on October 30, 2018, it would already be daylight saving time, and the offset used (the difference with respect to UTC) is minus two hours (-02:00).

Therefore, the date/time and offset of the future event would be 2018-10-31T10:00-02:00, which in UTC corresponds to 2018-10-31T12:00Z (the "Z" at the end means that the date/time is in UTC, according to the format defined by ISO 8601). Then you write this amount into the bank.

So far so good. Only daylight saving time is set by the government, and it can change its mind at any time. And in this case, it really changed: the Brazilian Summer Time rule was changed by a decree published in December 2017. According to the new rule, from 2018 the start of daylight saving time is on the first Sunday in November.

Therefore, by the new rules, 30 October 2018 is no longer in daylight saving time, which means that on this day Brasilia time is still 3 hours behind UTC. And converting the value into UTC that had been recorded in the bank (2018-10-31T12:00Z) for offset -03:00, the result is 2018-10-31T09:00-03:00 (9 am, one hour before it was registered by the user).

For this specific case, it is best to record the date and time without offset, and save the Timezone name in a separate field. In case, you could use America/Sao_Paulo, which is the identifier of the IANA (the timezone information database that Java uses) for Brasilia time: IANA uses the name of the most populous city in each region that has the same rules to define its local time.

If this is not your users' Timezone, you can check the list of all available ones. In Java, you can use ZoneId.getAvailableZoneIds() (Java >= 8) or TimeZone.getAvailableIDs() (Java <= 7) to know which Ids are available.

It is also possible to know the timezones used by a given country in the IANA repository. The timezones of Brazil, for example, are here.

IANA identifiers are the recommended form, as they have the entire offsets history of each region of the planet, including the dates when daylight saving time starts and ends, and which offsets before and after.

---

IANA always launches frequent updates of its database, as the timezones change all the time. To update the information in the JVM, you can use the TZ Updater Tool, that updates this data without having to change the Java version.

Or you can follow at this link which version of the IANA database each version of Java has. The Brazilian summer time update, for example, is already in versions 10, 8u172, 7u181 and 6u191.

---

Specifically speaking in your case, Sqlite does not have specific date types, saving everything in strings, as the documentation explains. Mysql already has it specific types which you can use as per what you need (only date, date and time, timestamp, etc).

In Java 8 specific classes were created for these cases as well, in the package java.time. A LocalDate represents only one date (day, month and year, without any interference from timezones or daylight savings time), LocalDateTime (date and time without Timezone), LocalTime (only the time), OffsetDateTime (fixed date, time and offset, no daylight saving) and ZonedDateTime (date, time and Timezone, with daylight saving time rules, etc).

From JDBC 4.2 it is possible to use the classes of java.time, provided that the database driver is also compatible. Otherwise, you will have to use the old API (java.util.Date, java.sql.Date, java.sql.Time and java.sql.Timestamp).