Working with time

The current state of affairs over time

Since the time when JDBC was invented and its interfaces were standardized, 20 years have passed, and during this time a lot of things have changed.

Firstly, the world has become global and now one server can serve users from all over the world. The internet speed is up. Therefore, another data type was added to SQL to work with time. Now the types look like this:

• DATE - stores the date: year, month, day.
• TIME - stores time: hours, minutes, seconds.
• TIMESTAMP - stores a specific point in time: date, time and milliseconds.
• TIMESTAMP WITH TIME ZONE - TIMESTAMP and time zone (zone name or offset).

Secondly, Java introduced the DateTime API for global time management. It has the following classes:

• Date and time :
  • LocalDate
  • LocalTime
• Exact moment :
  • java.time.Instant
  • java.time.LocalDateTime
  • java.time.OffsetDateTime
  • java.time.ZonedDateTime
• Time with time zone :
  • java.time.OffsetDateTime
  • java.time.ZonedDateTime

The third interesting point is that many SQL clients would like to receive time from the server already in their local zone . Of course, you can convert time on the fly, but it's not convenient, and there are mistakes.

For example, I want to get all the tasks for today from the database. SQL Server has a CURDATE() function for this. Only here the server is in the USA, and I am in Japan. And I would like him to return all the records for “my today”, and not “his today”.

In general, the SQL server must also be able to work smartly with clients in different time zones.

Modern problems require modern solutions

In principle, new types from the Java DateTime API and types from SQL can be conveniently mapped. To represent the DATE type in Java, you need to use the java.time.LocalDate class from the JDK 8 DateTime API.

The TIME type from the database can be represented by two types from Java: java.time.LocalTime and java.time.OffsetTime . Nothing complicated either.

A specific point in time, represented by the TIMESTAMP type in the database, can be represented in Java by 4 types:

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

And finally, TIMESTAMP WITH TIME ZONE can 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 :)

I will write it in the form of a table, so it will be easier:

Getting the date

I have a good news for you. First in a long time. We can get around the limitation of the getDate() method , which returns a java.sql Date type.

The point is that the objectresult setthere is another interesting method - getObject() . This method takes two parameters: a column and a type, and returns the value of the column converted to the given type. The general form of the method is as follows:

ClassName Name = getObject(column, ClassName);

And if you want to convert the DATE type to the java.time.LocalDate type , then you need to write something like:

LocalDate localDate = results.getObject(4, LocalDate.class);

And any TIMESTAMP in general can be converted to a bunch of types:

java.time.Instant instant = results.getObject(9, java.time.Instant.class);
java.time.LocalDateTime local = results.getObject(9, java.time. LocalDateTime.class);
java.time.OffsetDateTime offset = results.getObject(9, java.time. OffsetDateTime.class);
java.time.ZonedDateTime zoned = results.getObject(9, java.time. ZonedDateTime.class);

Important! This code will not work if you have an outdated MySQL JDBC Driver . Pay attention to the version of "mysql-connector-java" written in your pom.xml, or added to the Libraries in the project settings.

By the way, in the same way, you can get around the inability to store null for primitive types. If a table column is of type INT, then there are a couple of ways to get null from it. See example below:

Integer id1 = results.getObject(8, Integer.class);    	 // this will work
Integer id2 = results.getObject(8, int.class);                 //this will also work
int id3 = results.getObject(8,  Integer.class);            	//method will return null, JVM will throw NPE
int id4 = results.getObject(8,  int.class);                    	//method will return null, JVM will throw NPE

Timezone setting in MySQL

mysql://localhost:3306/db_scheme?Name=meaning&Name=meaning

So, three parameters have been added to work with time zones in MySQL. You can pass these parameters when you establish a connection to the server.

Below I will give a table with them:

Using the connectionTimeZone parameter , we select the time zone (time zone) in which all requests will be executed. From the client's point of view, the server is running in the specified time zone.

The forceConnectionTimeZoneToSession parameter causes the session time_zone variable to be ignored and replaced with connectionTimeZone.

Finally, the preserveInstants parameter controls the exact-time-conversion between the JVM's timeZone and connectionTimeZone.

The most common configurations are:

• connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=false - corresponds to the old MySQL JDBC driver version 5.1 with useLegacyDatetimeCode=true.

• connectionTimeZone=LOCAL & forceConnectionTimeZoneToSession=true is a new mode that provides the most natural way to handle date and time values.

• connectionTimeZone=SERVER & preserveInstants=true - Corresponds to the old MySQL JDBC driver version 5.1 with useLegacyDatetimeCode=false.

• connectionTimeZone=user_defined & preserveInstants=true - Helps overcome the situation where the server's time zone cannot be recognized by the connector because it is set as a generic abbreviation such as CET/CEST.

Yes, dates are an interesting topic and there are many problems with them. As the saying goes: it's scary, of course, but I'm not pissed either! :)

1

Task

Module 4. Working with databases, level 7, lesson 4

Locked

Getting the date

task0708

Open