CONVERT_TZ

Purpose

This function converts a timestamp from one time zone into another one.

The timestamps do not contain any timezone information. This function therefore just adds the time shift between two specified timezones.

Usage Notes

• The timezones that are supported in Exasol are located in the system table EXA_TIME_ZONES. The following statement will return a list of all supported timezones:

  select * from EXA_TIME_ZONES;

• If the input value has type TIMESTAMP WITH LOCAL TIME ZONE, then this function is only allowed if the session time zone ( SESSIONTIMEZONE) is identical to the parameter from_tz. However, the result type is still the TIMESTAMP data type.

• The optional fourth parameter (string) specifies options how problematic input data due to time shifts should be handled. The following alternatives exist:

  'INVALID SHIFT AMBIGUOUS ST'
  

  'INVALID SHIFT AMBIGUOUS DST'
  

  'INVALID SHIFT AMBIGUOUS NULLIFY'
  

  'INVALID SHIFT AMBIGUOUS REJECT'
  

  'INVALID ADJUST AMBIGUOUS ST'

  'INVALID ADJUST AMBIGUOUS DST'
  

  'INVALID ADJUST AMBIGUOUS NULLIFY'
  

  'INVALID ADJUST AMBIGUOUS REJECT'
  

  'INVALID NULLIFY AMBIGUOUS ST'
  

  'INVALID NULLIFY AMBIGUOUS DST'
  

  'INVALID NULLIFY AMBIGUOUS NULLIFY'
  

  'INVALID NULLIFY AMBIGUOUS REJECT'
  

  'INVALID REJECT AMBIGUOUS ST'
  

  'INVALID REJECT AMBIGUOUS DST'
  

  'INVALID REJECT AMBIGUOUS NULLIFY'
  

  'INVALID REJECT AMBIGUOUS REJECT'
  

  'ENSURE REVERSIBILITY' 

• For more information about the options, refer to the Date and time data types data types in the Data Types section.
• ENSURE REVERSIBILITY is a special option to ensure the reversibility of the conversion. An exception is thrown if the input data is invalid or ambiguous and the resulting timestamp is ambiguous, since this means that the timestamp cannot be converted back without loss of information.
• When omitting the fourth parameter, the default behavior is defined by the session value TIME_ZONE_BEHAVIOR (additionally, see ALTER SESSION).

Examples

SELECT CONVERT_TZ(TIMESTAMP '2012-05-10 12:00:00',
    'UTC',
    'Europe/Berlin') CONVERT_TZ;

SELECT CONVERT_TZ(TIMESTAMP '2012-03-25 02:30:00',
    'Europe/Berlin',
    'UTC',
    'INVALID REJECT AMBIGUOUS REJECT') CONVERT_TZ;