Original author: James Koopmann
If you want to store Date and time information in Oracle, you can actually choose from the two field data types, let us see the difference between these two data types and what they have provided.
Date data type
This data type we are so familiar, when we need to indicate the date and time, you will think of a Date type. It can store months, year, day, century, time, division and seconds. It is typically used to say when things have happen or will happen. The Date data type problem is that it indicates that the measurement particle size of the two events time intervals is seconds. This issue will be resolved when the article will discuss TimeStamp later. You can use the To_Char function to conventionally package DATE data to achieve the purpose of representing a variety of 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
Most people I have seen are to calculate the number of intervals, months, days, hours and seconds of two time. What you need to understand is that when you have a subtraction of two dates, you get the number of days. You need to multiply the number of seconds per day (1 day = 86400 seconds), then you can calculate the number of space you want again. Below is my solution, you can accurately calculate two time intervals. I understand that this example can be more short, but I am to show all numbers to emphasize the calculation.
1 Select to_Char (Date1, 'MMDDYYYYYY: 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
The main problem with the TimeStamp data type Date data type is that it is not enough to distinguish between the two events. Oracle has expanded the TimeStamp data type on the Date data type, which includes information of all Date data types, and includes small seconds of information. Use the Cast function if you want to convert the DATE type to the TimeStamp type.
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 time period after the conversion. This is because the information does not have a decimal second when converted from Date, default is 0. Moreover, the display format is displayed in the default format of the parameter NLS_TimeStamp_Format. When you move the data of the Date Type field in a table to another table's TimeStamp type field, you can write directly to the Insert SELECT statement, and Oracle will automatically convert 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
