Repository navigation
Interval Literal output inconsistent date_type #3180
Description
Describe the bug
A clear and concise description of what the bug is.
the output of INTERVAL XXX is inconsistent
To Reproduce
Steps to reproduce the behavior:
while we only have year or month, it outputs IntervalYearMonth
❯ select interval '1 year';
+------------------------------------------------+
| IntervalYearMonth("12") |
+------------------------------------------------+
| 1 years 0 mons 0 days 0 hours 0 mins 0.00 secs |
+------------------------------------------------+
1 row in set. Query took 0.001 seconds.
❯ select interval '1 year 1 month';
+------------------------------------------------+
| IntervalYearMonth("13") |
+------------------------------------------------+
| 1 years 1 mons 0 days 0 hours 0 mins 0.00 secs |
+------------------------------------------------+while we have (year or month) and any other unit, it outputs IntervalMonthDayNano
+--------------------------------------------------------+
| IntervalMonthDayNano("950737950189618795196236955648") |
+--------------------------------------------------------+
| 0 years 12 mons 1 days 0 hours 0 mins 0.00 secs |
+--------------------------------------------------------+
1 row in set. Query took 0.001 seconds.
❯ select interval '1 year 1 second';
+--------------------------------------------------------+
| IntervalMonthDayNano("950737950171172051123527404032") |
+--------------------------------------------------------+
| 0 years 12 mons 0 days 0 hours 0 mins 1.00 secs |
+--------------------------------------------------------+
1 row in set. Query took 0.001 seconds.Otherwise, it outputs IntervalDaytime
❯ select interval '1 second';
+------------------------------------------------+
| IntervalDayTime("1000") |
+------------------------------------------------+
| 0 years 0 mons 0 days 0 hours 0 mins 1.00 secs |
+------------------------------------------------+
1 row in set. Query took 0.001 seconds.
❯ select interval '1 day 1 second';
+------------------------------------------------+
| IntervalDayTime("4294968296") |
+------------------------------------------------+
| 0 years 0 mons 1 days 0 hours 0 mins 1.00 secs |
+------------------------------------------------+
1 row in set. Query took 0.001 seconds.Expected behavior
A clear and concise description of what you expected to happen.
they should be consistent, as our current Timestamp is in nanosecond, i suggest using IntervalMonthDayNano. We could consider add functions to output specific data type(i.e. to_interval_monthdaynano(), to_interval_daytime(), interval_yearmonth() . But i think they might need different arithmetics
Additional context
Add any other context about the problem here.
Activity
waitingkuo commentedon Aug 16, 2022
just found that it's designed in purpose. let me close this and fire another ticket for discussing