How to get 10,000 years (max) interval in days?-CodePudding

To get 10,000 years -- the max interval value -- I can use year or month:

SELECT INTERVAL 10000 year, INTERVAL 10000*12 month

What is the value that I would use for day to get the 10,000 year value? Any variation I've tried of it either does not convert-up from day to year or overflows (if I go beyond). Is there a way to convert day into year in the output, or BQ never converts it?

As an example:

CodePudding user response：

From JUSTIFY_DAYS documentation:

Normalizes the day part of the interval to the range from -29 to 29 by incrementing/decrementing the month or year part of the interval.

INTERVAL 1 MONTH is same as INTERVAL 30 DAY and therfore INTERVAL 1 YEAR will be INTERVAL 30 DAY * 12 which is INTERVAL 360 DAY.

SELECT INTERVAL 1 MONTH = INTERVAL 30 DAY month_is_30,
       INTERVAL 1 MONTH = INTERVAL 31 DAY month_is_31,
       INTERVAL 1 YEAR = INTERVAL 360 DAY year_is_360,
       INTERVAL 1 YEAR = INTERVAL 365 DAY year_is_365,

 ------------- ------------- ------------- ------------- 
| month_is_30 | month_is_31 | year_is_360 | year_is_365 |
 ------------- ------------- ------------- ------------- 
| true        | false       | true        | false       |
 ------------- ------------- ------------- -------------

What is the value that I would use for day to get the 10,000 year value?

SELECT INTERVAL 10000 * 360 DAY days,
       JUSTIFY_DAYS(INTERVAL 10000 * 360 DAY) max_years;
 ------------------- ----------------- 
|     days          |    max_years    |
 ------------------- ----------------- 
| 0-0 3600000 0:0:0 | 10000-0 0 0:0:0 |
 ------------------- -----------------