# Converting a duration to an integer

### The problem

Hello, I want to be able to convert a duration to an integer number. Here’s what I have in my query:

(striptime(date(now)) - striptime(file.ctime)) AS TimeVariable

It’ll return a duration without the time component, which is expected, like:

• 3 weeks and 2 days
• 2 weeks and 5 days

However, I’d like to convert it to days, an integer number, so it can be used in a math calculation; so, the result above would be shown instead simply as:

• 23
• 19

If I try to, for example, get TimeVariable and sum or subtract with other variable with is a number, it’ll fail.

Please, what can be done in this case? Any ideas?

For something similar I’ve used `dateformat(date(...), "x")` to get milliseconds since the epoch, subtracted one from the other, and then divided by the appropriate number of milliseconds, seconds, minutes, and hours to get a day count. There could certainly be a more concise way within `dataview`’s functions, but that was my approach.

Edit: Here’s the code I was thinking of. It’s part of a bigger chunk so I don’t know if it’ll function as-is, but the point was to get the number of days until someone’s birthday.

`````````dataview
round(product([number(dateformat(date(dateformat(date(birthday), dateformat(date(today), "yyyy") + "-MM-dd")), "x")) - number(dateformat(date(today), "x")), 1/1000, 1/60, 1/60, 1/24]), 1)
```
``````

Thanks for the quick reply, geoffb.
I’ve actually managed to (sort of) get what I want by doing this:

(striptime(date(now)) - striptime(file.ctime)).days AS TimeVariable

so TimeVariable now shows an integer number, which was the goal.

I’ve even tested to multiply it to a negative number and it works with the code below:

(striptime(date(now)) - striptime(file.ctime)).days * (-0.4) AS TimeVariable

TimeVariable will shown a negative decimal number, which is totally OK and my intention

However, now for some reason, if I use TimeVariable in a calculation with another field, I’m having issues. The full query looks like this:

``````TABLE
Status,
uid,
choice(
(
Impact = NULL OR
Urgency = NULL
),
0,
Impact + Urgency - 1
) AS Priority

,

(striptime(date(now)) - striptime(file.ctime)).days * (-0.4) AS TimeVariable

,

(Priority + TimeVariable) AS Result
FROM
WHERE
Done = false AND
HideUntil < date(now)
SORT
Priority
LIMIT
5
``````

So, I have the columns Status, uid, Priority, TimeVariable and Result. However, Result should be a simple sum of a decimal value with an integer and it does not work; instead of showing the result, it simply shows an hifen (‘-’)

PS: Impact and Urgency variables are in the frontmatter and always has integer values from 1 to 3

Any other ideas? Thanks again

Could you show parts of a note which should be used by the query, so that we could see the syntax you’ve used?

Yes, I can reproduce. I tested with the `typeof()` function and when used with variables, both returned `null`. I had to replace `(Priority + TimeVariable) AS Result` with `((Impact + Urgency - 1) + ((striptime(date(now)) - striptime(file.ctime)).days * (-0.4))) AS Result`, which seemed to work. There may be a better way in dataview.

I didn’t see it at once, but you’re only doing the calculations in the “header” row, and renaming the output. You’re not doing them at a level available for other calculations. Try this one, and see if it works better:

`````````dataview
TABLE
Status,
uid,
Priority,
TimeVariable,
Result
FROM
FLATTEN choice(
( Impact = NULL OR Urgency = NULL	),
0,
Impact + Urgency - 1
) AS Priority
FLATTEN (striptime(date(now)) - striptime(file.ctime)).days * (-0.4) AS TimeVariable
FLATTEN (Priority + TimeVariable) as Result
WHERE
Done = false AND
HideUntil < date(now)
SORT
Priority
LIMIT
5
```
``````

Here I use `FLATTEN` to do computed fields, which makes them available for other calculations, and even as elements of `WHERE` clauses, and so on.

2 Likes

FLATTEN! Thanks @holroy!

That worked, thanks a lot @holroy

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.