Oracle9i introduced new date and time related data types.
1. TIMESTAMP
2. TIMESTAMP WITH TIME ZONE
3. TIMESTAMP WITH LOCAL TIME ZONE
4. INTERVAL DAY TO SECOND
5. INTERVAL YEAR TO MONTH
We can also change the database timezone as below. We can use v$timezone_names dictionary to view all the time zone information
ALTER SESSION SET TIME_ZONE = 'PST'
TIMESTAMP datatype is very much like the DATE datatype. it can store both the date and time values. It is not like DATE datatype, you can also specify the precision of the factional seconds value in the TIMESTAMP datatype. The default is 6 digit, but we can specify a value within the range from 0 through 9.
SQL> create table calltrack(
2 callnum integer,
3 calltime TIMESTAMP(4));
Table created.
We can change the NLS_TIMESTAMP_FORMAT as we required...
SQL> alter session set nls_timestamp_format = 'MMDDYYHH24MISSFF';
Session altered.
Now insert one record and select the timestamp field.
SQL> insert into calltrack values(1,sysdate);
1 row created.
SQL> select calltime from calltrack;
CALLTIME
---------------------------------------------------------------------------
1024080953590000
SQL>
TIMESTAMP WITH TIME ZONE data type stores time zone displacement which needs additional bytes of storage. We can also change the NLS_TIMESTAMP_TZ_FORMAT at session level.
SQL> create table calltime(
2 callnum integer,
3 calltime timestamp(4) with time zone);
Table created.
SQL> insert into calltime values(1,sysdate);
1 row created.
SQL> select calltime from calltime;
CALLTIME
-------------------------------------------------------
04-APR-09 11.10.40.0000 PM -04:00
SQL> select vsize(calltime) from calltime;
VSIZE(CALLTIME)
---------------
13
TIMESTAMP WITH LOCAL TIME ZONE includes time zone information, but unlike the TIMESTAMP WITH TIME ZONE datatype. It does not store the time zone displacement in additional bytes of storage. Instead, it stores the time values normalized in terms of the database time zone. When user attempts to retrieve this information, the database dispalys the information in terms of local time zone of the user's session.
Let us create a table with this data type.
SQL> create table calltrack(
2 callnum integer,
3 calltime timestamp(4) with local time zone);
Table created.
SQL> insert into calltrack values(1,sysdate);
1 row created.
SQL> select calltime from calltrack;
CALLTIME
---------------------------------------------------------------------------
24-OCT-08 10.58.34.0000 AM
SQL>
Now the date is displaying with Eastern US time. Now let us change the database time zone to Pacific time on session level.
SQL> ALTER SESSION SET TIME_ZONE = 'PST';
Session altered.
SQL> select calltime from calltrack;
CALLTIME
---------------------------------------------------------------------------
24-OCT-08 07.58.34.0000 AM
SQL>
INTERVAL DAY TO SECOND can store a time interval value in terms of days, hours, minutes and seconds. We can specify the precision for the number of days in a range from 0 to 9, the default being 2. We can also specify the precision for the fractional seconds in a range from 0 to 9, the default being 6.
SQL CREATE TABLE promotions (
2 promotion_id INTEGER,
3 name VARCHAR2(30),
4 duration INTERVAL DAY(3) TO SECOND (4))
5 /
Table created.
SQL
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (1, '10% off Z Files', INTERVAL '3' DAY);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (2, '20% off Pop 3', INTERVAL '2' HOUR);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (3, '30% off Modern Science', INTERVAL '25' MINUTE);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (4, '20% off Tank War', INTERVAL '45' SECOND);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (5, '10% off Chemistry', INTERVAL '3 2:25' DAY TO MINUTE);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (6, '20% off Creative Yell',
3 INTERVAL '3 2:25:45' DAY TO SECOND);
1 row created.
SQL
SQL INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '123 2:25:45.12' DAY(3) TO SECOND(2));
1 row created.
scott@orcl> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '1253 2:25:45.12' DAY(3) TO SECOND(2));
INTERVAL '1253 2:25:45.12' DAY(3) TO SECOND(2))
*
ERROR at line 3:
ORA-01873: the leading precision of the interval is too small
scott@orcl> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, '15% off My Front Line',
3 INTERVAL '153 2:25:415.12' DAY(3) TO SECOND(2))
4 select duration from promotions;
INTERVAL '153 2:25:415.12' DAY(3) TO SECOND(2))
*
ERROR at line 3:
ORA-01852: seconds must be between 0 and 59
scott@orcl>
SQL select duration from promotions;
DURATION
-------------------------------------------------------------------------
+003 00:00:00.0000
+000 02:00:00.0000
+000 00:25:00.0000
+000 00:00:45.0000
+003 02:25:00.0000
+003 02:25:45.0000
+123 02:25:45.1200
7 rows selected.
SQL
INTERVAL YEAR TO MONTH can store time interval value in terms of years and months. We can specify the precision for the number of years in a range from 0 to , default being 2.
SQL> CREATE TABLE coupons (
2 coupon_id INTEGER,
3 name VARCHAR2(30),
4 duration INTERVAL YEAR(3) TO MONTH);
Table created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (1, '$1 off Z Files', INTERVAL '1' YEAR);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (2, '$2 off Pop 3', INTERVAL '11' MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (3, '$3 off Modern Science', INTERVAL '14' MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (4, '$2 off Tank War', INTERVAL '1-3' YEAR TO MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (5, '$1 off Chemistry', INTERVAL '0-5' YEAR TO MONTH);
1 row created.
SQL>
SQL> INSERT INTO coupons (coupon_id, name, duration)
2 VALUES (6, '$2 off Creative Yell', INTERVAL '123' YEAR(3));
1 row created.
SQL>
SQL> SELECT duration FROM coupons;
DURATION
---------------------------------------------------------------------------
+001-00
+000-11
+001-02
+001-03
+000-05
+123-00
6 rows selected.
SQL>
No comments:
Post a Comment