[CONJ-823] SEC_TO_TIME wraps time at 24 hours Created: 2020-08-27  Updated: 2021-04-22  Resolved: 2021-04-22

Status: Closed
Project: MariaDB Connector/J
Component/s: JDBC 4.2 compatibility
Affects Version/s: 2.6.2
Fix Version/s: 3.0.0

Type: Bug Priority: Major
Reporter: Riccardo Paolo Bestetti Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Linux, MariaDB 10.3, MariaDB 10.5


 Description   

SEC_TO_TIME should support converting seconds to the TIME data type, inside the representation limits of the TIME data type.

However, it looks like the JDBC connector wraps the result at 24 hours.

E.g. using the `mariadb` client:

MariaDB [(none)]> SELECT SEC_TO_TIME(257394);
---------------------

SEC_TO_TIME(257394)

---------------------

71:29:54

---------------------

E.g. using the JDBC connector to execute the same query:
23:29:54
(i.e. 71:29:54 minus 2*24h)

It might be interesting to note that the MySQL JDBC connector returns `NULL` for the same query.

 Comments   
Comment by Riccardo Paolo Bestetti [ 2020-08-27 ]

It might be interesting to note that

SELECT @t := SEC_TO_TIME(257394);

returns the correct result both with the MariaDB and the MySQL JDBC drivers.

Comment by Diego Dupin [ 2020-11-17 ]

connectors receive the information, but you'll have the result according to the data type you want :
example : 

    ResultSet rs = statement.executeQuery("SELECT SEC_TO_TIME(257394)");
 
    rs.next();
 
    System.out.println(rs.getString(1)); // return 71:29:54
 
    System.out.println(rs.getTime(1)); // return 23:29:54

as indicated in java.sql.Time, "The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed." So the days part are truncated.

Comment by Riccardo Paolo Bestetti [ 2020-11-17 ]

Interesting. I first noticed the bug while using DBeaver, a universal (JDBC) SQL client, which showed the incorrect result.

I think I might want to report this to them, then.

Generated at Thu Feb 08 03:18:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.