MYSQL Time Type & jdbc Time type

MySQL Time type: DATETIME & TIMESTAMP

• Length

  • The datetime type contains date and time, range 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59. DateTime can also contain milliseconds, for example, 2018-03-11 14::13:22.999
  • Timestamp can also store dates and times, but the range is 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC

• Time

  • DateTime storage time does not include time zone

  • Timestamp the UTC time Stored

    For example:

    2. CREATE table

       CREATE TABLE timestamp_n_datetime (id INT AUTO_INCREMENT PRIMARY KEY,ts TIMESTAMP,dt DATETIME);

    4. Insert now (current time zone is +08)

       insert into timestamp_n_datetime(ts,dt) values (now(), now());

    6. Select

       MariaDB [test]> select * from timestamp_n_datetime;+----+---------------------+---------------------+| id | ts                  | dt                  |+----+---------------------+---------------------+|  1 | 2018-03-11 14:35:01 | 2018-03-11 14:35:01 |+----+---------------------+---------------------+1 row in set (0.00 sec)

    8. Modify Time Zone

       MariaDB [test]> set time_zone = ‘+00:00‘;Query OK, 0 rows affected (0.02 sec)

    9. Query again

       MariaDB [test]> select * from timestamp_n_datetime-> ;+----+---------------------+---------------------+| id | ts                  | dt                  |+----+---------------------+---------------------+|  1 | 2018-03-11 06:35:01 | 2018-03-11 14:35:01 |+----+---------------------+---------------------+1 row in set (0.00 sec)

       The time that the timestamp is stored is adjusted to the new time zone, and the DateTime storage time is the same. When I first touched Java, there was a time zone problem when writing code, MyBatis MySQL, table field type Datetime,java type is java.util.Date. By looking at the Configuretimezone method of Java Connector source code Com.mysql.cj.mysqla.MysqlaSession.java,

       this.getServerVariable("system_time_zone")

       Debugging here found that the time zone setting read out from the system is CST. So the reason to find the database server

       MariaDB [(none)]> show variables like ‘%time_zone%‘;+------------------+--------+| Variable_name    | Value  |+------------------+--------+| system_time_zone | CST    || time_zone        | SYSTEM |+------------------+--------+2 rows in set (0.01 sec)

       Workaround:

       • Modify configuration file/etc/my.cnf.d/server.cnf (CentOS 7), need to restart MySQL

         # this is only for the mysqld standalone daemon[mysqld]default-time-zone=‘+08:00‘ # 将这行加到mysqld节点下

       • Modify the database service variable (not verified, if it is not convenient to modify the configuration file or restart the database.) Failed after restarting database?? ）

         set time_zone=‘+08:00‘;set global time_zone=‘+08:00‘;flush privileges;

       If you are using JDBC directly instead of MyBatis, use the

       The third parameter is specified. If no third parameter is specified, CST time is used (Settimestamp source)

JDBC represents the type of time

The information in this section is a bit old (https://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/guide/jdbc/getstart/mapping.doc.html)

These three types are inherited from Java.util.Date because Java.util.Date cannot match three JDBC time types.

• The date represents the time type java.sql.Date for SQL date that contains only the month and date (SQL in this case is the SQL-92 standard). The hour, minute, second, millisecond fields of the parent class are set to 0.

• Time indicates that only the temporal type of java.sql.Time is included for the duration of information. The year of the parent class, month, the day field is set to 1970, January, number 1th. This time is the "zero era" inside Java.

• Timestamp contains Date+time plus a nanosecond (nanosecond) field java.sql.Timestamp for SQL Timestamp information. This type expands the extra nanosecond field for java.util.Date.

Other commands get current Time_zone

SELECT @@global.time_zone, @@session.time_zone;

If the query result is system, indicates that the MySQL server is using the time zone date -Rcommand to view the system time zone.

MYSQL Time Type & jdbc Time type