I know that this query return all the possible timezones in Oracle

select distinct tzname d, tzname r
from V$TIMEZONE_NAMES
order by 1

but that gives 597 rows which is difficult for the user to choose the favorite one.

Is there a way to select a shortlisted list like the one here

I'm using oracle 21c with Oracle APEX.

CodePudding user response：

You can find a distinct time zone for each location and then find a single time zone name for each time zone in each region:

SELECT region,
       tzname,
       tzabbrev
FROM   (
  SELECT region,
         tzname,
         tzabbrev,
         ROW_NUMBER() OVER (
           PARTITION BY region, tzabbrev
           ORDER BY tzname
         ) AS region_tz_rn
  FROM   (
    SELECT SUBSTR(TZNAME, 1, INSTR(TZNAME, '/') - 1) AS region,
           TZNAME,
           TZABBREV,
           ROW_NUMBER() OVER (
             PARTITION BY tzname
             ORDER BY
               -- Prefer named time zone over a UTC offset and, if possible, ignore LMT.
               CASE 
               WHEN TZABBREV = 'LMT' -- Local Mean Time
               THEN 2
               WHEN TZABBREV LIKE '-%' OR TZABBREV LIKE ' %'
               THEN 1
               ELSE 0
               END,
               TZABBREV
           ) AS tz_rn
    FROM   V$TIMEZONE_NAMES
  )
  WHERE  tz_rn = 1
)
WHERE  region_tz_rn = 1;

Which filters from over 2000 original entries down to about 200 rows:

REGION	TZNAME	TZABBREV
Africa	Africa/Casablanca	00
Africa	Africa/Juba	CAST
Africa	Africa/Blantyre	CAT
Africa	Africa/Algiers	CEST
Africa	Africa/Addis_Ababa	EAT
Africa	Africa/Cairo	EEST
Africa	Africa/Abidjan	GMT
Africa	Africa/Johannesburg	SAST
Africa	Africa/Ndjamena	WAST
Africa	Africa/Bangui	WAT
America	America/Scoresbysund	00
America	America/Noronha	-01
America	America/Araguaina	-02
...	...	...
US	US/Alaska	AHDT
US	US/Aleutian	AHST
US	US/Central	CDT
US	US/Michigan	CST
US	US/Eastern	EDT
US	US/Hawaii	HDT
US	US/Arizona	MDT
US	US/Pacific	PDT
US	US/Samoa	SST
null	Kwajalein	09
null	NZ-CHAT	1215
null	GB	BDST
null	Eire	BST
null	CST	CDT
null	CET	CEST
null	EST5EDT	EDT
null	EET	EEST
null	EST	EST
null	GMT	GMT
null	Hongkong	HKST
null	HST	HST
null	Israel	IDDT
null	Japan	JDT
null	ROK	JST
null	MST7MDT	MDT
null	MET	MEST
null	MST	MST
null	NZ	NZDT
null	PST	PDT
null	Singapore	SMT
null	Iran	TMT
null	UCT	UTC
null	WET	WEST

If you want to filter down even more then you can find equivalent time zones and aggregate them (i.e. 00, UTC, GMT are equivalent and could be combined).

db<>fiddle here