Calculate difference between 2 time fields using strptime

[MedralaG]

As an example, I am getting weather data where in each json even I have the sunrise and sunset time for that day.
The field values only give the time , ex:
sunrise= 7:03 AM
sunset = 4:45 PM

I would like to calculate the difference between them to calculate how much daylight we are getting each day.
I first use the strptime command to convert the sunrise and sunset values into a epoch time timestamp. This puts the hours and minutes in nicely but it assumes the day, month and year. The issue that I've noticed that sometimes it will assume that the day is today or sometimes that its yesterday. This causes a problem when I subtract one timestamp from another to calculate the daylight time. At moments I get results of around 10 hours (which makes sense) and then it drops to -14 hours.

If I run the same search again it will bring up different results with some days still calculating correctly and then other days being assumed incorrectly and calculating a negative daylight time.

I use the following SPL to first convert lower case "am" and "pm" to upper case so that the %p variable can correctly identify if its an afternoon time or morning time, then pull out the into a timestamp format with the strptime command, then substract one from another, which gives me the time difference in seconds, so I devide it by 60/60 to get the difference in hours and put that in a timecahrt

| eval sunrise_time=replace(sunrise,"am","AM"),sunset_time=replace(sunset,"pm","PM")
| eval sunrise_time=strptime(sunrise_time,"%I:%M %p"),sunset_time=strptime(sunset_time,"%I:%M %p") 
| eval daylight_time=(sunset_time-sunrise_time)/60/60 | timechart span=1d  eval(round(avg(daylight_time),2)) by city

The strange thing is that for the sunrise time it will always assume that its today but with the sunset time it will sometimes assume its yesterday and give me a negative daylight_time.

[elliotproebstel · Accepted solution]

I just tested this locally, and it looks like strptime is interpreting a time variable as "today" if the time is earlier than 3 hours into the future, but assuming it was "yesterday" if it's more than 3 hours into the future. You could probably get around this by appending a string containing the current date to the timestamp before converting it.

[elliotproebstel]

Also, you will get bugs if you are trying to interpret "16:45 PM" with "%I:%M %p", because 16 is not a valid value for %I. That would need to either be "4:45 PM" or it could just be "16:45" with a format string of "%H:%M"

[MedralaG]

Sorry, that was actually a typo, I've made up a bad example.
the sunset time always appears in 4:45 PM (12 hours format)

[elliotproebstel]

Sure thing. 🙂 In that case, your strptime will almost certainly function as expected if you append a static date to the timestamp. Any date will do, as long as you apply the same one to sunset and sunrise. So you could just choose a day like "1/1/2000" and always append that to your timestamp and then interpret the whole thing with strptime.

[MedralaG]

So how would I append the sunset and sunrise hour to a sixed timestamp without having the 3 hour issue affect it.
the moment I do a strptime on the sunset it will be affected by the issue.

[elliotproebstel]

Assuming you have an event containing (at least) two fields: sunrise_time="7:03 AM" and sunset_time="4:45 PM", it would work like this:

your base search
| eval sunrise_time=sunrise_time." 2000-01-01", sunset_time=sunset_time." 2000-01-01"
| eval sunrise_time=strptime(sunrise_time, "%I:%M %p %F"), sunset_time=strptime(sunset_time, "%I:%M %p %F")
| eval daylight_time=(sunset_time-sunrise_time)/60/60 
| timechart span=1d  eval(round(avg(daylight_time),2)) by city

[MedralaG]

Ah yes, that will do it, using the "." .
Awesome, thank you very much.

[elliotproebstel]

Glad to help! By the way, when you "award points", you actually subtract from your own karma count. It's a generous thing to do, but usually unnecessary. You can also give people points just by accepting the answer or upvoting what they've posted (both of which you did!), and neither of those actions will subtract from your point total. So you went above and beyond, and thank you.

[MedralaG]

Why would you want to keep your karma points?

[elliotproebstel]

As you accumulate karma points, you are able to do more things on the site. Not all users care about that, which is fine. But, for example, it can be helpful to be able to post links or attach files to a post, and those are things you can only do if you have 50 or 60 points, respectively. Here's the full table.