Get Current Local Time of Any Country in PL/SQL

When we want to know the current local time in any country, we often use Google to search for this information. However, in Oracle PL/SQL, you can retrieve the current local time for any country directly using built-in features. In this article, I will show you an example of how to obtain the current local time for any country using PL/SQL.

Oracle provides a data dictionary view called V$TIMEZONE_NAMES, which contains a list of all available time zone names. You can use this view to get the names of different countries or regions. Additionally, the TZ_OFFSET function helps you determine the time zone offset for a particular country or region.

Once you have the country name and its corresponding time zone offset, you can use the ALTER SESSION command to set the time zone for your current session. After setting the appropriate time zone, you can use the CURRENT_TIMESTAMP function to retrieve the current local time for that country or region. Below, I’ll guide you through these steps with practical examples.

Step 1: Query the List of Time Zones and Their Offsets

First, you can retrieve the list of time zone names and their corresponding offsets using the following query:

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

This query will return a list of about 577 rows, displaying each time zone and its offset. Here are a few sample results:

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

Step 2: Set the Session Time Zone

Suppose you want to find the current local time in New York. From the previous query, you can see that the time zone offset for New York is -04:00. You can set your session’s time zone as follows:

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

Step 3: Retrieve the Current Local Time

After setting the session time zone, you can use the following query to get the current local time in 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.

This output shows the current date and time in New York at the moment the query was executed.

You can also use an online tool to check the date and time in any country.

Step 4: Restore the Original Time Zone

Once you have retrieved the required information, it is good practice to restore your session’s time zone to its original (local) value. You can do this by running:

ALTER SESSION SET time_zone = LOCAL;

After resetting the time zone, if you run the previous query again, it will display the current local time according to your original session time zone.

Automating with a Stored Function

To make this process more convenient, you can create a PL/SQL function that returns the current local time for any country or region by passing the time zone name from the V$TIMEZONE_NAMES view as a parameter. Below is the function definition:

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;
/

Usage Example

You can call this function from SQL 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.

Additional Notes

• Time Zone Names:
  The function expects the time zone name to match exactly one from the V$TIMEZONE_NAMES view. Make sure to use the correct name and case, or use the UPPER() function as shown in the code.
• Restoring the Session:
  The function restores the session’s time zone to LOCAL after execution to avoid affecting subsequent operations.
• Other Tools:
  While PL/SQL provides this capability, you can also use online tools or websites to quickly check the local time in any country.

By using this approach, you can dynamically retrieve the current local time for any country or region directly from your Oracle database, without needing to leave your PL/SQL environment.