Oracle timestamp type

We all know that date and timestamp are both expressions of date and time, but the two types of precision are different. The former is accurate to seconds, and the latter is accurate to decimal seconds (Fractional_seconds_precision), which can be0 to 9. The default value is 6.

However, the date operation is very simple, and many functions can be used for processing; the difference between the two timestamp is that it intuitively shows the number of days, hours, minutes, seconds, and decimal seconds,

SQL> create table test (T1 TIMESTAMP (6 ),
2 T2 TIMESTAMP (6 ));

The table has been created.

SQL> insert into test values (
2 to_timestamp ('2017-01-01 12:10:10. 1', 'yyyy-mm-dd hh24: mi: ss. ff '),
3 to_timestamp ('2017-01-01 12:20:10. 2', 'yyyy-mm-dd hh24: mi: ss. ff '));

One row has been created.

SQL>
SQL> insert into test values (
2 to_timestamp ('2017-01-01 12:10:10. 1', 'yyyy-mm-dd hh24: mi: ss. ff '),
3 to_timestamp ('2017-01-02 12:20:10. 2', 'yyyy-mm-dd hh24: mi: ss. ff '));

One row has been created.

SQL>
SQL> insert into test values (
2 to_timestamp ('2017-01-01 12:10:10. 1', 'yyyy-mm-dd hh24: mi: ss. ff '),
3 to_timestamp ('2017-01-02 13:40:20. 2', 'yyyy-mm-dd hh24: mi: ss. ff '));

One row has been created.

SQL> commit;

Submitted.

SQL>

The difference between the two timestamp is that it intuitively shows the number of days, hours, minutes, seconds, and decimal seconds:
SQL> select t2-t1 from test;
+ 000000000 00:10:00. 100000
+ 000000001 00:10:00. 100000
+ 000000001 01:30:10. 100000

SQL>

However, it is difficult to simply convert the data into a specific precision. It is not feasible to use a method similar to the date type. For example, to score:
SQL> select 1440 * (t2-t1) from test;
+ 000000010 00:02:24. 000000000
+ 000001450 00:02:24. 000000000
+ 000001530 04:02:24. 000000000

SQL>

The results are not originally expected, but multiplied by 1440 for each item of the original "how many days + how many hours + how many minutes + how many seconds + how many decimal seconds" and then processed in the traveling system.

The easiest way to understand is to use substr to split and convert the difference between two timestamps:

SQL> SELECT substr (t2-t1), instr (t2-t1), '') +) seconds,
2 substr (t2-t1), instr (t2-t1), '') +) minutes,
3 substr (t2-t1), instr (t2-t1), '') +) hours,
4 trunc (to_number (substr (t2-t1), 1, instr (t2-t1, '') days,
5 trunc (to_number (substr (t2-t1), 1, instr (t2-t1, '')/7) weeks
6 FROM test;

SECO MINU HOUR DAYS WEEKS
--------------------------------
00 10 00 0 0
00 10 00 1 0
10 30 01 1 0

Or use custom functions to implementConvert the number of days to the format of "day, minute, and second:

Create or replace function F_DAYS2STR (P_DAYS IN NUMBER DEFAULT0)
RETURN VARCHAR2 IS
-- Ver: 1.0
-- Created by xsb on 2005-05-26
--:Convert days to days, hours, minutes, And seconds
DAYS NUMBER: = NVL (P_DAYS,0);
VD NUMBER;--Days
Vl NUMBER;--Hours
VM NUMBER;--Minute
VS NUMBER;--Seconds
RESULT VARCHAR2 (100);--Return Value
BEGIN
VD: = TRUNC (DAYS );
Vl: = TRUNC (DAYS-VD )*24);
VM: = TRUNC (DAYS-VD-Flac/24)*24*60);
VS: = TRUNC (DAYS-VD-Flac/24-VM/24/60)*24*60*60);
Select decode (VD,0,'', VD |'Days') | DECODE (VH,0,'', VL |'Hours') | DECODE (VM,0,'', VM |'Minute') | DECODE (,0,'', VS |'Seconds') Into result from dual;
RETURN (RESULT );
END;

SQL>

If the accuracy requirement of the final result is not high (when the score is greater than or equal to the score), you can convert the timestamp to date before settlement. This is much simpler:

SQL> select (to_date (to_char (t2, 'yyyy-mm-dd hh24: mi: ss'), 'yyyy-mm-dd hh24: mi: ss'
)-To_date (to_char (t1, 'yyyy-mm-dd hh24: mi: ss'), 'yyyy-mm-dd hh24: mi: ss') * 24*60
2 from test;

10

1450

1530.16667

Convert date to timestamp:

Select cast (sysdate as timestamp) from dual;