Search code examples
sqlselecttimestampderby

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

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

Solution

  • 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;