Get Current Local Time of Any Country in PL/SQL

Usually we used to do Google to check current local time of any country. In this post I am giving an example, how you can get current local time of any country in PL/SQL. You can get the list of country names through V$TIMEZONE_NAMES view in Oracle. I am using this dictionary view to get the names of country and using TZ_OFFSET function to get the time zone value for particular country.

After getting country name and time zone value, you can use Alter Session command to set time zone of current session. After that you can get Current_Timestamp to get the current local time of that country. Below I am giving example step by step.

First query the V$TIMEZONE_NAMES view using TZ_OFFSET function to check the country names and their timezone, as shown in below example:

 SELECT DISTINCT tzname, TZ_OFFSET (tzname)
 FROM V$TIMEZONE_NAMES
ORDER BY tzname;

You will get around 577 rows something like below:

America/Mexico_City -05:00 
America/Miquelon -02:00 
America/Moncton -03:00 
America/Monterrey -05:00 
America/Montevideo -03:00 
America/Montreal -04:00 
America/Montserrat -04:00 
America/Nassau -04:00 
America/New_York -04:00

Now if you want to know that what is the current local time in New York. Then you run the following Alter Session command with Timezone value of New York, which is -04:00. As shown below:

ALTER SESSION SET time_zone = '-04:00';

Then query as below to get the current local time for New York.

SELECT TO_CHAR (CURRENT_TIMESTAMP, 'dd-mon-yyyy HH:mi:ss PM') c_time
 FROM DUAL;
C_TIME 
--------------------------------
13-jun-2017 04:26:10 AM 
1 row selected.

After that you should set the timezone to local. Run the following Alter Session command to restore the timezone:

ALTER SESSION SET time_zone = LOCAL;

Now if you will query as above you will get the current local time of your country.

I have created a stored function also in PL/SQL to get the local time of any country by passing the country name as parameter. But parameter country name should be from the list of country names in V$TIMEZONE_NAMES view.  Below is the function:

CREATE OR REPLACE FUNCTION get_current_local_time (country_name IN VARCHAR2)
 RETURN VARCHAR2
IS
 CURSOR c_tz (p_country_name IN VARCHAR2)
 IS
 SELECT DISTINCT tzname, TZ_OFFSET (tzname) z_offset 
 FROM V$TIMEZONE_NAMES
 WHERE UPPER (tzname) = UPPER (p_country_name) AND ROWNUM = 1;

v_offset VARCHAR2 (100);
 vtime VARCHAR2 (100);
BEGIN
 FOR c IN c_tz (country_name)
 LOOP
 v_offset := c.z_offset;
 END LOOP;

EXECUTE IMMEDIATE 'Alter Session Set time_zone = '
 || CHR (39)
 || v_offset
 || CHR (39);

SELECT TO_CHAR (CURRENT_TIMESTAMP, 'dd-mon-yyyy HH:mi:ss PM')
 INTO vtime
 FROM DUAL;

/* restore local time for current session*/
 EXECUTE IMMEDIATE 'Alter Session Set time_zone = local';

RETURN (vtime);
EXCEPTION
 WHEN OTHERS
 THEN
 RETURN '';
END;
/

Now use it as shown below:

SELECT get_current_local_time ('America/New_York') FROM DUAL;
C_TIME 
--------------------------------------------------------
13-jun-2017 04:33:05 AM 
1 row selected.