5

I have hibernate mapping as follow:

<property formula="(Timediff(ifnull(sttime,now()),sstime))" insert="false" name="duration" update="false" />
where sstime is of type Timestamp

here this formula returns some value in this format "838:59:59" from my data.

I got the exception when i am trying to load this value in 'Duration' field, which has 'java.sql.Time' type, it gives me this exception.

"Generic JDBC exception - Bad format for Time '838:59:59'"
Haresh Godhani
  • 261
  • 1
  • 4
  • 13
  • Similar question : http://stackoverflow.com/questions/13733148/surpassing-mysqls-time-value-limit-of-8385959 – Shashi Mar 12 '13 at 11:32

3 Answers3

3

That's indeed not a valid time. You should not use a java.sql.Time to represent a duration, especially if this duration is potential longer tha 24 hours, since a Time represents the time portion of a date. Use a custom type instead, or simply a string that you'll parse yourself.

Perception
  • 79,279
  • 19
  • 185
  • 195
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
1

From the source

http://www.microshell.com/database/mysql/getting-around-mysql-timediff-maximum-value-of-8385959/

Use TIMESTAMPDIFF instead of TIMEDIFF in formulae

Shashi
  • 12,487
  • 17
  • 65
  • 111
1

Following the MySQL documentation you should use TIMESTAMPDIFF() and UNIX_TIMESTAMP():

The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers.

Query may look something like this, but is dependent from the result data type that you are using:

SELECT FROM_UNIXTIME(TIMESTAMPDIFF(SECOND,sstime,NOW())) FROM time_table;

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(sstime)) FROM time_table;

Demo

Rostyslav
  • 428
  • 3
  • 13
  • I think may works for me... To try this i need to write following line: and hibernate make 'SECOND' as 'this_.SECOND'.so how can i solve this problem..? – Haresh Godhani Mar 12 '13 at 11:55