Managing Data in Different Timezones
- Timezone Session Parameter
TIME_ZONE may be set to
a.Absolute Offset
b.Database Timezone
c.OS Local Timezone
d.A Named Region
- Session TimeZone
— Query to return the Session Timezone (Returns the Time Zone of the Current Session) —
SELECT SESSIONTIMEZONE FROM dual
/
— Alter the Timezone Value for the current Session —
ALTER SESSION SET TIME_ZONE = ‘-05:00’
/
— Query to return the Session Timezone after altering the session —
SELECT SESSIONTIMEZONE FROM DUAL
/
Note :
We can set the default TimeZone for a session by setting the value in ORA_SDTZ environment variable.
- Database TimeZone
— Query to return the Current Value of Database Timezone —
SELECT dbtimezone FROM DUAL
/
— Alter the Timezone Value for the current Session to Database TimeZone —
ALTER SESSION SET TIME_ZONE = dbtimezone
/
— Query to return the Database Timezone after altering the session to Session Timezone —
SELECT SESSIONTIMEZONE FROM DUAL
/
Note :
Database TimeZones can be queried from V$TIMEZONE_NAMES Data Dictionary View
SELECT tzname
FROM v$timezone_names
/
— Alter the Database Timezone to a Specific Region —
ALTER DATABASE SET TIME_ZONE=’America/Menominee’
/
- Local TimeZone
— Setting Timezone to Local for the Current Session —
ALTER SESSION SET TIME_ZONE = local
/
— Query to return the Current Value of Local Timezone —
SELECT SESSIONTIMEZONE FROM DUAL
/
4.Region Specific TimeZone
— Setting Timezone to region specific —
ALTER SESSION SET TIME_ZONE = ‘America/New_York’
/
— Query to return the Current Value of Session Timezone after setting it to Region Specific Timezone —
SELECT SESSIONTIMEZONE FROM DUAL
/
- CURRENT_DATE,CURRENT_TIMESTAMP and LOCALTIMESTAMP
a.CURRENT_DATE
It returns the current date from the system and has a datatype of DATE
b.CURRENTTIMESTAMP
It returns the current time stamp from the system
It has a datatype of TIMESTAMP WITH TIME ZONE
c.LOCALTIMESTAMP
It returns the current time stamp from User Session
Has a datatype of TIMESTAMP
- CURRENT_DATE
— Display the Current Date —
SELECT current_date FROM dual
/
— Alter the Session to display the Current Date in Time and Seconds —
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24:MI:SS’
/
— Display the Current Date in the format —
SELECT current_date FROM dual
/
- CURRENT_TIMESTAMP
— Display the Current Timestamp —
SELECT current_timestamp FROM dual
/
— Alter the Session Timezone —
ALTER SESSION SET TIME_ZONE = ‘-8:0’
/
— Display the Current Timestamp after altering the session —
SELECT current_timestamp FROM dual
/
- LOCALTIMESTAMP
— Display the Local Timestamp —
SELECT localtimestamp FROM dual
/
— Alter the Session Timezone to see changes in Local Timestamp —
ALTER SESSION SET TIME_ZONE = ‘-8:0’
/
— Display the Local Timestamp and Current Timestamp after altering the session —
SELECT LOCALTIMESTAMP,CURRENT_TIMESTAMP FROM DUAL
/
Local Timestamp returns a TIMESTAMP value whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIMEZONE value
- TIMESTAMP Datatype
a.The TIMESTAMP Datatype is an extension to a DATE datatype.
b.It stores the year,month and day of the DATE datatype plus HOUR,MINUTE and SECOND value.
— Query to display the HIREDATE of all Employees with the concept of Timestamp Datatype —
SELECT empno,ename,TO_CHAR(hiredate,’DD-MON-YYYY HH24:MM:SS’) “HIREDATE” FROM emp
/
a. Create a new table having the HIREDATE Column as a TIMESTAMP Datatype.
CREATE TABLE emp_hiredate(empno number,ename varchar2(50),hiredate TIMESTAMP)
/
b. Insert records into the Table
INSERT INTO emp_hiredate VALUES(1000,’Name 2′,’01-MAY-2012′)
/
c. Query the newly created records to understand the difference between With TIMESTAMP and without TIMESTAMP
SELECT * FROM emp_hiredate
/
DROP TABLE emp_hiredate
/
We can convert from DATE to TIMESTAMP when the column has data
CREATE TABLE emp_hiredate_new(empno NUMBER,ename VARCHAR2(50),hiredate DATE)
/
INSERT INTO emp_hiredate_new VALUES(1000,’Name 2′,’01-MAY-2012′)
/
SELECT * FROM emp_hiredate_new
/
ALTER TABLE emp_hiredate_new MODIFY(hiredate TIMESTAMP)
/
We cannot convert DATE or TIMESTAMP to TIMESTAMP WITH TIMEZONE with the column having existing data.
ALTER TABLE emp_hiredate_new MODIFY(hiredate TIMESTAMP WITH TIME ZONE)
/
We can specify the fractional seconds precision for time stamp.
If we do not specify any value for the TIMESTAMP it defaults to 6.
ALTER TABLE EMP_HIREDATE_NEW MODIFY HIREDATE TIMESTAMP(7)
/
d.Drop the Table
DROP TABLE emp_hiredate_new PURGE
/
- Timestamp with TIMEZONE Datatype
TIMESTAMP WITH TIMEZONE is a added feature to the TIMESTAMP which includes
timezone displacement in its value. This displacement is the difference in hours and minutes
between local time and UTC.
TIMESTAMP (fractional precision) WITH TIME ZONE
CREATE TABLE WEB_ORDERS
(ord_id NUMBER PRIMARY KEY,ord_date TIMESTAMP WITH TIME ZONE)
/
INSERT INTO WEB_ORDERS VALUES(100,CURRENT_DATE)
/
SELECT * FROM WEB_ORDERS
/
DROP TABLE WEB_ORDERS
/
— TIMESTAMP with local TIMEZONE —
TIMESTAMP WITH TIMEZONE is a added feature to the TIMESTAMP which includes
timezone displacement in its value. This displacement is the difference in hours and minutes
between local time and UTC.
TIMESTAMP (fractional precision) WITH LOCAL TIME ZONE
CREATE TABLE shipping(delivery_time TIMESTAMP WITH LOCAL TIME ZONE)
/
INSERT INTO shipping VALUES(CURRENT_TIMESTAMP + 3)
/
SELECT * FROM SHIPPING
/
This example is more useful when a web based company gurantees shipping they can estimate their delivery time based on the time zone of the customer placing the order.
— Accordingly the TIMEZONE can be altered to a specific Location —
ALTER SESSION SET TIME_ZONE = ‘EUROPE/LONDON’
/
SELECT * FROM SHIPPING
/
DROP TABLE shipping PURGE
/
- Interval Datatypes
INTERVAL Datatypes are used to store difference between two datetime values.
Two classes of Intervals :
a. Year-Month
b. Day-Second
INTERVAL YEAR (year_precision) TO MONTH
Accepted value within the range are 0 to 9. Default is 6.
INTERVAL DAY (day_precision) TO SECOND
(fractional_seconds_precision)
INTERVAL DAY Accepted value within the range are 0 to 9.Default is 2.
INTERVAL SECOND Accepted value within the range are 0 to 9.Default is 6.
INTERVAL YEAR TO MONTH can have fields of YEAR and MONTH.
INTERVAL DAY TO SECOND can have fields of DAY,HOUR,MINUTE and SECOND.
— Example for INTERVAL YEAR TO MONTH —
CREATE TABLE warranty
(prod_id NUMBER,warranty_time INTERVAL YEAR(3) TO MONTH)
/
INSERT INTO warranty VALUES (123,INTERVAL ‘8’ MONTH)
/
INSERT INTO warranty VALUES (155,INTERVAL ‘200’ YEAR(3))
/
INSERT INTO warranty VALUES (678,’200-11′)
/
INSERT INTO warranty VALUES (678,’2000-10′)
/
SELECT * FROM warranty
/
DROP TABLE warranty PURGE
/
— Example for INTERVAL DAY TO SECOND (Stores a period of time in days,hours,minutes and seconds —
INTERVAL ‘6 03:30:16’ DAY TO SECOND
Indicates an interval of 6 days 3 hours 30 minutes and 16 seconds
CREATE TABLE LAB
(EXP_ID NUMBER,TEST_TIME INTERVAL DAY(2) TO SECOND)
/
INSERT INTO lab VALUES(100012,’90 00:00:00′)
/
INSERT INTO lab VALUES(56098,INTERVAL ‘6 03:30:16’ DAY TO SECOND)
/
SELECT * FROM lab
/
DROP TABLE lab
/
- Extract Function
— Query to Extract the YEAR from a Date Datatype —
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL
/
— Query to Extract the Month from a Date Datatype (Returns the month in numerical) —
SELECT EMPNO,ENAME,HIREDATE,EXTRACT(MONTH FROM HIREDATE) FROM EMP
WHERE DEPTNO = 10
/
— Query to Extract the Day from a Date Datatype (Returns the day in numerical) —
SELECT EMPNO,ENAME,HIREDATE,EXTRACT(DAY FROM HIREDATE) FROM EMP
WHERE DEPTNO = 10
/
- TZ_OFFSET
— Query to display the Timezone Offset for a region —
SELECT TZ_OFFSET(‘US/Eastern’) FROM DUAL
/
— Query to list a valid time zone names —
SELECT distinct tzname FROM V$TIMEZONE_NAMES
/
FROM_TZ converts a Timestamp value and a timezone to a TIMESTAMP WITH TIMEZONE value.
SELECT FROM_TZ(TIMESTAMP ‘2000-03-28 08:00:00′,’3:00’) FROM DUAL
/
SELECT FROM_TZ(TIMESTAMP ‘2000-03-28 08:00:00′,’Australia/North’) FROM DUAL
/
— Converting CHAR,VARCHAR2 to a TIMESTAMP —
SELECT TO_TIMESTAMP
(‘2000-12-01 11:00:00’,
‘YYYY-MM-DD HH:MI:SS’)
FROM DUAL
/
— Converting CHAR,VARCHAR2 to a TIMESTAMP WITH TIMEZONE —
SELECT
TO_TIMESTAMP_TZ(‘1999-12-01 11:00:00 -8:00’,
‘YYYY-MM-DD HH:MI:SS TZH:TZM’)
FROM DUAL
/