Forum Stats

  • 3,874,081 Users
  • 2,266,674 Discussions


Getting two random timezones and calculating timestamp between them

foxhound Member Posts: 26 Red Ribbon

The problem here is to get two randomly chosen time zones from first distinct five, where unique key is pair time zone name and its offset. I have developed following solution. In my opinion and after some testing looks like working one. But the query itself looks little overcomplicated. Could you take a look and provide feedback if there is anything that could be improved?


   a as current_tz,

   b as new_tz,


    FROM_TZ(LOCALTIMESTAMP,a) AS withtimezone,






   FROM (


           ROWNUM AS rown,


       FROM (



           FROM (




                       TZ_OFFSET(tzname) AS tzoffset



               ORDER BY



           WHERE ROWNUM < 6


           ) t

       WHERE ROWNUM < 3


   PIVOT (

       MAX(tzoffset) FOR rown IN (1 AS a, 2 AS b)





  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,745 Red Diamond

    Hi, @foxhound

    the query itself looks little overcomplicated.

    Here's one way to do the same thing using two sub-queries instead of four:

    WITH  finalists  AS
    	,        TZ_OFFSET (tzname)	  AS tzoffset
    	FROM	 v$timezone_names
    	ORDER BY tzname
    ,	got_rown  AS
    	SELECT tzoffset
    	,	ROW_NUMBER () OVER (ORDER BY (dbms_random.value)) AS rown
    	FROM	finalists
    SELECT   current_tz
    ,	 new_tz
    ,  	 FROM_TZ( LOCALTIMESTAMP, current_tz)			 AS withtimezone
    ,  	 SYS_EXTRACT_UTC (FROM_TZ (LOCALTIMESTAMP, current_tz)) AS extracted_utc
    ,  	 FROM_TZ (LOCALTIMESTAMP, current_tz)
    	   AT TIME ZONE new_tz				 AS converted
    FROM	 got_rown
    PIVOT	 (    MAX (tzoffset)
    	 FOR  rown  IN   ( 1 AS current_tz
    	    	   	 , 2 AS new_tz

    This illustrates two alternatives to using ROWNUM to get the Top-N rows:

    1. FETCH
    2. Analytic ROW_NUMBER

    I could have used ROW_NUMBER for both filters, but I wanted to show how to use FETCH.

  • mathguy
    mathguy Member Posts: 10,893 Black Diamond

    where unique key is pair time zone name and its offset.

    What does that mean? That is: the offset is completely determined by the time zone name; so why do you need to talk about "pair time zone name and its offset", when that's logically the same as "unique key is time zone name"?

    Other than that - I would use match_recognize for the initial part (selecting just the first five distinct time zone names), I would use the first/last aggregate function to select two distinct time zones at random (note that I select the first and the last out of the five, after ordering them randomly; this still means "two random names out of the first distinct five"). And then, I don't quite understand why you need to use the offsets in some of the computations in the final (outer) query; using the names should work just as well. If you want to show the time zone components in TZH:TZM format, you can control that with NLS settings; and if you want to show the offsets explicitly, you can do so by calling tz_offset just in the outer query; but other than that, there's no reason to use the offsets in your calculations.

    For what it's worth, the first five distinct time zone names don't all have distinct offsets; since in your output only the offsets matter, why do you need five distinct time zone names, when their offsets aren't distinct anyway? Just asking...

    I came up with this query:

      prep1 (tzname) as (
        select tzname
        from   v$timezone_names
          order    by tzname
          measures a.tzname as tzname
          pattern  (a b*)
          define   a as match_number() <= 5, b as tzname = a.tzname
        order by dbms_random.value()
    , prep2 (current_tz_name, new_tz_name) as (
        select min(tzname) keep (dense_rank first order by rownum),
               min(tzname) keep (dense_rank last  order by rownum)
        from   prep1
    select tz_offset(current_tz_name) as current_tz_offset,
           tz_offset(new_tz_name)     as new_tz_offset,
           localtimestamp at time zone current_tz_name                            as withtimezone,
           sys_extract_utc(localtimestamp at time zone current_tz_name)           as extracted_utc,
           (localtimestamp at time zone current_tz_name) at time zone new_tz_name as converted
    from   prep2

    Notice the (intentionally) dumb-looking definition of the last column, converted, at the end of the code (in the main query). Regardless of which functions and which options you use: what is the point of converting a timestamp (like `localtimestamp`) to a specific time zone, and then further convert it to another time zone? The result is the same as converting the timestamp (without time zone) directly to the final time zone - or are you testing to see if Oracle perhaps got that wrong? If that's not what you are concerned about, then simplify converted - it should look the same as withtimezone, just use the "new" time zone instead of the "current" one.