1

We have a data source: Query Results, with two dates per row: CreatedDate and ResolutionDate.

I would like to be able to calculate the number of days between the two dates, I.E.

Cast ((
            JulianDay(ResolutionDate) - JulianDay(CreatedDate)
        ) As Integer)

This doesn’t work though, it doesn’t support Cast or JulianDay. Even though these are supported in SQLite…

1 Like
2

You should be able to use the datediff function to do what you want. For instance:

datediff(week, CreatedDate, ResolutionDate) as weeksToResolve

3

When trying to do that, I get an error:

Error running query: no such function: datediff

4

I found a solution. The way Redash deals with dates in Query Results is a bit weird it seems. I had to convert the date to a substr first, and then julianday worked:

julianday(substr(resolutiondate, 1, 10)) - julianday(substr(created, 1, 10))

For Jira, substr 1-10 is the YYYY-MM-DD part.