Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL and Oracle time zone settings compare     - Ubuntu is not in the sudoers file problem solving (Linux)

- Ubuntu Gnome and Fedora 22 Gnome desktop, extended to achieve global menu (Linux)

- Understand ASP.NET 5 running the command: DNVM, DNX, and DNU (Server)

- Getting the Linux shell expr use (Programming)

- Denyhosts prevent hackers using SSH scanning (Linux)

- Use Linux firewall camouflage defense hacked (Linux)

- How to clear the v $ archived_log view expiration information (Database)

- Make Linux more secure server tips (Linux)

- MongoDB3.0.6 wiredtiger MMAPv1 (Database)

- map and hash_map STL containers (Programming)

- Echo Command Examples (Linux)

- iOS in Singleton (Programming)

- Git use and interpretation of common commands (Linux)

- Oracle 11g through SCN do incremental backup repair standby library detailed process (Database)

- How do you prevent other users from accessing your home directory in Linux (Linux)

- Linux mount command Detailed (Linux)

- How to track performance issues when using the Unity Game Development Android (Programming)

- Linux operating system security settings initial understanding (Linux)

- Java, hashcode, equals and == (Programming)

- Java is simple to read and write HDFS Demo (Programming)

 
         
  MySQL and Oracle time zone settings compare
     
  Add Date : 2018-11-21      
         
       
         
  MYSQL:
Note that the time zone affects TIMESTAMP values, the system defaults to the time zone is TIME_ZONE = SYSTEM,
 Can be modified dynamically
set global time_zone = '+8: 00';

then
my.cnf plus, permanent changes
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 of
 system_time_zone ").
This means that each link will use this parameter as his default time zone, and TIMESTMAP is based on different time zones and different clients, so if you have a misunderstanding if this parameter is set to cause problems with TIMESTAMP Time

MYSQL tests:
mysql> select now ();
 + --------------------- +
 | Now () |
 + --------------------- +
 | 2015-06-12 12:10:13 |
 + --------------------- +
 1 row in set (0.00 sec)


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


 mysql> select current_timestamp from dual;
 + --------------------- +
 | Current_timestamp |
 + --------------------- +
 | 2015-06-12 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 () |
 + --------------------- +
 | 2015-06-12 04:11:01 |
 + --------------------- +
 1 row in set (0.00 sec)


 mysql> select now ();
 + --------------------- +
 | Now () |
 + --------------------- +
 | 2015-06-12 04:11:04 |
 + --------------------- +
 1 row in set (0.00 sec)


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

Visible MYSQL of NOW (), SYSDATE (), current_timestamp all follow the client time zone to go.

Oracle:
Another said that the time zone problem ORACLE, ORACLE time zone divided
dbtimezone and sessiontimezone
Wherein only when DBTIMEZONE and TIMESTAMP WITH LOCAL TIME ZONE related to the TIMESTAMP WITH LOCAL TIME ZONE type stored in the database is actually converted to DBTIMEZONE time taken
 SESSIONTIMEZONE automatically add the offset of the client, the following documents:

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.

While other types of time and DBTIMEZONE irrelevant, which is why with TIMESTAMP WITCH LOCAL TIME ZONE modify DBTIMEZONE not the reason, because if you modify the DBTIMEZONE will lead to the wrong time.
 In fact MYSQL and ORACLE is the type of TIMESTAMP TIMESTAMP WITCH LOCAL TIME ZONE types are based on the client's return time to time, but you can simply set the MYSQL
 time_zone parameters to change time zones all connected, so time to return correctly.
 In the description of what ORACLE and MYSQL TIMESTAMP TIMESTAMP is completely different,
ORACLE of TIMESTAMP to accurate to six seconds later,
 The TIMESTAMP and MYSQL for less storage unit (DATETIME 4 bytes, TIMESTAMP is one byte) but the range of the start of 1970 to sometime in 2037, and will be judged according to the time zone of the return value of a client

 And sessiontimezone, the impact on the client's time zone, TIMESTAMP WITCH LOCAL TIME ZONE will follow the time zone change, other data types, such as DATE, TIMESTAMP, etc. will not be affected
 You can then set the ALTER SESSION can also set the environment variable TZ =
Such as:
ALTER SESSION SET TIME_ZONE = '-05: 00';
or
export TZ = 'Asia / Shanghai';

Do 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;

 DATE1
 -------------------------------------------------- -------------------------
 DATE2
 -------------------------------------------------- -------------------------
 DATE3
 -------------------------------------------------- -------------------------
 12-JUN-15 11.40.02.000000 AM
 12-JUN-15 11.40.02.000000 AM +08: 00
 12-JUN-15 11.40.02.000000 AM


 SQL> alter SESSION SET TIME_ZONE = '-05: 00';

 Session altered.

 SQL> select * from testtim;

 DATE1
 -------------------------------------------------- -------------------------
 DATE2
 -------------------------------------------------- -------------------------
 DATE3
 -------------------------------------------------- -------------------------
 12-JUN-15 11.40.02.000000 AM
 12-JUN-15 11.40.02.000000 AM +08: 00
 11-JUN-15 10.40.02.000000 PM

 


Finally ORACLE commonly used function of time taking different:

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP, that is date time similar to CURRENT_DATE but the datatype is TIMESTAMP.

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE, that is date time similar to CURRENT_DATE but the datatype is TIMESTAMP WITH TIME ZONE.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE. Unlike SYSDATE, which you can set to a constant using FIXED_DATE, SYSTIMESTAMP will give the system date even though FIXED_DATE is set.

"SYSDATE" and "SYSTIMESTAMP" are purely dependent on the operating system clock, hence it IS depending on the timezone information of this operating system and / or the operating system settings when the database and listener where started.
Obviously LOCALTIMESTAMP and CURRENT_TIMESTAMP client SESSIONTIMEZONE have been affected, and SYSDATE are not affected his return must be SESSIONTIMEZONE time server ORACLE settings.
If you need to change the client's values must SYSDATE
1, the next user to modify the server ORACLE TZ
 2, restart the database

 Such as:

export TZ = 'UTC';
After viewing the server SYSDATE
 SQL> select to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') from dual;


 TO_CHAR (SYSDATE, 'YY
 -------------------
 2015-06-12 04:06:34
Logically speaking, the client should return this value
 But any client then returns

SQL> select to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') from dual;
 TO_CHAR (SYSDATE, 'YYYY-MM-DDHH2
 ------------------------------
 2015-06-12 12:08:19

Restart
SQL> select to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') from dual;
 TO_CHAR (SYSDATE, 'YYYY-MM-DDHH2
 ------------------------------
 2015-06-12 04:09:19

Clients normally.

 in conclusion:
1, ORACLE and MYSQL different timestamp
ORACLE of TIMESTAMP to accurate to six seconds later,
The TIMESTAMP and MYSQL for less storage unit (DATETIME 4 bytes, TIMESTAMP is one byte) but the range of the start of 1970 to sometime in 2037, and will be judged according to the time zone of the return value of a client
TIMESTAMP Time Zone MYSQL ORACLE sensitive to this point and the TIMESTAMP WITH LOCAL TIME ZONE consistent.
2, ORACLE and MYSQL function returns is not the same
ORACLE:
LOCALTIMESTAMP and CURRENT_TIMESTAMP client SESSIONTIMEZONE have been affected, and SYSDATE, SYSTIMESTAP not affected his return must be SESSIONTIMEZONE time server ORACLE set
MYSQL:
 NOW (), SYSDATE (), CURRENT_TIMESTAMP client connections are subject to the impact zone.

3, oracle DBTIMEZONE of little use, and only about TIMESTAMP WITH LOCAL TIME ZONE.
4, in order to return a consistent set of data MYSQL TIME_ZONE parameters can because he is each connection will be used, but it is preferable to use SYSDATE or ORACLE SYSTIMESTAMP take time to direct the service side SESSIONTIMEZONE under.
     
         
       
         
  More:      
 
- Editor of the popular Linux Gvim (Linux)
- Linux VMware virtual machine after the cloning of the card can not start to solve (Linux)
- MongoDB 3.2 to upgrade from 3.0.7 (Database)
- Oracle () trunc function usage (Database)
- Debian 7 and Debian 8 users how to install Oracle Java 8 (Linux)
- Sysdig: system troubleshooting tool (Linux)
- Install Oracle JDK 8 and JVM class loading mechanism in Linux (Linux)
- MySQL + Corosync + Pacemaker + DRBD build highly available MySQL (Server)
- Three methods easy data encryption on Linux (Linux)
- CentOS 7 Configure logging (VirtualBox) (Linux)
- JEdit 5.2 Pro Edition installation on Ubuntu 14.04 (Linux)
- Boot automatically remove Linux operating history records (Linux)
- jQuery plugin dynamic label generation (Linux)
- Sort search algorithm Java - application examples with recursive dichotomy (Programming)
- How to convert images, audio and video formats on Ubuntu (Linux)
- Common data structures and functions of Linux process scheduling (Programming)
- CentOS 7.0 Automatic installation CD-ROM production Comments (Linux)
- NAT and firewall under Linux (Linux)
- 11 examples in Linux df command (Linux)
- How to install Ubuntu applications Device 7 (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.