DBTIMEZONE

1. Purpose

This document explains the purpose of the DBTIMEZONE function in IvorySQL, which implements Oracle-style database-level time zone querying.

Oracle provides two time-zone-related built-in functions: SESSIONTIMEZONE returns the time zone of the current session (changed via ALTER SESSION SET TIME_ZONE), and DBTIMEZONE returns a fixed database-level time zone (determined at CREATE DATABASE / ALTER DATABASE SET TIME_ZONE time, independent of the session). IvorySQL has already implemented SESSIONTIMEZONE; this feature completes DBTIMEZONE.

2. Feature Description

2.1. Basic Syntax

SELECT dbtimezone() FROM dual;

Returns a text-typed time zone value in UTC offset format ([+-]HH:MI), with a default value of '+00:00'.

2.2. Core Characteristics

Database-level, not session-level: The return value is fixed by ALTER DATABASE … SET ivorysql.dbtimezone = …, and is not affected by the current session’s SET timezone, in contrast to SESSIONTIMEZONE (session-level)
Can only be modified via ALTER DATABASE … SET: Setting SET ivorysql.dbtimezone = … directly within a session, and any form of ALTER ROLE … SET ivorysql.dbtimezone = … (ALTER ROLE rolename SET, ALTER ROLE rolename IN DATABASE dbname SET, ALTER ROLE ALL SET), will raise an error directly at the command level rather than being silently accepted; only ALTER DATABASE dbname SET ivorysql.dbtimezone = … takes effect, and only on the next connection to that database
Only superusers can set it by default: An ordinary user cannot execute ALTER DATABASE … SET ivorysql.dbtimezone = … even if they are the owner of a database they created themselves; a superuser can explicitly grant a non-superuser role permission to manage this setting via GRANT SET ON PARAMETER ivorysql.dbtimezone TO <role>;
All roles in the same database see the same value: DBTIMEZONE is a pure database property and does not support "different roles seeing different values within the same database" — any ALTER ROLE … SET attempting to differentiate by role is rejected directly at the command level (except ALTER ROLE … RESET, which is used to clean up stale legacy records)
Value format validation: Accepts a UTC offset in the form [+-]HH:MI, in the range -12:59 to +14:00 (consistent with Oracle), or a valid IANA time zone region name

3. Syntax Examples

3.1. Basic Usage

SELECT dbtimezone() FROM dual;
--  dbtimezone
-- ------------
--  +00:00
-- (1 row)

3.2. Comparison with SESSIONTIMEZONE: Unaffected by the Session Time Zone

SET timezone = 'Asia/Hong_Kong';
SELECT sessiontimezone() FROM dual;
--  sessiontimezone
-- -----------------
--  Asia/Hong_Kong

SELECT dbtimezone() FROM dual;
-- Still the fixed database value, unaffected by the SET timezone above
--  dbtimezone
-- ------------
--  +00:00

3.3. Fixing the Time Zone of a Database via ALTER DATABASE

ALTER DATABASE mydb SET ivorysql.dbtimezone = '+08:00';
-- Takes effect only after disconnecting and reconnecting (a new session)
\c mydb
SELECT dbtimezone() FROM dual;   -- +08:00

3.4. Granting a Non-Superuser Permission to Manage DBTIMEZONE for Their Own Database

-- The superuser performs a one-time grant:
GRANT SET ON PARAMETER ivorysql.dbtimezone TO app_owner;

-- app_owner can then set it for a database they own:
\c app_db app_owner
ALTER DATABASE app_db SET ivorysql.dbtimezone = '+05:30';
\c app_db app_owner
SELECT dbtimezone() FROM dual;   -- +05:30

4. Error Handling

4.1. Direct SET Within a Session Is Rejected

SET ivorysql.dbtimezone = '+08:00';
-- ERROR:  parameter "ivorysql.dbtimezone" cannot be set
-- DETAIL:  "ivorysql.dbtimezone" can only be set with ALTER DATABASE ... SET, not within a session or per-role.

4.2. ALTER ROLE … SET Is Rejected Directly at the Command Level

ALTER ROLE myrole IN DATABASE mydb SET ivorysql.dbtimezone = '+09:00';
-- ERROR:  parameter "ivorysql.dbtimezone" cannot be set
-- DETAIL:  "ivorysql.dbtimezone" can only be set with ALTER DATABASE ... SET, not within a session or per-role.
-- HINT:    Use ALTER DATABASE ... SET ivorysql.dbtimezone instead, or ALTER ROLE ... RESET ivorysql.dbtimezone to remove a stale per-role override.

ALTER ROLE myrole SET ivorysql.dbtimezone = '+09:00';           -- Also errors
ALTER ROLE ALL SET ivorysql.dbtimezone = '+09:00';              -- Also errors

-- RESET / RESET ALL are unaffected and can still be used to clean up stale legacy records:
ALTER ROLE myrole IN DATABASE mydb RESET ivorysql.dbtimezone;  -- OK

4.3. Invalid Value / Out-of-Range Offset

ALTER DATABASE mydb SET ivorysql.dbtimezone = 'not_a_zone';
-- ERROR:  invalid value for parameter "ivorysql.dbtimezone": "not_a_zone"
-- DETAIL:  "not_a_zone" is not a valid UTC offset (+/-HH:MI) or time zone name.

ALTER DATABASE mydb SET ivorysql.dbtimezone = '+15:00';
-- ERROR:  invalid value for parameter "ivorysql.dbtimezone": "+15:00"
-- DETAIL:  time zone offset "+15:00" is out of range for DBTIMEZONE (-12:59 to +14:00)

4.4. An Unauthorized Ordinary User Executing ALTER DATABASE

-- normal_user is the owner of mydb, but has not been granted permission via GRANT SET ON PARAMETER
\c mydb normal_user
ALTER DATABASE mydb SET ivorysql.dbtimezone = '+08:00';
-- ERROR:  permission denied to set parameter "ivorysql.dbtimezone"

Owning or creating a database does not imply permission to set every GUC parameter for that database — database-level permission (whether you can ALTER this database) and GUC-parameter-level permission (whether you can "set" this parameter) are two independent checks.

5. Cleanup

-- Restore the default value:
ALTER DATABASE mydb SET ivorysql.dbtimezone = '+00:00';
-- Or remove the database's custom setting entirely, falling back to the cluster default:
ALTER DATABASE mydb RESET ivorysql.dbtimezone;