Changing the Default Database Time Zone

[Deleted User] · July 2018

Jim Knicely authored this tip.

When you first install Vertica, the default database time zone is controlled by the TZ environment variable. If TZ is undefined, Vertica uses the operating system time zone.

You can change the default database time zone later by altering the value of the TZ environment variable or the operating system time zone, and then restarting the database.

Example:

[dbadmin@s18384357 ~]$ date
Wed Jul 18 09:02:33 EDT 2018

[dbadmin@s18384357 ~]$ vsql -c "show timezone;"
   name   |  setting
----------+------------
 timezone | US/Eastern
(1 row)

[dbadmin@s18384357 ~]$ vsql -c "SELECT sysdate;"
          sysdate
----------------------------
 2018-07-18 09:02:36.969998
(1 row)

[dbadmin@s18384357 ~]$ export TZ="US/Pacific"

[dbadmin@s18384357 ~]$ admintools -t stop_db -d test_db; admintools -t start_db -d test_db
Info: no password specified, using none
        Connecting to database
        Issuing shutdown command to database
Database test_db stopped successfully
Info: no password specified, using none
        Starting nodes:
                v_test_db_node0001 (74.208.100.58)
        Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (DOWN)
        Node Status: v_test_db_node0001: (UP)
Database test_db: Startup Succeeded.  All Nodes are UP

[dbadmin@s18384357 ~]$ unset TZ

[dbadmin@s18384357 ~]$ date
Wed Jul 18 09:03:46 EDT 2018

[dbadmin@s18384357 ~]$ vsql -c "SELECT sysdate;"
          sysdate
----------------------------
 2018-07-18 06:03:50.226464
(1 row)

[dbadmin@s18384357 ~]$ vsql -c "show timezone;"
   name   |  setting
----------+------------
 timezone | US/Pacific
(1 row)

Have fun!