Different effects of Oracle and MySQL time zone settings on timestamp

Because of the recent international go to Oracle on MySQL, this inevitably involves time zone and timestamp issues. Do an experiment and summarize.

Oracle

First look at the definition of Oracle Concepts for timestamp:

The TIMESTAMP data type is an extension of the DATE data type. It Stores fractional seconds in addition to the information stored in the DATE data type. TIMESTAMPThe data type is useful for storing precise time values, and such as in applications, this must track event order.

timestamp  with  time  < Code dir= "LTR" >zone  is a variant of timestamp  that includes a < Span class= "bold" >time zone region name or A time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (coordinated Universal Time-forme Rly Greenwich Mean time). This data type was useful for preserving local time zone information.

TIMESTAMPWITH LOCAL TIME is another variant of this is sensitive to time ZONE TIMESTAMP zone information. It differs from TIMESTAMP WITH TIME ZONE The data stored in the database was normalized to the database time zone, and T He time zone information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. This data type was useful for date information, that's always-to was displayed in the time zone of the client system in a TW O-tier application.

• Timestamp is a more accurate storage of date, but it does not store time zone information, that is, not affected by Dbtimezone

• TIMESTAMP with time zone stores the client's timezone information, so it is not affected by Dbtimezone

• TIMESTAMP with LOCAL time zone type data does not store the client's time zone information, it converts the time that is sent to the client according to the database time zone, and stores the temporal information based on the unified database timezone, if the user does not specify the time zone information with the TIMESTAMP with The time zone defaults to the session timezone. Convert the time the client enters into a database based on the time of the DB timezone (this is the meaning of the Tmiezone setting, as the calculation ruler for the timestamp with LOCAL time zone type). When a user views this type of data, the server transforms the stored time data according to the time zone to which the session belongs, and the sessions in different time zones return different time data. Therefore, Oracle recommends that the database timezone be set to Standard Time UTC, which saves the overhead required for each conversion and improves performance.

The V$nls_parameters table does not only store the database's character set information, but also the display format for timestamp and timestamp with the local time zone:

Sql> SELECT * from v$nls_parameters where parameter in (' Nls_timestamp_format ', ' Nls_timestamp_tz_format ');

PARAMETER VALUE

------------------------------ ------------------------------

Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM

Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR

After reading the definition, we directly use the experiment to see their differences.

$ date-r

Sun, April 2016 13:50:32 +0800

Sql>select Dbtimezone,sessiontimezone from dual;

Dbtimezone Sessiontimezone

-------------------- --------------------

+08:00 +08:00

We're experimenting with inserting data.

Sql>create table timezone_test (t0 timestamp,t1 timestamp with time zone,t2 timestamp with local time zone);

Table created.

Sql>insert to Timezone_test select Current_timestamp,current_timestamp,current_timestamp from dual;

1 row created.

Sql>select * from Timezone_test;

T0 T1 T2

------------------------------ ------------------------------------ ------------------------------

24-apr-16 02.35.03.613433 pm 24-apr-16 02.35.03.613433 pm +08:00 24-apr-16 02.35.03.613433 pm

Sql>alter session Set time_zone= ' -2:00 ';

Session altered.

Sql>select * from Timezone_test;

T0 T1 T2

------------------------------ ------------------------------------ ------------------------------

24-apr-16 02.35.03.613433 pm 24-apr-16 02.35.03.613433 pm +08:00 24-apr-16 04.35.03.613433 AM

Notice that both T0 and T1 are constant. T2, that is, the value output from the timestamp with local time zone has changed. We then connect with sqlplus from another db in -2:00, and the effect is the same:

Sql>select Dbtimezone,sessiontimezone from dual;

Dbtimezone Sessiontimezone

-------------------- --------------------

+08:00-02:00

Sql>insert to Timezone_test select Current_timestamp,current_timestamp,current_timestamp from dual;

1 row created.

Sql>select * from Timezone_test;

T0 T1 T2

------------------------------ ------------------------------------ ------------------------------

24-apr-16 02.35.03.613433 pm 24-apr-16 02.35.03.613433 pm +08:00 24-apr-16 04.35.03.613433 AM

24-apr-16 05.03.35.050304 am 24-apr-16 05.03.35.050304 am-02:00 24-apr-16 05.03.35.050304 am

T2 for (-2)-(+8) =-10 time difference

Sql>alter Session Set Time_zone = Dbtimezone;

Session altered.

Sql>select * from Timezone_test;

T0 T1 T2

------------------------------ ------------------------------------ ------------------------------

24-apr-16 02.35.03.613433 pm 24-apr-16 02.35.03.613433 pm +08:00 24-apr-16 02.35.03.613433 pm

24-apr-16 05.03.35.050304 AM 24-apr-16 05.03.35.050304 am-02:00 24-apr-16 03.03.35.050304 PM

When Dbtimezone is different from Sessiontimezone, the data is inserted. T0 and T1 retain the time string information at insertion, not change, and T2 back to the time we actually inserted, which is 3:03 P.M..

Sql>select Dbtimezone,sessiontimezone from dual;

Dbtimezone Sessiontimezone

-------------------- --------------------

+08:00-02:00

Sql>insert into timezone_test select timestamp ' 2016-04-24 15:14:00 +3:00 ', timestamp ' 2016-04-24 15:14:00 +3:00 ', Timestamp ' 2016-04-24 15:14:00 +3:00 ' from dual;

1 row created.

Sql>select * from Timezone_test;

T0 T1 T2

------------------------------ ------------------------------------ ------------------------------

24-apr-16 02.35.03.613433 pm 24-apr-16 02.35.03.613433 pm +08:00 24-apr-16 02.35.03.613433 pm

24-apr-16 05.03.35.050304 AM 24-apr-16 05.03.35.050304 am-02:00 24-apr-16 03.03.35.050304 PM

24-apr-16 03.14.00.000000 pm 24-apr-16 03.14.00.000000 pm +03:00 24-apr-16 08.14.00.000000 pm

We notice that both T0 and T1 are the time in the timestamp string we inserted, while the T2 time is already (+3)-(-2) = 5, which has already been transformed.

Sql>alter session Set time_zone= ' +3:00 ';

Session altered.

Sql>select * from Timezone_test;

T0 T1 T2

------------------------------ ------------------------------------ ------------------------------

24-apr-16 02.35.03.613433 pm 24-apr-16 02.35.03.613433 pm +08:00 24-apr-16 09.35.03.613433 AM

24-apr-16 05.03.35.050304 am 24-apr-16 05.03.35.050304 am-02:00 24-apr-16 10.03.35.050304 am

24-apr-16 03.14.00.000000 pm 24-apr-16 03.14.00.000000 pm +03:00 24-apr-16 03.14.00.000000 pm

When we set the time zone back to the 3 time zone we specified when inserting, that is, the time that appears is the value of the time string we inserted.

Therefore, in Oracle, the TIMESTAMP with LOCAL time zone changes with the user's timezone (sessiontimezone), and the TIMESTAMP with time zone does not change with the user's timezone, simply speaking, The reference time for these two time types is different, one is referring to the user's time zone and one is the time zone of the reference database.

The timestamp with time zone is added to the timezone, and the time zone in which the data is inserted will not change to the time zone of the other or database, or the time zone where the person is located. When inserting data, if the time zone is written, it is displayed in the time zone at the time of insertion, not in the timezone of the database, or in the timezone of the person in which it is located, and does not convert the time.

The timestamp with local time zone is displayed without adding the back timezone. There is a problem with conversion. When inserting data, the time zone is converted to the time zone in which the database is located, or the time zone of the queried person's location to display the data. When inserting without a time zone, it is considered to be the same time zone as the database, so that when the query is in the same time zone as the database, the time is the same, and if it does not, it is converted to the time zone in which the query is located.

But will the values in the table change when you build the library, when the time is set incorrectly, or if you want to change the time zone in the future? Since I did not test the environment, I quoted the official answer:

For the time zone data type data, even if you update the database timezone, the original data will not be adjusted, only you export data, and then adjust the database time zone, and then import the original data. Therefore, in general, be sure not to adjust the database time zone. The official recommendation is to use UTC for the database time because of the good performance of this time zone. If the database time zone is not explicitly specified, the database uses the operating system's time zone, but if the operating system time zone is not a reasonable database time zone, the database uses the default time zone, UTC, and the value range for UTC is -12:00 to +14:00. Wait, why would there be +14? Baidu for a moment Kiribati. This is a magical website country.

Mysql

See MySQL 5.6 Reference manual definition of Timestamp

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ' 1970-01-01 00:00:01 ' UTC to ' 2038-01-19 03:14:07 ' UTC.

MySQL converts TIMESTAMP values from the "Current time zone" to "UTC for storage" and "back" from UTC to the current time zone For retrieval. (This does isn't occur for other types such as DATETIME.) By default, the current time zone for each connection is the server ' s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value for you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different fro m the value you stored. This occurs because the same time zone is not used for conversion in both directions. The current time zone is available as the value of the Time_zone system variable.

We look directly at the difference between date and timestamp in MySQL:

Datetime

1.8 bytes of storage (8 bytes storage)

2. Actual format storage (Just stores what are you having stored and retrieves the same thing which you have stored.)

3. Time zone Independent (It has nothing to deal with the TIMEZONE and Conversion.)

TIMESTAMP

1.4 Bytes of storage (time stamp value is stored in 4 bytes)

2. Values are saved in UTC (It stores the number of milliseconds)

3. Time zone conversion, the current time zone is converted when stored, and then converted back to the current time zone.

MySQL timestamp without Oracle complex, directly experiment.

$date-R

Sun, APR 2016 05:50:02-0700

[Email protected] 05:50:16>show variables like '%time_zone% ';

+------------------+--------+

| variable_name | Value |

+------------------+--------+

| System_time_zone | PDT |

| Time_zone | SYSTEM |

+------------------+--------+

[Email protected] 05:50:14>create table Timezone_test (

-T1 datetime default NULL,

-T2 timestamp NOT NULL default CURRENT_TIMESTAMP on update current_timestamp);

[Email protected] 05:52:11>select * from Timezone_test;

+---------------------+---------------------+

| T1 | T2 |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 05:52:11 |

+---------------------+---------------------+

[Email protected] 05:52:21>set time_zone= ';

[Email protected] 05:52:47>select * from Timezone_test;

+---------------------+---------------------+

| T1 | T2 |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 20:52:11 |

+---------------------+---------------------+

[email protected] 05:52:53>insert into timezone_test values (Current_timestamp,current_timestamp);

[Email protected] 05:52:59>select * from Timezone_test;

+---------------------+---------------------+

| T1 | T2 |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 20:52:11 |

| 2016-04-24 20:55:04 | 2016-04-24 20:55:04 |

+---------------------+---------------------+

[Email protected] 05:55:04>set time_zone= ' -7:00 ';

[Email protected] 05:55:12>show variables like '%time_zone% ';

+------------------+--------+

| variable_name | Value |

+------------------+--------+

| System_time_zone | PDT |

| Time_zone | SYSTEM |

+------------------+--------+

[Email protected] 05:55:22>select * from Timezone_test;

+---------------------+---------------------+

| T1 | T2 |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 05:52:11 |

| 2016-04-24 20:55:04 | 2016-04-24 05:55:04 |

+---------------------+---------------------+

The default value that was used when timestamp column was created in the table just now has the following conclusion, which is interesting to experiment with:

• This data column is refreshed when new records are created and existing records are modified:

TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp

• This field is set to the current time when a new record is created, but it is no longer refreshed when you modify it later:

TIMESTAMP DEFAULT Current_timestamp

• Set this field to 0 when creating a new record, and refresh it later when it is modified:

TIMESTAMP on UPDATE Current_timestamp

• Set this field to the given value when you create a new record, and refresh it later when you modify it:

TIMESTAMP DEFAULT ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp

That is, for example, the original MySQL time zone is -7:00, and then add a read library, this library is the time zone is in the, the actual value will be different? Let's experiment with the following:

SOURCE Library:

$date-R

Tue, APR 2016 01:47:52-0700

[Email protected] 02:01:21>show variables like '%time_zone% ';

+------------------+--------+

| variable_name | Value |

+------------------+--------+

| System_time_zone | PDT |

| Time_zone | SYSTEM |

+------------------+--------+

[Email protected] 02:01:25>create table Timestamp_test (t0 timestamp);

[email protected] 02:01:31>insert into timestamp_test values (CURRENT_TIMESTAMP);

[Email protected] 02:01:46>select * from Timestamp_test;

+---------------------+

| T0 |

+---------------------+

| 2016-04-26 02:01:46 |

+---------------------+

New Read library:

$date-R

Tue, April 2016 16:52:39 +0800

[Email protected] 05:02:54>show variables like '%time_zone% ';

+------------------+--------+

| variable_name | Value |

+------------------+--------+

| System_time_zone | CST |

| Time_zone | SYSTEM |

+------------------+--------+

[Email protected] 05:03:00>select * from Timestamp_test;

+---------------------+

| T0 |

+---------------------+

| 2016-04-26 17:01:46 |

+---------------------+

1 row in Set (0.00 sec)

[Email protected] 05:03:02>set time_zone= ' -7:00 ';

[Email protected] 05:03:48>select * from Timestamp_test;

+---------------------+

| T0 |

+---------------------+

| 2016-04-26 02:01:46 |

+---------------------+

Because of MySQL's main standby synchronization, synchronous or SQL, even if the synchronization of the row mode is only the actual synchronization of the values in the form of physical block transmission, in fact, the target is converted to SQL to execute, still with the time zone information, the client time zone conversion.

To summarize:

1. The role of timestamp in Oracle and MySQL is different

• In Oracle, timestamp is a more accurate storage of date, and is an extension of datetime, but it does not store time zone information

• TIMESTAMP with time zone stores the timezone information in Oracle

• In Oracle, the TIMESTAMP with LOCAL time zone does not store the time zone information, which converts temporal data into the data in the database timezone, but does not store time zone information; When the client retrieves the Oracle converts the time data stored in the database to the time data of the client session time zone and returns it to the client

• In MySQL, the timestamp is for fewer storage units (DateTime is 4 bytes, timestamp is 1 bytes) but the range is 1970 for some time start to 2037, and will be based on the client's time zone to determine the return value, MySQL's timestamp time zone is sensitive to this and is consistent with the Oracle's timestamp with LOCAL zone.

2, Oracle and MySQL function return is not the same

• The time zone information read by Oracle is based on the client side, and the current_timestamp is affected by the session timezone, and Sysdate,systimestap is unaffected

• The time zone information read by MySQL is server-side, now (), Sysdate (), and current_timestamp are not affected by the client connection time zone

• DTS is always client side, and when data arrives at DTS, it is unified into a pure string

3. Oracle's Dbtimezone is only related to timestamp with LOCAL time zone. The time_zone in MySQL directly affects all timestamp values.

4, in order to return the consistent data MySQL set Time_zone parameters, because he is used for each connection, but Oracle is best to use sysdate or systimestamp to directly take the DB server side time.

5, MySQL modified time zone information, as long as the client side of the time zone information is not changed, this has no effect.

6, Oracle modification time information, similarly, TIMESTAMP with the LOCAL times zone is not affected, TIMESTAMP and TIMESTAMP with the timing zone will be changed.

7, if the time zone information is not specified in the client, Oracle takes the client's time zone information as the subject, to be converted, MySQL takes the server side time zone information as the standard.

Different effects of Oracle and MySQL time zone settings on timestamp