[Discussion] Do we need to support more than 24 hours in TIME? #852
Description
OpenSearch SQL can store up to 24 hours in TIME data type. It is not a bug, most of other SQL engines do the same. As far as we are trying to align OpenSearch SQL with MySQL, we should consider the same behavior for TIME type as MySQL has.
In MySQL
TIMEis not limited by 24 hours.
https://www.techonthenet.com/mysql/functions/maketime.php
mysql> SELECT time1, DATE_ADD(time1, INTERVAL 24 HOUR) AS `time + 24h` FROM calcs LIMIT 6;
+----------+------------+
| time1 | time + 24h |
+----------+------------+
| 19:36:22 | 43:36:22 |
| 02:05:25 | 26:05:25 |
| 09:33:31 | 33:33:31 |
| 22:50:16 | 46:50:16 |
| NULL | NULL |
| 19:57:33 | 43:57:33 |
+----------+------------+
6 rows in set (0.00 sec)
Compare with OpenSearch and Postgres:
opensearchsql> SELECT CAST(time1 AS time) AS `time`, DATE_ADD(CAST(time1 AS time), INTERVAL 24 HOUR) AS `time + 24h` FROM calcs LIMIT 6;
fetched rows / total rows = 6/6
+----------+--------------+
| time | time + 24h |
|----------+--------------|
| 19:36:22 | 19:36:22 |
| 02:05:25 | 02:05:25 |
| 09:33:31 | 09:33:31 |
| 22:50:16 | 22:50:16 |
| null | null |
| 19:57:33 | 19:57:33 |
+----------+--------------+
postgres=# select time1, time1 + INTERVAL '5 HOURS' AS "time + 5h", time1 + INTERVAL '24 HOURS' AS "time + 24h" FROM calcs LIMIT 6;
time1 | time + 5h | time + 24h
----------+-----------+------------
19:36:22 | 00:36:22 | 19:36:22
02:05:25 | 07:05:25 | 02:05:25
09:33:31 | 14:33:31 | 09:33:31
22:50:16 | 03:50:16 | 22:50:16
| |
19:57:33 | 00:57:33 | 19:57:33
(6 rows)