Comparison of MySQL and Oracle time zone settings

MYSQL: note that the time zone will affect the value of TIMESTAMP. The default value is TIME_ZONESYSTEM. You can modify setglobaltime_zone dynamically.

MYSQL: note that the time zone affects the value of TIMESTAMP. The default time zone is TIME_ZONE = SYSTEM. You can modify set global time_zone = dynamically.

MYSQL:
Note that the time zone affects the value of TIMESTAMP. The default value is TIME_ZONE = SYSTEM,
Dynamically modifiable
Set global time_zone = '+ 8:00 ';

Then
Add my. cnf and modify it permanently
Default-time_zone = '+ 8:00'

The current time zone. This variable is used to initialize the time zone for each client that
Connects. By default, the initial value of this is 'system' (which means, "use the value
System_time_zone ").
That is to say, each link uses this parameter as its default time zone, while TIMESTMAP varies depending on the client's time zone. If this parameter is set incorrectly, the TIMESTAMP time may be faulty.

MYSQL testing:
Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 12:10:13 |
+ --------------------- +
1 row in set (0.00 sec)

Mysql> select sysdate ();
+ --------------------- +
| Sysdate () |
+ --------------------- +
| 12:10:18 |
+ --------------------- +
1 row in set (0.00 sec)

Mysql> select current_timestamp from dual;
+ --------------------- +
| Current_timestamp |
+ --------------------- +
| 12:10:46 |
+ --------------------- +
1 row in set (0.00 sec)

Mysql> set time_zone = '+ 00:00 ';
Query OK, 0 rows affected (0.00 sec)

Mysql> select sysdate ();
+ --------------------- +
| Sysdate () |
+ --------------------- +
| 04:11:01 |
+ --------------------- +
1 row in set (0.00 sec)

Mysql> select now ();
+ --------------------- +
| Now () |
+ --------------------- +
| 04:11:04 |
+ --------------------- +
1 row in set (0.00 sec)

Mysql> select current_timestamp from dual;
+ --------------------- +
| Current_timestamp |
+ --------------------- +
| 04:11:06 |
+ --------------------- +
1 row in set (0.01 sec)

It can be seen that MYSQL's NOW (), SYSDATE (), current_timestamp follow the client's time zone.

Oracle:
In addition, the ORACLE time zone is divided
Dbtimezone and sessiontimezone
DBTIMEZONE is only related to timestamp with local time zone. It is saved to the database in the timestamp with local time zone type and is actually converted to the dbtimezone time.
Automatically add the client's SESSIONTIMEZONE offset. The document is as follows:

TimeStamp with Local Time Zone (TSLTZ) data stores internally the time converted to/from the database timezone (see point 3) from the timezone specified at insert/select time.

Note that the data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data, the current DBTIMZONE is used. when users retrieve the data, Oracle Database returns it in the users 'local session time zone from the current DBTIMEZONE.

Other TIME types have nothing to do with DBTIMEZONE, which is also the reason why the timestamp witch local time zone cannot be used to modify DBTIMEZONE, because if DBTIMEZONE is modified, a TIME error occurs.
In fact, the TIMESTAMP type of MYSQL and the timestamp witch local time zone type of ORACLE both return TIME based on the client TIME, But MYSQL can be simply set
The time_zone parameter is used to change the time zones of all connections, so that the returned time is correct.
The TIMESTAMP of ORACLE is different from that of MYSQL,
The TIMESTAMP of ORACLE is to be accurate to the last six digits of the second,
The TIMESTAMP of MYSQL aims to reduce the storage unit (DATETIME is 4 bytes and TIMESTAMP is 1 byte), but the time range is 1970 and starts until 2037, the return value is determined based on the client's time zone.

Sessiontimezone affects the client's time zone. timestamp witch local time zone also changes with this time zone. Other data types such as DATE and TIMESTAMP will not be affected.
You can also set the environment variable TZ = In the alter session.
For example:
Alter session set TIME_ZONE = '-05:00 ';
Or
Export TZ = 'Asia/Shanghai ';

Make a simple experiment
SQL> desc testtim;
Name Null? Type
-----------------------------------------------------------------------------
DATE1 TIMESTAMP (6)
DATE2 TIMESTAMP (6) WITH TIME ZONE
DATE3 TIMESTAMP (6) WITH LOCAL TIME ZONE
SQL> select * from testtim;