Subtracting Minutes from SYSTIMESTAMP or SYSDATE in H2 not working-CodePudding

Subtracting Minutes from SYSTIMESTAMP or SYSDATE in H2 not working .The result for following two commands are same:

select SYSTIMESTAMP from dual;    

select SYSTIMESTAMP- 30 / 1440 from dual;

CodePudding user response：

That math only works for DATE data types (e.g. SYSDATE), not for TIMESTAMP. For TIMESTAMP, use the INTERVAL function:

select systimestamp, systimestamp - interval '30' second from dual;

SYSTIMESTAMP                        SYSTIMESTAMP-INTERVAL'30'SECOND    
----------------------------------- -----------------------------------
13-APR-22 03.34.41.414075000 PM GMT 13-APR-22 03.34.11.414075000 PM GMT

See here for a thorough explanation of the differences in the two data types and their operations: https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals

H2 doesn't support the same functions as Oracle; use something like this:

SELECT DATEADD('SECOND',-30, CURRENT_TIMESTAMP) ...