Datatypes that support dates and times come in two flavours:
First, create a test table where the different types of DATE datatypes are bing used:
CREATE TABLE DATETYPETEST (TESTDATE1 DATE, TESTDATE2 TIMESTAMP, TESTDATE3 TIMESTAMP WITH TIME ZONE, TESTDATE4 TIMESTAMP WITH LOCAL TIME ZONE, TESTDATE5 INTERVAL YEAR TO MONTH, TESTDATE6 INTERVAL YEAR(4) TO MONTH, TESTDATE7 INTERVAL DAY TO SECOND, TESTDATE8 INTERVAL DAY TO SECOND(2) );
First I will test the Datetime data types:
INSERT INTO DATETYPETEST(TESTDATE1) VALUES(SYSDATE);
INSERT INTO DATETYPETEST(TESTDATE1) VALUES(TRUNC(SYSDATE));
TRUNC(SYSDATE) |
---|
17.01.2014 10:15:45 |
17.01.2014 |
INSERT INTO DATETYPETEST (TESTDATE2) VALUES (SYSTIMESTAMP);
INSERT INTO DATETYPETEST (TESTDATE2) VALUES (TRUNC(SYSTIMESTAMP));
TRUNC(SYSTIMESTAMP) |
---|
17.01.2014 10:15:45.978004 |
17.01.2014 00:00:00.000000 |
Interestingly enough, TRUNC on datatype TIMESTAMP does not strip of the time precision per se, Oracle simply sets it to zero altogether.
INSERT INTO DATETYPETEST (TESTDATE3) VALUES (SYSDATE);
TIMESTAMP WITH TIMEZONE |
---|
17.01.2014 10:15:46.000000 +01:00 |
INSERT INTO DATETYPETEST (TESTDATE4) VALUES (SYSDATE);
TIMESTAMP WITH LOCAL TIMEZONE |
---|
17.01.2014 10:15:46.000000 |
Remember that TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone offset with the column value; however, when the column value is retrieved in a query, the value is sent in the user's local session time zone.
Let's move on to the interval data types.
First, I will insert the interval as a literal:
INSERT INTO DATETYPETEST (TESTDATE5) VALUES (INTERVAL '2-11' YEAR TO MONTH);
INTERVAL YEAR TO MONTH |
---|
+2-11 |
Below, I am using the TO_YMINTERVAL function to convert a string representing a number of years and a number of months into a INTERVAL YEAR TO MONTH datatype:
INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('14-01'));
INTERVAL YEAR TO MONTH |
---|
+14-01 |
The statement below, however, fails with ORA-01873: the leading precision of the interval is too small because used the default precision for year(2):
SQL> INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'));
INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'))
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
So instead, let's insert the values into a column which allows for larger precision for the INTERVAL YEAR(n) TO MONTH datatype:
SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('2014-01'));
1 row created.
Of course, even values of less precision would be accepted here:
SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('14-01'));
1 row created.
INTERVAL YEAR(4) TO MONTH |
---|
+2014-01 |
+14-01 |
Again, I will first insert the interval as a literal:
INSERT INTO DATETYPETEST (TESTDATE7) VALUES(INTERVAL '2 6:30:01.222' DAY TO SECOND(3) ));
INTERVAL DAY TO SECOND |
---|
+02 6:30:01.222 |
Use the TO_DSINTERVAL function to convert the string representing a number of days, hours, minutes and seconds into an INTERVAL DAY TO SECOND datatype:
INSERT INTO DATETYPETEST (TESTDATE7) VALUES(TO_DSINTERVAL('7 12:30:000001'));
INTERVAL DAY TO SECOND |
---|
+07 12:30:01.000000 |
In the next example, I am limiting the number of fractional seconds according to the specifications of the column INTERVAL DAY TO SECOND(2):
INSERT INTO DATETYPETEST (TESTDATE8) VALUES(TO_DSINTERVAL('21 12:30:000001'));
INTERVAL DAY TO SECOND(2) |
---|
+21 12:30:01.00 |
The TO_DSINTERVAL is very flexible. Here is another post on the same topic.
An overview of conversion functions used with the INTERVAL data types can be found here
Sources: Oracle Documentation on intervals
No comments:
Post a Comment