· DATA type has the ability to store the month, day, year, century, hours, minutes, and seconds.
· To represent the date stored in a more readable format, use TO_CHAR function
Formatting a date
SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date"
FROM date_table;
Date
---------------------------
06/20/2003 16:55:14
06/26/2003 11:16:36
· 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.
Determine the interval breakdown between two dates for a DATE datatype
1 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
2 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
3 trunc(86400*(date2-date1))-
4 60*(trunc((86400*(date2-date1))/60)) seconds,
5 trunc((86400*(date2-date1))/60)-
6 60*(trunc(((86400*(date2-date1))/60)/60)) minutes,
7 trunc(((86400*(date2-date1))/60)/60)-
8 24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
9 trunc((((86400*(date2-date1))/60)/60)/24) days,
10 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks
11* FROM date_table
DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS
----------------- ----------------- -------- -------- ----- ----- -----
06202003:16:55:14 07082003:11:22:57 43 27 18 17 2
06262003:11:16:36 07082003:11:22:57 21 6 0 12 1
·
· 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.
· If you want to convert a DATE datatype to a TIMESTAMP datatype format, 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).
Convert DATE datatype to TIMESTAMP datatype
SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;
Date
-----------------------------------------------------
20-JUN-03 04.55.14.000000 PM
26-JUN-03 11.16.36.000000 AM
· 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 straight INSERTSELECT FROM and Oracle will do the conversion for you.
Formatting of the TIMESTAMP datatype
SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table
Date
-------------------
06/20/2003 16:55:14
06/26/2003 11:16:36
· TO_CHAR function works with both datatypes,
· TRUNC function not work with a datatype of TIMESTAMP.
· The use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, use TO_CHAR (we are only showing 3 place holders for the fractional seconds)
Formatting of the TIMESTAMP datatype with fractional seconds
1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
Date
-----------------------
06/20/2003 16:55:14:000
06/26/2003 11:16:36:000
· Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE datatype, just do straight subtraction of the columns. No more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function
Straight subtraction of two TIMESTAMP datatypes
1 SELECT time1, time2, (time2-time1)
2* FROM date_table
TIME1 TIME2 (TIME2-TIME1)
------------------------------ ---------------------------- ----------------------
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 +000000017 18:27:43.000000
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 +000000012 00:06:21.000000
Determine the interval breakdown between two dates for a TIMESTAMP datatype
1 SELECT time1,
2 time2,
3 substr((time2-time1),instr((time2-time1),' ')+7,2) seconds,
4 substr((time2-time1),instr((time2-time1),' ')+4,2) minutes,
5 substr((time2-time1),instr((time2-time1),' ')+1,2) hours,
6 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))) days,
7 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
8* FROM date_table
TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS
------------------------- -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2
06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1
· SYSDATE function return DATE datatype for the system date and time ;
SQL> SELECT SYSDATE FROM DUAL;
· SYSTIMESTAMP function return TIMESTAMP datatype for the system date and time
SQL> SELECT SYSTIMESTAMP FROM DUAL;
§ You can set the initialization parameter FIXED_DATE to return a constant value for what is returned from the SYSDATE function. This is a great tool for testing date and time sensitive code. Just beware that this parameter has no effect on the SYSTIMESTAMP function.
Setting FIXED_DATE and effects on SYSDATE and SYSTIMESTAMP
SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
System altered.
SQL> select sysdate from dual;
SYSDATE
---------
01-JAN-03
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------
09-JUL-03 11.05.02.519000 AM -06:00
