Getting the Difference Between Two DATEs-Datatypes
Another frequently asked question is, “How do I retrieve the difference between two dates?” The answer is deceptively simple: you just subtract them. This will return a number representing the number of days between the two dates.
Additionally, you have the built-in function MONTHS_BETWEEN that will return a number representing the number of months—including fractional months—between two dates. Lastly, with the INTERVAL datatypes, you have yet another method to see the elapsed time between two dates.
The following SQL query demonstrates the outcome of subtracting two dates (showing the number of days between them), using the MONTHS_BETWEEN function and then the two functions used with INTERVAL types:
SQL> select dt2-dt1 ,months_between(dt2,dt1) months_btwn,numtodsinterval(dt2-dt1,’day’) days,numtoyminterval(trunc(months_between(dt2,dt1)),’month’) monthsfrom (select to_date(’29-feb-2000 01:02:03′,’dd-mon-yyyy hh24:mi:ss’) dt1, to_date(’15-mar-2001 11:22:33′,’dd-mon-yyyy hh24:mi:ss’) dt2from dual );DT2-DT1 MONTHS_BTWN DAYS MONTHS
Those are all correct values, but not of great use to us yet. Most applications would like to display the years, months, days, hours, minutes, and seconds between the dates. Using a combination of the preceding functions, we can achieve that goal. We’ll select out two intervals: one for the years and months, and the other for just the day, hours, and so on. We’ll use the MONTHS_BETWEEN built-in function to determine the decimal number of months between the two dates, and then we’ll use the NUMTOYMINTERVAL built-in function to convert that number into the years and months. Additionally, we’ll use MONTHS_BETWEEN to subtract the integer number of months between the two dates from the larger of the two dates to get down to the days and hours between them:
SQL> select numtoyminterval(trunc(months_between(dt2,dt1)),’month’)years_months,numtodsinterval(dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ), ‘day’ )days_hoursfrom (select to_date(’29-feb-2000 01:02:03′,’dd-mon-yyyy hh24:mi:ss’) dt1, to_date(’15-mar-2001 11:22:33′,’dd-mon-yyyy hh24:mi:ss’) dt2from dual );YEARS_MONTHS DAYS_HOURS
Now it is clear that there is 1 year, 15 days, 10 hours, 20 minutes, and 30 seconds between the two DATEs.
TIMESTAMP Type
The TIMESTAMP type is very much like the DATE, with the addition of support for fractional seconds and time zones. We’ll look at the TIMESTAMP type in the following three sections: one with regard to just the fractional second support but no time zone support, and the other two with regard to the two methods of storing the TIMESTAMP with time zone support.
TIMESTAMP
The syntax of the basic TIMESTAMP datatype is straightforward:
TIMESTAMP(n)
where N is optional; it is used to specify the scale of the seconds component in the timestamp and may take on values between zero and nine. If you specify zero, then a TIMESTAMP is functionally equivalent to a DATE and, in fact, stores the same values in the same manner:
$ sqlplus eoda/foo@PDB1
SQL> create table t( dt date,ts timestamp(0));Table created.
SQL> insert into t values ( sysdate, systimestamp ); 1 row created.
SQL> select dump(dt,10) dump, dump(ts,10) dump from t;DUMP
Typ=12 Len=7: 120,121,7,17,22,31,15
Typ=180 Len=7: 120,121,7,17,22,31,16
The datatypes are different (the Typ=field indicates that), but the manner in which they store data is identical. The TIMESTAMP datatype will differ in length from the DATE type when you specify some number of fractional seconds to preserve, for example:
SQL> create table t( dt date,ts timestamp(9));Table created.
SQL> insert into t values ( sysdate, systimestamp ); 1 row created.
We can see the fractional seconds that were stored are there in the last 4 bytes. We used the DUMP function to inspect the data in HEX this time (base 16) so we could easily convert the 4 bytes into the decimal representation.