CODEWITHSUNDEEP
mysqlgrailsgroovy
vrghost
vrghost

Reputation: 1224

How to store duration in groovy with mysql

Setting up an application which does some reporting on call logs using groovy. I want to use mysql is the background and upload the data to the database externally (i.e. a script that runs every midnight which loads todays calls) I have gotten the connection between mysql and groovy (somewhat) working, it clearly validates the database even if I cant extract or store any data in it.

However, one of the values in the external log is a duration, normally in the format "00:05:42" (%H:%m:%s), how ever, I am having some issues how to store it in mysql in a way that groovy is happy with. Tried date first, but that gives me the issue that it will expect a calendar date as part of it. Then I started trying to use TimeDuration from grovy.time.*, but this seems to store things as a tinyblob. So, is there a Time version that works in mysql and groovy likes (tried a number of different ones, and while mysql seems happy enough, groovy don't quite seem to know what to do about it.

The domain at the moment looks like this:

import groovy.time.*
import java.sql.Time
import java.sql.Timestamp



class Calls {
    Time CallTime
    TimeDuration Length
    int Line
    String SourceNr
    String Direction
    String DialNr
    String Phone
    String User
    static constraints = {
    }
    static mapping = {
        version false
    }
//      id generator: 'hilo',
//      params: [table: 'hi_value', column: 'next_value', max_lo: 100]
        //id composite: ['DateTime', 'SourceNr','DialNr']

}

Upvotes: 0

Views: 250

Answers (1)

Tobia
Tobia

Reputation: 18811

If you cannot find a duration type that can parse the format you describe and that is stored correctly in MySQL, I suggest using a simple long value in seconds and doing the conversion to/from string in the getter and setter.

(This is just some quick code, do some proper tests on boundary cases before using it, plus maybe some input validation.)

class Calls {
    long length

    /** Parse a string with format hhh:mm:ss into a value in seconds. */
    public void setLength(String s) {
        length = s.split(':').collect(Integer.&parseInt).inject {
            a, b -> a * 60 + b
        }
    }

    /** Format a number in seconds into a hhh:mm:ss string. */
    public String getLength() {
        def a = [length]
        for (i in [1, 2]) {
            a << (long)(a[-1] / 60)
            a[-2] %= 60
        }
        String.format("%02d:%02d:%02d", *a.reverse())
    }
}

Upvotes: 1

Related Questions