How to use timestaffdiff in DerbySQL to find the records with the time gap not larger than 1 hour?-CodePudding

So I've tried to use the timestamp diff to calculate and get the results with time gap not larger than 1 hour from the current time. However, it is not working as intended.

SELECT * FROM schedule WHERE {fn TIMESTAMPDIFF( SQL_TSI_HOUR, CURRENT_TIMESTAMP, STARTTIME)} <= 1

When I run the code, all results with gap more than 1 day are displayed, as screenshot below with my current timestamp as reference. the results returned

CodePudding user response：

My guess is that you need to swap the order of the two timestamp inputs to TIMESTAMPDIFF():

SELECT *
FROM schedule
WHERE {fn TIMESTAMPDIFF(SQL_TSI_HOUR, STARTTIME, CURRENT_TIMESTAMP)} <= 1;