Comparison of ORACLE date and timestamp data types (i)

oracle| Comparison | data | data type
Original Author: James Koopmann

If you want to store date and time information in Oracle, you are actually choosing from two types of field data, let's look at the differences between the two data types and what they provide.



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



TIMESTAMP data type

The main problem with the date data type is that it doesn't have enough granularity to distinguish between two events which first occurs. Oracle has extended the timestamp data type on the date data type, which includes information about the seconds and seconds of all date data types, and includes the decimal second information. If you want to convert the date type to the timestamp type, use the cast function.

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



As you can see, there is a ". 000000" at the end of the converted time period. This is because when you convert from date, there is no information for decimal seconds, and the default is 0. and the display format is displayed in the default format set by the parameter Nls_timestamp_format. When you move data from a Date Type field in a table to a timestamp type field in another table, you can write the Insert SELECT statement directly, and Oracle will automatically convert it for you.

1 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