sql - 如何在两列数据类型均为日期的Oracle的两列之间，以分钟为单位，最多到小数点后两位小数？(How to find difference in minute up to two decimal between two column in Oracle where both column datatype is date?)

Question

I want to subtract the column entrystarttime from column entryfinishtime in the entry table so as to get the difference in minutes up to two decimal places in Oracle database.

(我想从entry表中的列entryfinishtime中减去列entrystarttime ，以便在分钟之内得到Oracle数据库中最多两位小数的差异。)

Both columns' datatype is date.

(两列的数据类型均为日期。)
An example of what's inserted into the entry table is

(entry表中插入的示例是)

INSERT INTO entry(entrystarttime,entryfinishtime) 
VALUES (TO_DATE('01-feb-2018:10:00:00 AM', 'dd-mon-yyyy:hh:mi:ss am'),
    TO_DATE('01-feb-2018:10:59:30 AM', 'dd-mon-yyyy:hh:mi:ss am');

Here is what I attempted so far

(到目前为止，这是我尝试过的)

select  to_date(entryfinishtime, 'dd-mon-yyyy:hh:mi:ss am') - to_date(entrystarttime, 'dd-mon-yyyy:hh:mi:ss am')from entry;

But all I get are rows of 0s.

(但是我得到的只是0行。)

Like this

(像这样)

TO_DATE(ENTRYFINISHTIME,'DD-MON-YYYY:HH:MI:SSAM')-TO_DATE(ENTRYSTARTTIME,'DD-MON-YYYY:HH:MI:SSAM')
--------------------------------------------------------------------------------------------------
                                                                                                 0
                                                                                                 0
                                                                                                 0
                                                                                                 0
                                                                                                 0
                                                                                                 0
                                                                                                 0
                                                                                                 0

  ask by Fish translate from so

Answer 1

Your values are already dates, so there is no need to convert them again.

(您的值已经是日期，因此无需再次转换它们。)

To get the difference in minutes:

(要在几分钟内获得差异：)

select (entryfinishtime - entrystarttime) * 24 * 60

To get this to two decimal places:

(要将其保留到小数点后两位：)

select round((entryfinishtime - entrystarttime) * 24 * 60, 2)

Here is a db<>fiddle.

(这是db <>小提琴。)