A detailed explanation of the differences between Oracle date and timestamp

Time:2020-4-5

A detailed explanation of the differences between Oracle date and timestamp

1. Date data type

We are so familiar with this data type that we will think of date type when we need to represent 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 will happen.

The problem with the date data type is that it represents that the granularity of measurement for the time interval between two events is seconds. This problem will be solved later when we discuss timestamp. You can use the to char function to wrap the date data in a traditional way, so as to express it in various 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 most people get into is calculating the number of years, months, days, hours, and seconds between the two times. You need to understand that when you subtract 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 number of intervals you want again. Here is my solution, which can accurately calculate the interval between two times. I understand that this example can be shorter, but I want to show all the numbers to emphasize the calculation.


SELECT TO_CHAR(date1, 'MMDDYYYY:HH24:MI:SS') date1,
    TO_CHAR(date2, 'MMDDYYYY:HH24:MI:SS') date2,
    trunc(86400 * (date2 - date1)) -
    60 * (trunc((86400 * (date2 - date1)) / 60)) seconds,
    trunc((86400 * (date2 - date1)) / 60) -
    60 * (trunc(((86400 * (date2 - date1)) / 60) / 60)) minutes,
    trunc(((86400 * (date2 - date1)) / 60) / 60) -
    24 * (trunc((((86400 * (date2 - date1)) / 60) / 60) / 24)) hours,
    trunc((((86400 * (date2 - date1)) / 60) / 60) / 24) days,
    trunc(((((86400 * (date2 - date1)) / 60) / 60) / 24) / 7) weeks
 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

2. Timestamp data type

The main problem with date data types is that they are not sufficiently granular to distinguish which event occurs first. Oracle has extended the timestamp data type on the date data type, which includes the information of year, day, hour, minute and second of all date data types, as well as the information of decimal seconds. 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 period of “. 000000” at the end of the converted period. This is because there is no decimal second information when converting from date. The default value is 0. And the display format is the default format set by the parameter NLS “timestamp” format. When you move the data of date type field in one table to timestamp type field in another table, you can directly write insert select statement, and Oracle will automatically convert it for you.


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

The format 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 has clearly shown that when the difference between the two times is extremely important, using the timestamp data type is more accurate than the date data type.


 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

It is easier to calculate the data difference between timestamps than the old date data type. When you subtract directly, see what happens. The results will be easier to understand, the first line of 17 days, 18 hours, 27 minutes and 43 seconds.


SELECT time1,
    time2,
    substr((time2 - time1), instr((time2 - time1), ' ') + 7, 2) seconds,
    substr((time2 - time1), instr((time2 - time1), ' ') + 4, 2) minutes,
    substr((time2 - time1), instr((time2 - time1), ' ') + 1, 2) hours,
    trunc(to_number(substr((time2 - time1), 1, instr(time2 - time1, ' ')))) days,
    trunc(to_number(substr((time2 - time1), 1, instr(time2 - time1, ' '))) / 7) weeks
 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

This means that you don’t need to worry about how many seconds a day are in the calculation. Therefore, getting the number of days, months, days, hours, minutes and seconds becomes a matter of fetching numbers with substr function.

3. System date and time

To get the system time, return to date data type. You can use the sysdate function.


SQL> SELECT SYSDATE FROM DUAL;

To get the system time, return to the timestamp data type. You can use the systimpstamp function.


SQL> SELECT SYSTIMESTAMP FROM DUAL;

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


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

4. Difference between date and timestamp

Date is a commonly used date variable in Oracle. Its time interval is seconds. The subtraction of two date types results in the interval of two times. Note that the unit is “day”. For example: check how long it will be before the opening of the London Olympic Games:


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

The result is: 92.2472685185 days, then you can convert the interval you want according to the corresponding time! This result may be useful for programmers. For those who want to see the result directly, this number is not very intuitive. Therefore, it leads to the timestamp type

Timestamp is an extension of date type, which can be accurate to fractional seconds (precision). It can be 0 to 9, and the default is 6. If you subtract two timestamps, you can’t get the number of days book directly. Instead, you can get the number of days book,

How many days, how many hours, how many seconds, etc., for example: also check the current distance from the opening of the London Olympic Games


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 interception, you can get 92 days, 5 hours, 51 minutes, 24 seconds, so that the user looks more intuitive! But this number is not very intuitive for programmers. If you want a specific length of time, and the accuracy is not required to milliseconds, you can change the timestamp type to date type, and then directly subtract.

5. The mutual conversion between date and timestamp can be achieved through

To char to convert timestamp – > date:


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

date ——>timestamp:


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

When using date and timestamp types, the choice is clear. You can handle date and timestamp types at will. When you try to switch to a more powerful timestamp, you need to be aware that they are similar and different enough to cause damage. Both of them have advantages in simplicity and space size, please choose them reasonably.

Thank you for reading, hope to help you, thank you for your support!