Changing the Default Database Time Zone

[Deleted User]

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:

[[email protected] ~]$ date
Wed Jul 18 09:02:33 EDT 2018

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

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

[[email protected] ~]$ export TZ="US/Pacific"

[[email protected] ~]$ 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

[[email protected] ~]$ unset TZ

[[email protected] ~]$ date
Wed Jul 18 09:03:46 EDT 2018

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

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

Have fun!