Find the difference of date format in years days hours min

[AL3Z]

Hi,

How we can find the difference of these two date difference in year days hour min 

from	till
11/28/2023 03:38 PM	11/28/2024 04:08 PM

[gcusello]

Hi @AL3Z ,

let me understand: you want the difference between two dates in format days, hours, minutes and seconds, is it correct?

if this is your requirement, you could try something like this:

<your_search>
| eval duration=tostring(strptime(till,"%m/%d/%Y %I:%M %p")-strptime(from,"%m/%d/%Y %I:%M %p"),"duration")
| table from till duration

Ciao.

Giuseppe

[AL3Z]

@gcusello ,

duration is showing  like 366+00:30:00.000000 how we can change it 
eg: 0 years 181 days 23 hours 30 min

[AL3Z]

@gcusello ,

Tried this Search not giving the desire result

| eval from_epoch = strptime(from, "%m/%d/%Y %I:%M %p")
| eval till_epoch = strptime(till, "%m/%d/%Y %I:%M %p")
| eval diff_seconds = till_epoch - from_epoch
| eval diff_years = floor(diff_seconds / (365.25*24*60*60))
| eval remaining_seconds = diff_seconds - (diff_years * 365.25*24*60*60)
| eval diff_days = floor(remaining_seconds / (24*60*60))
| eval remaining_seconds = remaining_seconds - (diff_days * 24*60*60)
| eval diff_hours = floor(remaining_seconds / (60*60))
| eval diff_minutes = floor((remaining_seconds - (diff_hours * 60*60)) / 60)
| eval duration = diff_years . " year" . if(diff_years != 1, "s", "") . " " . diff_days . " day" . if(diff_days != 1, "s", "") . " " . diff_hours . " hour" . if(diff_hours != 1, "s", "") . " " . diff_minutes . " min"

output:

11/28/2023 05:10 PM 11/28/2024 05:40 PM 1 year 0 days 18 hours 30 min

[PickleRick]

You're trying to cut corners here. Depending on your definition of "year", the issue is much more complicated than you think. If you take  year deifned as 31557600 seconds (365.25 days), it will give you a "weird" result - a year after a midnight Jan 1st 2023 will be 6:00AM Jan 1st 2024.

If you mean a year as 365 days, you'll get your Jan 1st 2024 + 1 year being Dec 31st. 2024. Again - not what some people would expect.

If you mean a year as in "the year number in the date changed", it's getting even more complicated.

Date manipulation is always a huge pain in the lower part of your back.

That's probably why the "duration" formatting only goes to days, because that's pretty straightforward and unambigous.

Of course going from "366+00:30:00.00000" to "366 days and 30 minutes" is relatively easy to do - just throw in some regexes and replace one parts of text with another.

But extracting that year part... that's also gonna be easy as soon as you know what you want. Which - as I wrote earlier - might not be that easy. And you have to account for border cases (leap years, let's assume for now that we don't have leap seconds :D)

[yuanliu]

Maybe check your "desired result?"  Among year month days hour min, day, hour, minute have definitive definitions.  Year as duration is dubious; month is practically meaningless.

[AL3Z]

@yuanliu ,

above dates are just for reference we need to make a generic to work for all  the dates like above !

[yuanliu]

What I am saying is that @gcusello's solution is the most generic because day, hour, minute can be algorithmically determined with no ambiguity.  These are time units, whereas year and month are calendar concepts.  You cannot have month as duration because there is no meaningful definition of a month. (Week, on the other hand, can be calculated.)  Using year is not very accurate.  Yes, you can use 365.25 but that is still an approximation.  If you insist on using year (but forego month), you can continue to use your formula, or this simplified one

| eval duration=strptime(till,"%m/%d/%Y %I:%M %p")-strptime(from,"%m/%d/%Y %I:%M %p")
| eval years = round(duration / 86400 / 365.25)
| eval duration = years . " year(s) " . tostring(duration - years * 365.25 * 86400, "duration")

The only way to define a "month" with time interval (duration) is if you choose a reference calendar.  For example, use the Unix epoch zero calendar, namely 1970.

| eval duration=strptime(till,"%m/%d/%Y %I:%M %p")-strptime(from,"%m/%d/%Y %I:%M %p")
| eval years = tonumber(strftime(duration, "%Y")) - 1970
| eval months = tonumber(strftime(duration, "%m")) - 1
| eval ymdt = years . " year(s) " . months . " month(s) " . strftime(duration, "%d") . " day(s) " . strftime(duration, "%T")

Of course, you can also change reference calendar to a different year.  But honestly I cannot see any useful application of this format.

Here is an emulation:

| makeresults format=csv data="from,	till
11/28/2023 03:38 PM,	11/28/2024 04:08 PM"
``` data emulation above ```

It will give the following

duration	from	months	till	years	ymdt
31624200.000000	11/28/2023 03:38 PM	0	11/28/2024 04:08 PM	1	1 year(s) 0 month(s) 01 day(s) 16:30:00