How to parse SQL to calculate the difference between timestamps

This article mainly introduces relevant information on the method of calculating the difference of timestamp in SQL. Friends who need it can refer to

How to calculate the difference of timestamp in SQL

Overview

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

Use the 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 function.

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 way you can calculate the time one hour before the sale time. Then pass it into the SQL statement and write the code piece here, so that if the sales time field is indexed, the index can be used.

The above is the detailed content of How to parse SQL to calculate the difference between timestamps. For more information, please follow other related articles on the PHP Chinese website!