[Discussion] Do we need to support more than 24 hours in `TIME`?

[Yury-Fridlyand]

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 TIME is 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)

[MaxKsyunz]

What is the use case for this? In particular,

1. How will such data be created?
2. How will it be stored?
3. How will it be used?

java.sql.Time and JDBC only work with 24-hour time. So, expanding OpenSearch SQL Time will affect JDBC driver and likely other interfaces. In other words, it's an expensive change -- what are we getting for it?

In MySQL JDBC driver, TIME > 24 hours is an error case and the recommended solution is to get such fields as strings and handle them on the client. See this comment.