SQL calculation timestamp difference example sharing

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

SQL method of calculating timestamp difference

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.

Related recommendations:

The difference between Datetime and Timestamp in Mysql

How timestamp automatically updates time in MySQL database Method

Does Mysql use int, timestamp or datetime to store time fields?

The above is the detailed content of SQL calculation timestamp difference example sharing. For more information, please follow other related articles on the PHP Chinese website!