Overview
If you store date and time information in Oracle, you have two different options for the column's datatype - DATE and TIMESTAMP.
DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype.
In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date:
1
2
|
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
FROM emp;
|
hiredate
----------------------17.12.1980:00:00:00
You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example:
1
2
3
|
SYSDATE + 1 is tomorrow
SYSDATE - 7 is one week ago
SYSDATE + (10/1440) is ten minutes from now;
|
Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired.
1
2
3
4
5
|
SELECT '03.12.2004:10:34:24' "Now",
TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hire date",
TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
-- hiredate "Hired since [Days]"
FROM emp;
|
Now Hire date Hired since [Days]
------------------------------------------------------------------------------------------03.12.2004:10:34:24 17.12.1980:00:00:00 8752.44056
You cannot multiply or divide DATE values. Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates.
Subtraction between Dates
The trouble people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned.
1
2
3
4
5
6
7
8
9
|
DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS');
SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
TRUNC(86400*(Today-hiredate))-60*(trunc((86400*(Today-hiredate))/60)) "Sec",
TRUNC((86400*(Today-hiredate))/60)-60*(trunc(((86400*(Today-hiredate))/60)/60)) "Min",
TRUNC(((86400*(Today-hiredate))/60)/60)-24*(trunc((((86400*(Today-hiredate))/60)/60)/24))"Hrs",
TRUNC((((86400*(Today-hiredate))/60)/60)/24) "Days"
FROM emp;
|
Hiredate Today Sec Min Hrs Days
----------------------------------------------------------------------------------------17.12.1980:00:00:00 03.12.2004:10:34:24 24 34 10 8752
Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation.
If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.
1
2
3
4
5
6
7
8
9
|
DEFINE DateDay = 8752.44056
SELECT TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD') + DateDay,'YYYY MM DD HH24:MI:SS') A FROM DUAL);
|
YEARS MONTHS DAYS HO MI SE
---------------------------------------------------------23 11 17 10 34 24
The new TIMESTAMP datatype
One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.
Convert DATE datatype to TIMESTAMP datatype
If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a INSERT SELECT FROM and Oracle will do the conversion for you.
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TABLE date_table
( date1 DATE,
time1 TIMESTAMP,
time2 TIMESTAMP);
INSERT INTO date_table (date1, time1, time2)
VALUES (SYSDATE, TO_TIMESTAMP('17.12.1980:00:00:00*'*,'DD.MM.YYYY:HH24:MI:SS'),
TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS') );
COMMIT;
SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;
|
Date
-------------------------------------------03-DEC-04 11.36.45.000000 AM
The TO_TIMESTAMP function converts a string to a timestamp. The syntax for the to_timestamp function is:
|
1
|
TO_TIMESTAMP ( string , [ format_mask ] [ 'nlsparam' ] )
|
String is the string that will be converted to a timestamp. format_mask is optional. This is the format that will be used to convert string to a timestamp.
The following is a list of options for the format_mask parameter These parameters can be used in many combinations.
Parameter
|
Explanation
|
YYYY
|
4-digit year
|
MM
|
Month (01-12; JAN = 01).
|
MON
|
Abbreviated name of month.
|
MONTH
|
Name of month, padded with blanks to length of 9 characters.
|
DD
|
Day of month (1-31).
|
HH
|
Hour of day (1-12).
|
HH12
|
Hour of day (1-12).
|
HH24
|
Hour of day (0-23).
|
MI
|
Minute (0-59).
|
SS
|
Second (0-59).
|
---------------------------------------------------------
Read Part-2 for formatting & date time arithmetic issues.
No comments :
Post a Comment