Help Center/ GaussDB(DWS)/ Troubleshooting/ Database Parameter Modification/ How to Change a Database's Default Time Zone When the Database Time Is Different from the System Time
Updated on 2024-01-25 GMT+08:00
View PDF
Share

How to Change a Database's Default Time Zone When the Database Time Is Different from the System Time

Symptom

The database time is inconsistent with the operating system time. After a user queries the default database time SYSDATE, it is found that the database time is eight hours later than the Beijing time. As a result, the updated data cannot be accurately located.

Possible Causes

The UTC time zone is used to display and interpret GaussDB(DWS) database timestamps. If the operating system's time zone is not the UTC, time of the GaussDB(DWS) database will be inconsistent with the system time. Generally, the time zone of the cluster does not need to be changed. Configuring the time zone of the client may affect SQL execution.

Prerequisites

You are advised to modify the timezone parameter during off-peak hours.

Handling Procedure

Method 1: Change the default time zone of the database in a GaussDB(DWS) cluster.

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation tree on the left, click Clusters.
  3. In the cluster list, find the target cluster and click its name. The Basic Information page is displayed.
  4. Click the Parameter Modifications tab and change the value of parameter timezone to your time zone. Then click Save.
  5. In the Modification Preview dialog box, confirm the modification and click Save.
  6. (Optional) Check the Restart Cluster column of the timezone parameter. It indicates whether you need to restart the cluster to make the parameter modification take effect.

    The modification of the timezone parameter takes effect immediately. You do not need to restart the cluster.

Method 2: Run background commands to query and change the database time zone.

  1. Query the time zone and current time of the client. The time zone of the client is UTC, and the now() function returns the current time. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    show time zone;
 TimeZone
----------
 UTC
(1 row)
 
select now();
              now
-------------------------------
 2022-05-16 06:05:58.711454+00
(1 row)

  2. Create a data table. timestamp and timestamptz are common time types. timestamp does not store the time zone, and timestamptz does. 

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE timezone_test (id int, t1 timestamp, t2 timestamptz) DISTRIBUTE BY HASH (id);

\d timezone_test
           Table "public.timezone_test"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 id     | integer                     |
 t1     | timestamp without time zone |
 t2     | timestamp with time zone    |

  3. Insert the current time into the timezone_test table and query the current table. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    insert into timezone_test values (1, now(), now() );
show time zone;
 TimeZone
----------
 UTC
(1 row)
select * from timezone_test;
 id |             t1             |              t2
----+----------------------------+-------------------------------
  1 | 2022-05-16 06:10:04.564599 | 2022-05-16 06:10:04.564599+00
(1 row)

    The t1 (timestamp type) parameter discards the time zone information when saving data. The t2 (timestamptz type) parameter saves the time zone information.

  4. Set the time zone of the client to UTC-8. Query the timezone_test table again. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    set time zone 'UTC-8';
show time zone;
 TimeZone
----------
 UTC-8
(1 row)
select now();
              now
-------------------------------
 2022-05-16 14:13:43.175416+08
(1 row)

  5. Insert the current time to the timezone_test table and query the table. The value inserted to t1 is UTC-8 time, and t2 converts the time based on the time zone of the client. 

    1
2
3
4
5
6
7
    		 
    insert into timezone_test values (2, now(), now() );
select * from timezone_test;
 id |             t1             |              t2
----+----------------------------+-------------------------------
  1 | 2022-05-16 06:10:04.564599 | 2022-05-16 14:10:04.564599+08
  2 | 2022-05-16 14:15:03.715265 | 2022-05-16 14:15:03.715265+08
(2 rows)
    • The timestamp type is affected the time zone used when data is inserted. The query result is not affected by the time zone of the client.
    • The timestamptz type records the time zone information used when data is inserted. In a query, the time is converted based on the time zone of the client.