Comparison of ORACLE date and timestamp data types (i)

Date data type

This data type is so familiar that we think of date types when we need to represent dates and times. It can store months, years, days, centuries, hours, minutes, and seconds. It is typically used to indicate when something has happened or is going to happen. The problem with the date data type is that it indicates that the measure granularity of the time interval for two events is seconds. This problem will be solved when the article discusses timestamp later. You can use the To_char function to wrap date data in a traditional way to represent multiple formats.

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

The trouble with most of the people I've met is to calculate the number of years, months, days, hours, and seconds between two time intervals. What you need to know is that when you subtract two dates, you get the number of days. You need to multiply the number of seconds per day (1 days = 86,400 seconds), and then you can calculate the number of intervals you want. The following is my solution, which can be used to accurately calculate the interval between two times. I know this example can be a little bit shorter, but I'm trying to show all the numbers to emphasize the way I calculate.

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) days,

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