How to get full duration in base formula like dataview?

[ni554n]

In dataview, date(today) - purchase_date results in 2 years, 9 months, 2 weeks, 1 day whereas, in base, today() - purchase_date results in just 3 years. I want the precise format in base.

[Rofl]

same problem – I would like to see any duration only in a specific format (eg duration(“1d”).format(“HH”): 24h) etc

[dawni]

Don’t know if there are better ways already, but this is a simple way to display that duration in days:

((number(today()) - number(purchase_date)) / 86400000).floor() + " days"

Unfortunately, this method is not robust at all when aiming for a display like “2 years, 9 months, 2 weeks, 1 days”.

no volunteers?

I’m not at all interested in making the formulas, but if someone else wants to… You would could subtract year from year, month from month, and day from day then increment for “negative” remainders and recalculate for the variable length of the time frame (e.g., a month with 28 days versus 31 days).

I’d rather wait for official implementation or a plugin.

[dawni]

I feel so silly for using number() / 86400000 when this is already an option:

(today() - purchase_date).days.floor()

So, in the absence of the feature Rofl mentioned, this long formula does it:

[(today() - purchase_date).years.floor() + " years", (today() - (purchase_date + duration((today() - purchase_date).years.floor() + "y"))).months.floor() + " months", (today() - (purchase_date + duration((today() - purchase_date).years.floor() + "y") + duration((today() - (purchase_date + duration((today() - purchase_date).years.floor() + "y"))).months.floor() + "M"))).days.floor() + " days"].filter(value.slice(0,1) > 0).join(", ")

I believe now is where the youths would add a crying emoji.

[ivavictoria]

Hi! Just wanted to drop a thanks for sharing this, as a way to cast my vote that “it would be really nice if this were built-in.”

With Bases being released, I’ve decided to finally migrate away from Notion for good, and this date issue resolves one of my final dependencies.

I have a database that lists various events, with formulas that generate columns showing how old I was when the event happened, and how long ago the event was from today. Notion has a really convenient dateBetween() function, and it would be pretty cool to see something like that in Obsidian.

Thanks to what you shared, I have what I need! I’ll drop my version in as an additional example, in case it’s somehow useful to someone else.

Time between an arbitrary hard-coded day (such as y2k in this example – NOT telling you my birthday lol) and a date in a Base property (in this case “Event Date”):

[
  (note["Event Date"] - date("2000-01-01")).years.floor() + " years",

  (note["Event Date"] - (
      date("2000-01-01") 
      + duration((note["Event Date"] - date("2000-01-01")).years.floor() + "y")
  )).months.floor() + " months",

  (note["Event Date"] - (
      date("2000-01-01") 
      + duration((note["Event Date"] - date("2000-01-01")).years.floor() + "y") 
      + duration((
          note["Event Date"] - (
            date("2000-01-01") 
            + duration((note["Event Date"] - date("2000-01-01")).years.floor() + "y")
          )
        ).months.floor() + "M")
  )).days.floor() + " days"
]
.filter(value.slice(0,1) > 0)
.join(", ")

You can remove the extra spacing and whatnot, but it also seems to work fine when pasted in like this.

[dawni]

Nice. Also, your quip about not revealing your age made me realize the code should include some data validation for time! Here’s a working template.

Time between two dates

Conditions

• earlierDate and laterDate are date-without-time values
• earlierDate comes before laterDate
  • includes a check just in case

Code

if(laterDate < earlierDate, "time travel!", if(laterDate == earlierDate, "same day", 
[

(laterDate - earlierDate).years.floor() + " years", 

(laterDate - (earlierDate + duration((laterDate - earlierDate).years.floor() + "y"))).months.floor() + " months", 

(laterDate - (earlierDate + duration((laterDate - earlierDate).years.floor() + "y") + duration((laterDate - (earlierDate + duration((laterDate - earlierDate).years.floor() + "y"))).months.floor() + "M"))).days.floor() + " days"

].filter(value.slice(0,1) > 0).join(", ")
))

Adaptations

datetime values

• when the values include time

Append .date() as needed.

timeSince (age)

• when laterDate is set to today()
• e.g., for calculating time since a past event

Replace the first pseudo-line with:

if(today() < earlierDate, "you traveled to the future to do this", if(today() == earlierDate, "today", 

timeTill (countdown)

• when earlierDate is set to today()
• e.g., for calculating time until a future event

Replace the first pseudo-line with:

if(laterDate < today(), "this event has passed", if(laterDate == today(), "today", 

[anon5277159]

Hello.

I have used your code to work out ended - started properties in one if my bases. Thank you very much.

Some of the ended properties are blank at the moment. To get a simple count of days, I use:

(if(ended, ended, today()) - started).days.round() + " days"

The above is code that you helped me with. Thanks, again.

I have tried to adapt your year-month-day code to fall back to using today if ended is blank, but I cannot get it to work. I have got more confused because I don’t know what "time travel!" is doing. I changed it to ttt in my version of the code just to see if anything happened. Nothing did—the code still worked, although it stopped working if I deleted ttt completely.

if(ended < started, "ttt", if(ended == started, "same day", 
[
(ended - started).years.floor() + " years", 
(ended - (started + duration((ended - started).years.floor() + "y"))).months.floor() + " months", 
(ended - (started + duration((ended - started).years.floor() + "y") + duration((ended - (started + duration((ended - started).years.floor() + "y"))).months.floor() + "M"))).days.floor() + " days"
].filter(value.slice(0,1) > 0).join(", ")
))

Thanks, again, for any tips.

[anon5277159]

I also tried adapting one of your earlier suggestions in this thread. It validates but gives wrong durations. If I ever work out the answer, I’ll share it here.

[(if(ended, ended, today()) - started).years.floor() + " years", (today() - (started + duration((today() - started).years.floor() + "y"))).months.floor() + " months", (today() - (started + duration((today() - started).years.floor() + "y") + duration((today() - (started + duration((today() - started).years.floor() + "y"))).months.floor() + "M"))).days.floor() + " days"].filter(value.slice(0,1) > 0).join(", ")

[dawni]

You could insert the if statement throughout the formula. But the easiest way might be to make a separate property formula for it:

if(ended, ended, today())

Let’s say you name that property endedCalc. Now you can use formula.endedCalc in other formulas in the same base.

So in your duration formula, replace all ended with formula.endedCalc (or whatever you name it).

The time-travel thing checks for out-of-order dates. To see it in action, set one of your started values to a date that comes after its ended value, and you’ll see “time travel!” but more importantly, the code will stop trying to subtract the dates so as to not return misleading duration info.

[anon5277159]

Thank you.

Think I understand what to do. Will give this a try when I am back on my Mac. And I also want to try time travelling.

[dawni]

And just to explain, the method you tried here is possible but is way more a hassle to write than using a separate property. For example, the formula you showed missed instances where you needed to replace ended with your if statement, and it missed applying the math to those instances.

It ought to have started with:

[(if(ended, ended - started, today() - started).years.floor() + " years"

and carried on from there. Ugh, totally not worth the effort.

[anon5277159]

In the nicest possible way, you are frying my brain. Again, will try to understand this more when I am back on a Mac. Thanks for the time and the clarity of your explanations. They do help a lot. I am just starting from a very low level of knowledge.

[dawni]

Ah, I over-described it. But you got this!

Ignore the extras that don’t serve you well!

[anon5277159]

Without wishing to fawn over you à la the unctuous Mr Collins (from Pride and Prejudice), you definitely haven’t over-described anything. I have read most posts on the forum over the last few years, and I often been struck by how you write so clearly and concisely, let alone knowledgeably and helpfully.

And in this case, your formula.endedCalc solution (1) works perfectly, and (2) is something I would never have figured out on my own. Thank you, yet again.

Very grateful for your help and guidance.

[dawni]

Wow, that was really nice, thank you.

But…

It is happy for you that you possess the talent of flattering with delicacy. May I ask whether these pleasing attentions proceed from the impulse of the moment, or are the result of previous study?

Kidding! I genuinely appreciate it.

[anon5277159]

Chapeau, Mx Austen.

As someone who cannot lie—the truth is always etched into my face; I have a poor memory for anything that isn’t factual; and my brain just doesn’t work that way—I have never been able to ape Mr Collins, not even to the smallest degree. And I have never wanted to.

They say that everyone has a novel in them. I don’t know who you are or what you do, but I imagine if you do ever find the novel that’s in you—if you haven’t already found it—that it will be a sharp and percipient read. You strike me as a natural, free-flowing writer. You’re the only person on the forum I have thought that about, including the people who have said they are writers.