Order by id only count las 2 values and get the day that they have between with MYSQL in GRAILS

Question

The data that is going to order by id, within the information I get from the query, I need to calulate the days between days from this query.

result = DEVsTACK.executeQuery("
select maintenance 
     , advisor
     , advisorId
     , DATEDIFF(managementDate, appointmentDate) AS date
     , Date
     , carKm
     , observations 
  FROM DEVsTACKAS 
 order 
    by id DESC
", [[offset:0, max:2])

Example:

    one                    two                   three
2017-04-21 12:36:10 and 2017-04-22 09:36:10 and 2017-04-26 09:36:10 

one to two should get an answer of a day and from two to three the answer should be four days. How can I get values from a users id and see how many days are between the days I have showed from the column date?

Answer 1

i suggest you use createCriteria follow this link.

you can use maxResult.

this is my example createCriteria

def c = PerintahKerja.createCriteria()
        def results = c.list(params) {
            if(params.noPerintah) {
                ilike("noPerintah", "%${params.noPerintah}%")
            }
            if(params.from){
                from = new Date().parse('dd/MM/yyyy hh:mm:ss', params.from+" 00:00:00")
            }
            else{
                from = removeTime(from)
            }
            if(params.to){
                to = new Date().parse('dd/MM/yyyy hh:mm:ss', params.to+" 00:00:00")
            }else{
                to = removeTime(to)
            }

            ge("tanggalPerintah", from)
            le("tanggalPerintah", to)

            eq("deleteFlag", "N")
            eq("cif", cif)
        }

FYI, you can get range of date in gsp.

for example.

|managementDate      | appointmentDate      | countDay|
|-------------------------------------------|---------|
|2017-04-21 12:36:10 | 2017-04-22 12:36:10  | 1       |

after you throw this list to gsp.

you can do something like this to get range between two dates.

<%
    use(groovy.time.TimeCategory) {
        def duration = date1 - date2
        print "Days: ${duration.days}, Hours: ${duration.hours}, etc."
    }
%>

Answer 2

You can do this within HQL. So as you are currently doing 'executeQuery' aka HQL query.

First point You can run specific mysql commands within HQL which will give you the difference between the two dates by default so no additional work around using timeCategory or any additional messing with results from db.

Mysql :

FROM_UNIXTIME(UNIX_TIMESTAMP(coalesce(rq.finished,rq.start)) -  UNIX_TIMESTAMP(rq.start))

This will then fail whilst you test your query on internal db So instead:

CONVERT(concat(hour(rq.finished)*60*60+minute(rq.finished)*60+second(rq.finished)) ,INTEGER) -
                CONVERT(concat(hour(rq.start)*60*60+minute(rq.start)*60+second(rq.start)) ,INTEGER)
             as internalDuration,

Change rq.finished and rq.start to your date fields managementDate, appointmentDate

if you want to produce days hours months ago etc:

results=results?.each { instance ->
            TimeDuration duration=getDifference(instance?.startDate,instance?.finishDate)
            instance.duration=formatDuration(duration)

Which uses getDifference

private TimeDuration getDifference(Date startDate,Date endDate) {
        if (startDate && endDate) {
            //return TimeCategory.minus(endDate, startDate)
            TimeDuration customPeriod = use(TimeCategory ) {
                customDuration( endDate - startDate )
            }
            return customPeriod
        }
    }
    TimeDuration customDuration( TimeDuration tc ) {
        new TimeDuration(  tc.days , tc.hours, tc.minutes, tc.seconds, ((tc.seconds > 0||tc.minutes > 0||tc.hours > 0) ? 0 : tc.millis))
    }