咨询：将Microsoft SQL日期分钟差查询转换为Oracle SQL

Hey there, let's get your query converted to Oracle SQL smoothly!

First, let's recap your original SQL Server query—it pulls records where date2 is more than 5 minutes later than date1, along with the ID and the minute difference:

SELECT t.Id, t.date1, t.date2, DATEDIFF(MINUTE, t.date1 , t.date2) AS Mtime 
FROM table1 t 
WHERE DATEDIFF(MINUTE,t.date1, t.date2) > 5

Oracle doesn't have the DATEDIFF function, but it has a simpler way to calculate date differences: when you subtract two DATE type values, the result is the number of days between them. To turn that into minutes, just multiply by 1440 (since 24 hours × 60 minutes = 1440 minutes per day).

A quick note on the order: your draft Oracle query had (t.date1 - t.date2), which would give you a negative number if date2 is later than date1—that's the opposite of what your SQL Server query does. We need to keep the order (t.date2 - t.date1) to match the original logic of checking if date2 is more than 5 minutes after date1.

Here's the corrected, clean Oracle version:

SELECT t.Id, t.date1, t.date2, (t.date2 - t.date1) * 1440 AS Mtime 
FROM table1 t 
WHERE (t.date2 - t.date1) * 1440 > 5;

If you actually want to find records where the absolute difference between the two dates is more than 5 minutes (regardless of which is earlier), just wrap the calculation in ABS():

SELECT t.Id, t.date1, t.date2, ABS((t.date2 - t.date1) * 1440) AS Mtime 
FROM table1 t 
WHERE ABS((t.date2 - t.date1) * 1440) > 5;

One extra tip: if your date1/date2 columns are TIMESTAMP instead of DATE, subtracting them gives an INTERVAL DAY TO SECOND value. For that case, you can extract the components to calculate minutes:

SELECT t.Id, t.date1, t.date2, 
       EXTRACT(DAY FROM (t.date2 - t.date1)) * 1440 +
       EXTRACT(HOUR FROM (t.date2 - t.date1)) * 60 +
       EXTRACT(MINUTE FROM (t.date2 - t.date1)) AS Mtime 
FROM table1 t 
WHERE EXTRACT(DAY FROM (t.date2 - t.date1)) * 1440 +
      EXTRACT(HOUR FROM (t.date2 - t.date1)) * 60 +
      EXTRACT(MINUTE FROM (t.date2 - t.date1)) > 5;

But for standard DATE columns, the first approach is totally sufficient and much more concise.

内容的提问来源于stack exchange，提问作者Sam