How to get different timezone date in oracle?

1
votes

I want to know what is current date and time in South Africa.

How i can check what is the current time and date across different timezone in oracle?

I will add timezone values in data table and from that can i get current date and time?

I have one Country table in that i will save timezone as per the country. Now in procedure i have to check one date from other table against all countries is the current date or not. So query: SELECT SYSTIMESTAMP AT TIME ZONE 'Africa/Johannesburg' FROM DUAL; will not solve my issue.

2
sqloracleoracle11g
Typically timestamps are stored in SQL databases using a single timezone, usually UTC. You should worry about your particular timezone after you have queried out your data in the presentation layer. – Tim Biegeleisen
Can you please suggest me suppose if i add all timezone in one column of the table and from that i have to get time zone? select SYSTIMESTAMP at time zone timezone_column_name from dual; I have to check for multiple countries in procedure depending on one variable. so i can not hard code timezone value in query. – Bala

2 Answers

4
votes

Use SYSTIMESTAMP to get the current system timestamp and AT TIME ZONE '<time zone>' to change to a different time zone:

SELECT SYSTIMESTAMP AT TIME ZONE 'Africa/Johannesburg'
FROM   DUAL;

A list of time zone names can be found with this query:

SELECT *
FROM gv$timezone_names;

You could even pass the time zone name as a variable rather than using a hard-coded text literal:

SELECT TZNAME,
       SYSTIMESTAMP AT TIME ZONE TZNAME AS current_time
FROM   gv$timezone_names;
2
votes
select 
       SYSTIMESTAMP at time zone 'Africa/Johannesburg' south_africa_time
from dual;

You could refer this link for all time zone values

TIME Zones