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

David542 0
Viewed 75

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

SELECT INTERVAL 10000 year, INTERVAL 10000*12 month

enter image description here

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:

enter image description here

google-bigquery sql
1 Answers

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 |
+-------------------+-----------------+
Jaytiger0
Related