Documents Created in last 5 min

Question

Hi

I have below code and Time is system is stored as below in Database . I want Documents created in last 5 min from Current Date and Time.. If now is 17:00 than I need documents added from 16:55 till 17:00

Query is in Hana version

14:37

15:36

SELECT 
T0."DocDate" "Doc. Date",  

T1."Remarks" FROM OWDD T0 inner 
join WDD1 T1 on T1."WddCode" = T0."WddCode"
where T1."Status" = 'W'

Thanks

Tuhin Paul · Answer

Optimized version of the query is : SELECT T0.DocDate AS Doc. Date T1.Remarks FROM OWDD T0 INNER JOIN WDD1 T1 ON T1.WddCode = T0.WddCode WHERE T1.Status = 'W' AND T0.DocDate >= SYSDATE - INTERVAL '5' MINUTE Why this is more efficient: SYSDATE is a direct function that can return the current date and time and in some cases it may perform better because it is optimized internally in HANA. The query reduces the time spent on any unnecessary data processing by filtering directly on the DocDate within the last 5 minutes ensuring only relevant rows are processed.

Tuhin Paul · Answer

To retrieve documents that were created in the last 5 minutes from the current date and time in SAP HANA you need to filter the DocDate based on the current system time. In HANA you can use CURRENT_TIMESTAMP to get the current system time. SELECT T0.DocDate AS Doc. Date T1.Remarks FROM OWDD T0 INNER JOIN WDD1 T1 ON T1.WddCode = T0.WddCode WHERE T1.Status = 'W' AND T0.DocDate >= CURRENT_TIMESTAMP - INTERVAL '5' MINUTE

Sreenath Kappoor · Answer

Hi Ramco Add this to the where condition T0.DocDate >= ADD_SECONDS(CURRENT_TIMESTAMP -300);