SQL method for calculating timestamp difference

SQL method for calculating timestamp difference

Overview

Sometimes we need to find certain records by time, for example: calculate the records one hour before the sales time.
Usually we can use MYSQL's timestampdiff function to do this, but this cannot use the index, and if the amount of data is large, it will cause slow queries.

Use code to calculate the time and then pass it to SQL

We can use JAVA code to calculate the time first, and then pass it to the SQL statement to avoid using MYSQL functions.

public long xxxx(long sellTimeFrom){
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(new Date(sellTimeFrom));
    calendar.set(Calendar.HOUR_OF_DAY,calendar.get(Calendar.HOUR_OF_DAY) - 1);
    return calendar.getTime().getTime();
}

This will calculate the time one hour before the sale time. Then pass it into the SQL statement where you write the code snippet. In this way, if the sales time field has an index, the index can be used.

Thank you for reading, I hope it can help you, thank you for your support of this site!