Original Unified UTC Time scheme for Java projects

Unified UTC Time scheme for Java projects

Gods_ giant Ant

Introduction

The recent team's individual projects after the framework upgrade, some time value has a 8-hour error, because of the wrong time data in the database to understand the UTC time (the old version is considered to be Beijing time)

Considering the consistency of the future project for time understanding, I decided to unify the project to use UTC time, and after investigation, formed this article

MySQL database timezone and time Time type description database time zone

MySQL database has a time zone setting, using the system time zone by default

The current time zone can be queried using the following statement

Show variables like '%time_zone% ';

For my personal machine on the MySQL database time zone setting:

The Project line database time zone setting is as follows:

Cst--china Standard Time utc+8:00 China coastal Time (Beijing time) with visible database

Time Type description

Datetime

The actual format is stored (Just stores what are you having stored and retrieves the same thing which you have stored.)

is not related to time zone (It has nothing to deal with the TIMEZONE and Conversion.)

Timestamp

Value is saved in UTC milliseconds (it stores the number of milliseconds)

Conversion of time values based on current time zone settings during storage and retrieval

Because the timestamp is associated with the time zone, the online database time zone is set to Beijing time (that is, utc+8:00). Therefore, when the timestamp column is used in the database, the unification of UTC format time will most likely introduce errors if used improperly! The reasons are detailed later

Unified UTC Time Transformation Scheme brief unified time zone setting

The default time zone for the current process is set at project initialization with the Utctimezoneconfiguration type in the new project framework

@Configuration  Public class Implements servletcontextlistener{    publicvoid  contextinitialized (servletcontextevent Event) {        system.setproperty ("User.timezone", "UTC");        Timezone.setdefault (Timezone.gettimezone ("UTC"));      Public void contextdestroyed (Servletcontextevent event) {}}

Time type Joda how datetime is used

DateTime types can use Java.util.Date, but it is recommended to use a more convenient Joda datetime, this section describes Joda datetime serialization/deserialization usage

The Joda DateTime type is used to define interface input and output parameters, which require serialization/deserialization operations. Unlike the native date type, DateTime requires a little extra processing

1 , Model Type of Date field use type DateTime Alternative Date

The instance code is as follows

 public  class   = Utcdatetimeserializer.class  ) @JsonDeserialize (using  = Utcdatetimedeserializer.class  )  private   datetime datetime;  public   DateTime GetDateTime () { Span style= "color: #0000ff;"    >return   DateTime;  public  void   SetDateTime (datetime datetime) { this . DateTime = DateTime; }}

The implementation of Utcdatetimeserializer and Utcdatetimedeserializer class is shown in appendix

2 , Get Request Accept Time parameter

At this point, an efficient way to handle this is to use a string to accept the date parameter, as follows:

    @RequestMapping (value = "/xxx", method = requestmethod.get)    public commonresponse getXxx (@ Requestparam (value = "BeginTime") string begintimetext,                                 = "EndTime") string endtimetext) {        = datetime.parse (begintimetext). Withzone (DATETIMEZONE.UTC);         = DateTime.Parse (endtimetext). Withzone (DATETIMEZONE.UTC);        ...    }

DAO time Operations--a scenario that lists a DateTime for a database

Using the Joda datetime type as an example, the two methods that exist in a single DAO type are as follows:

     Public voidUpdateintID, datetime datetime) {String SQL= "UPDATE" + table_name + "SET datetime =?" WHERE id =? "; Jdbctemplate.update (SQL,NewTimestamp (Datetime.getmillis ()), id); }     PublicDateTime GetDateTime (intID) {String SQL= "Select datetime from" + table_name + "WHERE id =?"; List<DateTime> datetimelist = jdbctemplate.query (sql,NewObject[] {ID},NewRowmapper<datetime>() {@Override PublicDateTime Maprow (ResultSet RS,intRowNum)throwsSQLException {return NewDateTime (Rs.gettimestamp ("datetime"). GetTime ());        }        }); returnDatetimelist.size () > 0? Datetimelist.get (0):NULL; }

to insert or update data, pass the time parameter using new Timestamp (Datetime.getmillis ())

Read the time parameter, using the new DateTime (Rs.gettimestamp ("datetime"). GetTime ())

DAO time Operations--a scenario that lists timestamp for a database

Database timestamp type is appropriate for recording the last modified time of the data

Other scenarios suggest using datetime or int

programme I Change the session time zone to UTC Time

The operation of the timestamp column is not distinguished from the operation of the DateTime column, where the time zone of the data connection session needs to be set, the default is Beijing time, which needs to be set to UTC time, set by the following statement

Set time_zone = ' +0:00 ';

The database connection pool is used in the actual project, and when the DataSource is created, the time zone is set up as follows, which takes effect for all connections

Datasource.setinitsql ("Set time_zone = ' +0:00 '");

After this operation, the time zone is unified to UTC time, DAO in time operation, do not need to do special handling of timestamp

Scenario Two do not change the session time zone

There is a limit to the use of timestamp type data because the time zone is not changed

1. How to update timestamp Data

For timestamp columns in a database table, updates to their values should be maintained by the database itself and set at CREATE table as follows:

CREATE TABLE T1 (  TIMESTAMPDEFAULTcurrent_timestamponUPDATE  current_timestamp);

can be abbreviated as follows

CREATE TABLE T1 (  TIMESTAMP);

Do not allow programs to update timstamp column data autonomously

The online database timezone is Beijing time, and the date data received is treated as Beijing time, while the upper program business logic uses UTC time uniformly and the time zone is not uniform. Therefore, it is not possible for the program to update the timestamp column with a write operation SQL statement to avoid inconsistent understanding of date data in database records

Data for my measured data, timestamp column is updated by the program, update_time column is automatically updated by the database

The former shows UTC time, seemingly reasonable, but in fact error, the database internal storage time is utc-8:00

Update_time matches the database time zone setting, returns to Beijing time, internal actual storage UTC time

2. How to read timestamp Data

To avoid getting time zone-related time (Beijing time) from the database, force UTC time, use function Unix_timestamp to get 1970 to date seconds, convert to datetime multiplied by 1000 to milliseconds

     PublicDateTime Gettimestamp (intID) {String SQL= "Select Unix_timestamp (update_time) as Unix_timestamp from" + table_name + "WHERE id =?"; List<DateTime> datetimelist = jdbctemplate.query (sql,NewObject[] {ID},NewRowmapper<datetime>() {@Override PublicDateTime Maprow (ResultSet RS,intRowNum)throwsSQLException {return NewDateTime (Rs.getlong ("Unix_timestamp") * 1000);        }        }); returnDatetimelist.size () > 0? Datetimelist.get (0):NULL; }

Appendix MySQL time zone settings

Setting the global time zone requires Administrator privileges

Using the native system time zone

SET = SYSTEM;

Use UTC time

SET = ' +0:00 ';

Using GMT

SET = '  the ';

Set the current Connection session time zone

Set = ' +0:00 ';

Utcdatetimeserializer and Utcdatetimedeserializer

Utcdatetimeserializer completes the conversion of a DateTime object to a UTC time string in the format: YYYY-MM-DDTHH:MM:SSZ

Utcdatetimedeserializer completion time string conversion to a DateTime object, converted to UTC time zone

The specific implementation is as follows:

 Public classUtcdatetimeserializerextendsJsonserializer<datetime>{@Override Public voidSerialize (datetime datetime, Jsongenerator jsongenerator, serialize Rprovider provider)throwsIOException {String datetimeasstring=Datetime.withzone (DATETIMEZONE.UTC). toString (Becconstant.datetime_format);    Jsongenerator.writestring (datetimeasstring); }} Public classUtcdatetimedeserializerextendsJsondeserializer<datetime>{@Override PublicDateTime Deserialize (jsonparser jsonparser, Deserializationcontext deserializationcontext)throwsIOException {jsontoken Currenttoken=Jsonparser.getcurrenttoken (); if(Currenttoken = =jsontoken.value_string) {String datetimeasstring=jsonparser.gettext (). Trim (); returnDateTime.Parse (datetimeasstring). Withzone (DATETIMEZONE.UTC); }        return NULL; }}

Original Unified UTC Time scheme for Java projects