异探究 Oracle 时间比较差异性（oracle时间比较差）
Time is an important factor in everyone’s life, and Oracle databases also need to pay special attention to it. Whether in database application development, database performance optimization, database security or other aspects, we always need to accurately calculate the time difference. Accordingly, Oracle database provides DATEDIFF and TIMESTAMPDIFF functions for us to calculate time differences.
DATEDIFF is a function provided in Oracle database to compare two dates. It returns the difference between two dates in terms of the specified time unit. It takes three arguments, where the first two arguments are mandatory, indicating the start and end dates of the time period, and the third argument is the optional time unit.
The time unit argument of DATEDIFF function can be one of the following format:
D,DD: days
Y,YY,YYYY: year
H,HH: Hour
MN,N: minutes
SS: second
For example:
SELECT DATEDIFF(‘2020-12-31 23:59:59′,’2020-1-1 00:00:00′,’y’) AS year_difference;
The result is that year_difference = 2020.
On the other hand, TIMESTAMPDIFF is a function in Oracle database that calculates the difference between two timestamps. The TIMESTAMPDIFF function also takes 3 arguments, just like the DATEDIFF function. The first 2 arguments are mandatory, representing the start and end time values of the time period, and the third argument is an optional time unit.
The time unit argument of TIMESTAMPDIFF function can be one of the following format:
FRAC_SECOND: Returns the difference as fraction of second
SECOND: Returns the difference as second
MINUTE: Returns the difference as minute
HOUR: Returns the difference as hour
DAY: Returns the difference as day
WEEK: Returns the difference as week
MONTH: Returns the difference as month
QUARTER: Returns the difference as quarter
YEAR: Returns the difference as year
For example:
SELECT TIMESTAMPDIFF(‘2020-12-31 23:59:59′,’2020-1-1 00:00:00′,’day’) AS day_difference;
The result is that day_difference = 364.
You can also combine these two functions together to implement more complex requirements.
In a word, if the basic date comparison is needed, the Oracle database uses the DATEDIFF function … If the time field is needed to calculate the difference, the Oracle database uses the TIMESTAMPDIFF function … Using them correctly can help us better and faster solve many time-related problems.
