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.
... View more