11.3. RDB$TIME_ZONE_UTIL

A package of time zone utility functions and procedures.

11.3.1. Function DATABASE_VERSION

RDB$TIME_ZONE_UTIL.DATABASE_VERSION returns the version of the time zone database.

Return type: VARCHAR(10) CHARACTER SET ASCII.

Example 

  |select rdb$time_zone_util.database_version()
  |from rdb$database;

Returns:

  |DATABASE_VERSION
  |================
  |2023c

11.3.2. Procedure TRANSITIONS

RDB$TIME_ZONE_UTIL.TRANSITIONS returns the set of rules between the start and end timestamps for a named time zone.

Input parameters

  • RDB$TIME_ZONE_NAME type CHAR(63)

  • RDB$FROM_TIMESTAMP type TIMESTAMP WITH TIME ZONE

  • RDB$TO_TIMESTAMP type TIMESTAMP WITH TIME ZONE

Output parameters:

RDB$START_TIMESTAMP

type TIMESTAMP WITH TIME ZONE — The start timestamp of the transition

RDB$END_TIMESTAMP

type TIMESTAMP WITH TIME ZONE — The end timestamp of the transition

RDB$ZONE_OFFSET

type SMALLINT — The zone’s offset, in minutes

RDB$DST_OFFSET

type SMALLINT — The zone’s DST offset, in minutes

RDB$EFFECTIVE_OFFSET

type SMALLINT — Effective offset (ZONE_OFFSET + DST_OFFSET)

Example 

  |select *
  |  from rdb$time_zone_util.transitions(
  |    'America/Sao_Paulo',
  |    timestamp '2017-01-01',
  |    timestamp '2019-01-01');

Returns (RDB$ prefix left off for brevity):

  |             START_TIMESTAMP                END_TIMESTAMP ZONE_OFFSET DST_OFFSET EFFECTIVE_OFFSET
  |============================ ============================ =========== ========== ================
  |2016-10-16 03:00:00.0000 GMT 2017-02-19 01:59:59.9999 GMT       -180        60             -120
  |2017-02-19 02:00:00.0000 GMT 2017-10-15 02:59:59.9999 GMT       -180         0             -180
  |2017-10-15 03:00:00.0000 GMT 2018-02-18 01:59:59.9999 GMT       -180        60             -120
  |2018-02-18 02:00:00.0000 GMT 2018-10-21 02:59:59.9999 GMT       -180         0             -180
  |2018-10-21 03:00:00.0000 GMT 2019-02-17 01:59:59.9999 GMT       -180        60             -120