Converting a duration to an integer

Help
1

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?

Thanks a lot in advance.

2

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)
```
3

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
	#task 
WHERE
	Done = false AND
	BlockedByTask = NULL 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

4

Does your table show the correct values if you add them to your query? Are you querying files, or tasks?

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

5

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.

6

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
	#task 
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
	BlockedByTask = NULL 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
7

FLATTEN! Thanks @holroy!

8

That worked, thanks a lot @holroy

9

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