Difference between oracle date and timestamp, oracletimestamp

Difference between oracle date and timestamp, oracletimestamp
1. DATE data type

We are very familiar with this data type. We will always think of the date type when we need to represent the date and time. It can store months, years, days, centuries, hours, minutes, and seconds. It is typically used to indicate when something has happened or is about to happen.
The problem with the DATE data type is that it indicates that the interval between two events is measured in seconds. This problem will be solved later when we discuss timestamp. You can use the TO_CHAR function to traditionally wrap the DATE data to express it in multiple formats.

1 SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;2 3 Date 4 --------------------------- 5 06/20/2003 16:55:14 6 06/26/2003 11:16:36

The trouble for most people is to calculate the number of years between two times, the number of months, the number of days, the number of hours, and the number of seconds. What you need to understand is that when you perform the subtraction operation on two dates, you get the number of days. You need to multiply the number of seconds per day (1 day = 86400 seconds), and then you can calculate the expected number of intervals again. The following is my solution. We can accurately calculate the two time intervals. I understand that this example can be shorter, but I want to show all the numbers to emphasize the calculation method.

 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) weeks11   FROM date_table12 13 DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS 14 ----------------- ----------------- ---------- ---------- ---------- ---------- ---------- 15 06202003:16:55:14 07082003:11:22:57 43 27 18 17 2 16 06262003:11:16:36 07082003:11:22:57 21 6 0 12 1

2. TIMESTAMP Data Type

The main problem with the DATE data type is that its granularity cannot determine which of the two events occur first. ORACLE has extended the TIMESTAMP data type in the DATE data type, which includes information about all DATE data types, such as year, month, day, hour, minute, and second, and contains information about decimal seconds. If you want to convert the DATE type to the TIMESTAMP type, use the CAST function.

1 SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t; 2 Date 3 ----------------------------------------------------- 4 20-JUN-03 04.55.14.000000 PM 5 26-JUN-03 11.16.36.000000 AM

As you can see, there is a section ". 000000" at the end of the converted time period ". This is because there is no decimal second information during the conversion from date. The default value is 0. The display format is based on the default format specified by the NLS_TIMESTAMP_FORMAT parameter. When you move the data in the date Field of a table to the timestamp field of another table, you can directly write the insert select statement. oracle will automatically convert the data for you.

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table 2 Date 3 ------------------- 4 06/20/2003 16:55:14 5 06/26/2003 11:16:36

The formatted display of TIMESTAMP data is the same as that of DATE data. Note that the to_char function supports date and timestamp, but trunc does not support the TIMESTAMP data type. This clearly shows that the use of TIMESTAMP data type is more accurate than the DATE data type when the difference between the two time is extremely important.

1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table2 3 Date 4 ----------------------- 5 06/20/2003 16:55:14:000 6 06/26/2003 11:16:36:000

It is easier to calculate the data difference between timestamp than the old date data type. If you subtract it, you can see what will happen. The result is easier to understand, 17 days, 18 hours, 27 minutes, and 43 seconds in the first line.

 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 9   10 TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS 11 ------------------------- -------------------------- ------- ------- ----- ---- ----- 12 06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2 13 06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1 

This means that you no longer need to worry about how many seconds a day is in troublesome computing. Therefore, getting the number of days, months, days, hours, minutes, And seconds is the use of the substr function to extract numbers.

3. system date and time

To obtain the system time, the Data Type of date is returned. You can use the sysdate function.

1 SQL> SELECT SYSDATE FROM DUAL;

To obtain the system time, the data type is returned as timestamp. You can use the aggregate impstamp function.

1 SQL> SELECT SYSTIMESTAMP FROM DUAL;

You can set the initialization parameter FIXED_DATE to specify the sysdate function to return a fixed value. This is used in code that is sensitive to test date and time. Note that this parameter is invalid for the systimestamp function.

 1 SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';  2 System altered. 3  4 SQL> select sysdate from dual;  5 SYSDATE  6 ---------  7 01-JAN-03 8  9 SQL> select systimestamp from dual; 10 SYSTIMESTAMP 11 --------------------------------------------------------- 12 09-JUL-03 11.05.02.519000 AM -06:00

4. Difference between date and timestamp

The date type is a common date variable in Oracle, and its interval is second. The offset between the two date types is the interval of two times. Note that the unit is "day ". For example, you can see how long it is before the opening of the London Olympics:

1 select to_date('2012-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss')-sysdate from dual

The result is: 92.2472685185185 days, and then you can convert the desired interval based on the corresponding time! This result may be useful to programmers. For those who want to directly see the result, this number is not very intuitive, so the timestamp type is introduced.

Timestamp is an extension of the DATE type. It can be precise to the decimal second (fractional_seconds_precision). It can be 0 to9 and the default value is 6. If two timestamp values are subtracted, you cannot get the number of days book directly, but instead,
How many days, how many hours, how many seconds, etc. For example, you can also check the length of time from the opening of the London Olympics.

1 select to_timestamp('2012-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss')-systimestamp from dual

The result is: + 000000092 05:51:24. 032000000. With a little screenshot, you can get 92 days, 5 hours, 51 minutes, and 24 seconds. This makes the user look more intuitive! However, this number is not intuitive for programmers. If you want a specific length of time and the precision is not limited to milliseconds, you can convert the timestamp type to the date type and then subtract it directly.

5. Mutual conversion between date and timestamp can be achieved through

To_char to convert timestamp --> date:

1 select to_date(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual

Date --> timestamp:

1 select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual

When the date and timestamp types are used, the selection is clear. You can handle the date and timestamp types at will. When you try to convert to a more powerful timestamp, You need to note that they both have similarities and differences, which is enough to cause damage. The two have their own advantages in conciseness and interval size. Please choose a proper one.