Course Module 4. Working with databases - Lecture: Date Mapping

4.1 Excursus into history

The task of saving Java objects to the database was relevant almost immediately after the creation of the Java language. At that time, there was only one data type in the Java language, Date, which stored time according to the UNIX-time standard: as the number of milliseconds since 1970.

Well, in the databases at that time there were already different data types for dates, at least there were separate types for date, time and date + time:

DATE
TIME
TIMESTAMP

Therefore, the creators of the Java language added a special package to it - java.sql, which contained classes:

java.sql.date
java.sql.Time
java.sql.timestamp

Mapping these classes is a real pleasure:


@Entity
public class TemporalValues {
 
	@Basic
    private java.sql.Date sqlDate;
 
	@Basic
    private java.sql.Time sqlTime;
 
    @Basic
    private java.sql.Timestamp sqlTimestamp;
}

But since programmers used to work with the class java.util.Date, Hibernate added a special annotation @Temporalin order to control the mapping of the Date type.

Example:

// If the annotation is missing, then the database will have a TIMESTAMP type
Date dateAsTimestamp;

@Temporal(TemporalType.DATE) // will be mapped to DATE type
Date dateAsDate;

@Temporal(TemporalType.TIME) // will be mapped to TIME type
Date dateAsTime;

The type java.util.Calendarand java.util.Datedefault type use the TIMESTAMP type to represent them in the database.

4.2 New time

At present, with mapping, everything is much simpler and better. All databases support 4 types of data to work with time:

DATE - date: year, month and day.
TIME - time: hours, minutes, seconds.
TIMESTAMP - date, time and nanoseconds.
TIMESTAMP WITH TIME ZONE - TIMESTAMP and time zone (zone name or offset).

To represent the type DATEin Java, you need to use a class java.time.LocalDatefrom the JDK 8 DateTime API.

TypeTIMEfrom the database can be represented by two types from Java: java.time.LocalTimeand java.time.OffsetTime. Nothing complicated.

And the exact date and time represented by the typeTIMESTAMPin the base, in Java it can be represented by 4 types:

java.time.Instant
java.time.LocalDateTime
java.time.OffsetDateTime
java.time.ZonedDateTime

And finallyTIMESTAMP WITH TIME ZONEcan be represented by two types:

java.time.OffsetDateTime
java.time.ZonedDateTime

Since you are already familiar with the DateTime API , remembering this matter will not be difficult for you :)

Mapping them is pure pleasure:

@Basic
private java.time.LocalDate localDate;

@Basic
private java.time.LocalTime localTime;

@Basic
private java.time.OffsetTime offsetTime;

@Basic
private java.time.Instant instant;

@Basic
private java.time.LocalDateTime localDateTime;

@Basic
private java.time.OffsetDateTime offsetDateTime;

@Basic
private java.time.ZonedDateTime zonedDateTime;

The annotation @Basicmeans that the field should be processed automatically : Hibernate will decide on which column and type this field should be mapped.

4.3 Working with time zones

If the time zone is part of a date, then storing them in the database is simple - just like a regular date:

@Basic
private java.time.OffsetDateTime offsetDateTime;

@Basic
private java.time.ZonedDateTime zonedDateTime;

However, if you want to store timezones separately from the date:

@Basic
private java.time.TimeZone timeZone;

@Basic
private java.time.ZoneOffset zonedOffset;

Then Hibernate will store them in VARCHAR type by default. Which, in fact, is logical, since the TimeZone usually has a string name like "UTC + 3" or "Cairo".

4.4 Setting your own time zone

When you work with saving dates to the database, you will come across the fact that there are already 4 places where you can set the current time zone:

Server operating system;
DBMS;
Java application
Hibernate.

If the DBMS does not specify a time zone (TimeZone), then it will take it from the operating system settings. This can be inconvenient, since backup DBMSs are often located in other data centers that have their own time zone.

Therefore, almost all DBMS admins set a single zone so that data can be easily transferred from one server to another.

The situation is similar with a Java application. It can also be run on different servers in different data centers, so it usually has an explicit time zone.


java -Duser.timezone=UTC ...

Or while the program is running:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

And, of course, Hibernate allows you to set your time zone explicitly.

First, it can be specified when configuring the SessionFactory:

settings.put(
    AvailableSettings.JDBC_TIME_ZONE,
    TimeZone.getTimeZone("UTC")
);

Secondly, the time zone can be specifiedfor a specific session:

Session session = sessionFactory()
    .withOptions()
    .jdbcTimeZone(TimeZone.getTimeZone("UTC"))
    .openSession();

4.5 @TimeZoneStorage annotation

It often happens that programmers started designing a database based on working in one country (and one time zone), and then after a couple of years they needed to add support for working in different time zones.

Therefore, they simply added a separate column to the database to store the time zone. This is such a common situation that Hibernate has added a special annotation that allows you to store the TimeZone of a specific date in a separate column.

Example:

@TimeZoneStorage(TimeZoneStorageType.COLUMN)
@TimeZoneColumn(name = "birthday_offset_offset")
@Column(name = "birthday_offset")
private OffsetDateTime offsetDateTimeColumn;

@TimeZoneStorage(TimeZoneStorageType.COLUMN)
@TimeZoneColumn(name = "birthday_zoned_offset")
@Column(name = "birthday_zoned")
private ZonedDateTime zonedDateTimeColumn;

This is a crutch. But there is also an excuse for it: it appeared at a time when the DateTime API did not yet exist. And it was impossible to store TimeZone in the class java.util.Date.

I really hope that you will not often see this in your code.