DATE_DIFF
Computes the difference between two timestamps.
Syntax
DATE_DIFF(UNIT, TIMESTAMP1, TIMESTAMP2)
Arguments
UNIT
Type: string
The unit of time to return.
It can be one of the following:
yearquartermonthweekdayhourminutesecondmillisecond
TIMESTAMP1
Type: timestamp
The time to subtract from TIMESTAMP2.
TIMESTAMP2
Type: timestamp
The time TIMESTAMP1 is subtracted from.
Returns
Type: bigint
TIME2 - TIME1 expressed in terms of UNIT.
Examples
| UNIT | TIMESTAMP1 | TIMESTAMP2 | Output |
|---|---|---|---|
| day | 2001-08-22 03:04:05.321
|
2001-08-23 03:04:05.321
|
1 |
| year | 2001-08-22 03:04:05.321
|
2006-08-22 03:04:05.321
|
5 |
| year | 2006-08-22 03:04:05.321'
|
2001-08-22 03:04:05.321
|
-5 |
| second | 2001-08-22 03:04:05.321
|
2001-08-22 03:04:35.321
|
30 |
| quarter | 2001-08-22 03:04:05.321
|
2001-11-22 03:04:05.321
|
1 |
| quarter | 2001-08-22 03:04:05.321
|
2001-08-22 03:04:05.321
|
0 |
| year | 2001-08-22 03:04:05.321
|
null
|
null
|