SQL Macro – Đơn giản hóa query phức tạp: Tính toán thời gian

Chắc chắn rằng bạn cũng đã sử dụng SQL Macro nhiều rồi, và bạn cũng sẽ thấy được nó rất hiệu quả, hữu ích và giúp việc lập trình trở lên dễ dàng hơn.
Bài viết này nói về việc sử dụng SQL Macro để tính toán thời gian chênh lệch giữa 2 giá trị thời gian.

Đối với kiểu dữ liệu DATE trong Oracle database, để tính toán thời gian giữa 2 giá trị date rất đơn giản, chúng ta chỉ cần trừ 2 giá trị này cho nhau và quy đổi sang giây là OK.
Như bạn có thể xem query dưới đây:

SQL> select started, ended, (ended-started)*24*60*60
  2  from t1;

STARTED                              ENDED                                (ENDED-STARTED)*24*60*60
------------------------------------ ------------------------------------ ------------------------
15/11/2021 12:04:09                  16/11/2021 16:52:09                                    103680
15/11/2021 12:04:09                  15/11/2021 21:40:09                                     34560
15/11/2021 12:04:09                  15/11/2021 12:35:50                                      1901
15/11/2021 12:04:09                  15/11/2021 12:05:55                                       106
15/11/2021 12:04:09                  15/11/2021 12:06:00                                       111

Tuy nhiên, với kiểu dữ liệu TIMESTAMP, thì mọi chuyện lại khác. Khi trừ 2 giá trị TIMESTAMP kết quả sẽ trả về INTERVAL.

SQL> select started, ended, (ended-started) ela
  2  from   t;

STARTED                              ENDED                                ELA
------------------------------------ ------------------------------------ -------------------------------
15-NOV-21 12.04.09.000000 PM         16-NOV-21 04.52.09.000000 PM         +000000001 04:48:00.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 09.40.09.000000 PM         +000000000 09:36:00.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.35.50.000000 PM         +000000000 00:31:41.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.05.55.000000 PM         +000000000 00:01:46.000000
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.06.00.123000 PM         +000000000 00:01:51.123000

Nếu bạn muốn trả về dưới dạng giây, bạn phải xử lý khá phức tạp như sau:

SQL> select started, ended, extract(day from (ended-started))*86400+    --| W
  2                           extract(hour from (ended-started))*3600+  --| T
  3                           extract(minute from (ended-started))*60+  --| H
  4                           extract(second from (ended-started)) ela  --| !
  5  from t;

STARTED                              ENDED                                       ELA
------------------------------------ ------------------------------------ ----------
15-NOV-21 12.04.09.000000 PM         16-NOV-21 04.52.09.000000 PM             103680
15-NOV-21 12.04.09.000000 PM         15-NOV-21 09.40.09.000000 PM              34560
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.35.50.000000 PM               1901
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.05.55.000000 PM                106
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.06.00.123000 PM            111.123

Chúng ta không có cách nào để loại bỏ điều này, nhưng với 21c scalar SQL Macro, chúng ta có thể giảm bớt đi nỗ lực bằng cách chỉ sử dụng nó một lần, và điều này cũng có ích hơn khi check lỗi cũng như nâng cấp SQL code.

SQL> create or replace function elapsed(
  2    ts1 in timestamp,
  3    ts2 in timestamp
  4  ) return varchar2 sql_macro(scalar) is
  5  begin
  6    return '
  7  extract(day from (ts2-ts1))*86400+
  8  extract(hour from (ts2-ts1))*3600+
  9  extract(minute from (ts2-ts1))*60+
 10  extract(second from (ts2-ts1))';
 11  end;
 12  /

Function created.

Bây giờ code đã tốt hơn rất nhiều:

SQL> select started, ended, elapsed(started,ended) ela from t;

STARTED                              ENDED                                       ELA
------------------------------------ ------------------------------------ ----------
15-NOV-21 12.04.09.000000 PM         16-NOV-21 04.52.09.000000 PM             103680
15-NOV-21 12.04.09.000000 PM         15-NOV-21 09.40.09.000000 PM              34560
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.35.50.000000 PM               1901
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.05.55.000000 PM                106
15-NOV-21 12.04.09.000000 PM         15-NOV-21 12.06.00.123000 PM            111.123

SQL Macros sẽ giúp cho việc viết các câu lệnh SQL trở lên trong sáng và thú vị hơn.