Language setting for ‘TZD’ format in TO_CHAR (timestamp with time zone)

oracleselect

Which setting controls the language of 'TZD' or 'TZR' in the TO_CHAR(timestamp with time zone) function?

e.g.
select to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD') from dual;

returns the English value for TZD "MDT" (Mountain Daylight Time), which is expected as the default value.

However, I can't find which parameter or setting to use for it to return the French value of TZD, i.e. "HNR"

  • I've tried adding ", 'nls_date_language=FRENCH'" to the TO_CHAR example above, as well as altering the session parameters NLS_LANG and NLS_DATE_LANGUAGE, but to no avail.

Best Answer

You can get the TZD abbreviations with this query:

SELECT tzabbrev, tzname, TZ_OFFSET(tzname) 
FROM V$TIMEZONE_NAMES
WHERE tzname = 'America/Edmonton'

TZABBREV    TZNAME              TZ_OFFSET(TZNAME)
----------  ------------------  ------------------
LMT         America/Edmonton    -06:00
MST         America/Edmonton    -06:00
MDT         America/Edmonton    -06:00
MWT         America/Edmonton    -06:00
MPT         America/Edmonton    -06:00

I think they are fix and you cannot change it. Note, for time zone abbreviations there is no common or official standard, for every time zone you will find dozens of abbreviations, see https://www.timeanddate.com/time/zones/na.

If you need a language specific output you could use CASE or DECODE

DECODE(to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD'), 'MDT','HNR' ,'HAR')
Related Question