34 Using Data Types

• ·         Allows time to be stored as an interval of years and months- INTERVAL YEAR[(x)] TO MONTH (x is optional  default is 2)
• ·         When a column is selected in a SQL statement the time is automatically converted to the user’s timezone- TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE (data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone)
• ·         Binary large object data up to 4 gigabytes-BLOB
• ·         Stores a time zone value as a displacement from Universal Coordinated Time or UCT- TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE (includes a time zone offset or time zone region name in its value)
• ·         Allows time to be stored as an interval of days to hours, minutes, and seconds- INTERVAL DAY[(x)] TO SECOND[(y)] default for x is 2 and for y is 6
• ·         Character data up to 4 gigabytes-CLOB
• ·         Allows the time to be stored as a date with fractional seconds- TIMESTAMP [(fractional_seconds_precision)] (It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype.)

1.  Create each of the three time-zone tables.

a.  TIMESTAMP WITH LOCAL TIME ZONE

SELECT DBTIMEZONE FROM DUAL;

SELECT SESSIONTIMEZONE FROM DUAL;

Now let’s create a table and insert a row:

CREATE TABLE time_ex3 (first_column TIMESTAMP WITH TIME ZONE,

second_column TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO time_ex3

(first_column,

 second_column)

VALUES

(TO_TIMESTAMP_TZ('2016-11-0116:02:08.236+05:30','YYYY-MM-DDHH24:MI:SS.FFTZH:TZM'),

TO_TIMESTAMP_TZ('2016-11-0116:02:08.236+05:30','YYYY-MM-DDHH24:MI:SS.FFTZH:TZM'));

While storing first_column is stored as is, but second_column is normalized to server time.

b.  INTERVAL YEAR TO MONTH

CREATE TABLE time_ex4

(loan_duration1 INTERVAL YEAR(3) TO MONTH,

loan_duration2  INTERVAL YEAR(2) TO MONTH);

INSERT INTO time_ex4 (loan_duration1, loan_duration2 )

VALUES( INTERVAL '120' MONTH(3), INTERVAL '3-6' YEAR TO MONTH);

c.  INTERVAL DAY TO SECOND

CREATE TABLE time_ex5

(day_duration1 INTERVAL DAY(3) TO SECOND,

day_duration2  INTERVAL DAY(3) TO SECOND);

INSERT INTO time_ex5 (day_duration1, day_duration2 )

VALUES( INTERVAL '25' DAY(2), INTERVAL '4 10:30:10' DAY TO SECOND);

2.   Execute a SELECT * from each table to verify your input.

a.  TIMESTAMP WITH LOCAL TIME ZONE

Since for apex me the user’s timezone in PST ( -07.00):

SELECT * FROM time_ex3;

Now let’s execute below mentioned two statements together as a script:

ALTER SESSION SET TIME_ZONE = '-05:00';

SELECT * FROM time_ex3;

It proves that for second_column: When user retrieves the data, Oracle returns it in the users' local session time zone

b.  INTERVAL YEAR TO MONTH

SELECT TO_CHAR( SYSDATE + loan_duration1 , 'DD-Month-YYYY') AS "120 months from Now", TO_CHAR( SYSDATE + loan_duration2 , 'DD-Month-YYYY') AS "3 years 6 months from Now"

FROM time_ex4;

c.  INTERVAL DAY TO SECOND

SELECT

TO_CHAR( SYSDATE , 'DD-Month-YYYY hh:mi:ss') AS now,

 TO_CHAR( SYSDATE + day_duration1 , 'DD-Month-YYYY hh:mi:ss') AS "25 days from Now",

 TO_CHAR( SYSDATE + day_duration2 , 'DD-Month-YYYY hh:mi:ss') AS "4day10hr30min10sec from now "

FROM time_ex5;

3.  Give 3 examples of organizations and personal situations where it is important to know to which time zone a date-time value refers.

·         My team fills timesheet every Friday end of the day.  For team members in U.S. end of day time in UTC is different than team in India.

·          When client says lets schedule weekly meeting for Wednesday 8 AM, it means he is saying every Wednesday 08:00 AM in his time zone. This time is different for offshore team. Outlook must take care of this thing. And it does.

·         When there is a cricket match in India, news channel display timings in IST. To watch the match live I have to convert it to my local time zone and switch on my TV at that time.