As I guessed, you are looking for average time of day (seconds since midnight).
It looks like you are assuming that everyone checks in and out within the same day (a reasonable assumption but there will certainly be exceptions) and you are trying to calculate an average check-out time (what time do people generally leave the office and head for home?).
You had a problem where you were using the same BY clause in both stats commands which is definitely wrong.
The second one should be one of either BY nick OR BY day_of_swipe OR nothing at all. I went with BY nick with the assumption that that is the door/office and you need the final answer broken out by that.
I think what you need is this:
... | eval date_month = strftime(_time, "%m") | eval date_mday = strftime(_time, "%d") | eval date_wday = strftime(_time, "%A")
| eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday )
| eval Month=strftime(_time,"%b")
| eval secondsSinceMidnight_TOD=_time - relative_time(_time, "@d")
| stats first(JobTitle) AS JobTitle
first(UserName) AS UserName
count(dvc_name) AS swipes_per_day
first(Manager_Name) AS Manager_Name
first(department) AS department
min(secondsSinceMidnight_TOD) AS earliest_TOD
last(secondsSinceMidnight_TOD) AS latest_TOD
values(secondsSinceMidnight_TOD) AS TOD_values
first(Month) AS Month BY nick day_of_swipe
| stats min(earliest_TOD) AS earliest_earliest_TOD
max(latest_TOD) AS latest_latest_TOD
values(TOD_values) AS combined_TOD_values
mean(latest_TOD) AS mean_latest_TOD BY nick
| convert timeformat="%H:%M:%S" ctime(earliest_earliest_TOD) AS earliest_earliest_TOD
| convert timeformat="%H:%M:%S" ctime(latest_latest_TOD) AS latest_latest_TOD
| convert timeformat="%H:%M:%S" ctime(combined_TOD_values) AS combined_TOD_values
| convert timeformat="%H:%M:%S" ctime(mean_latest_TOD) AS mean_latest_TOD
P.S. NEVER use the "free" (but wrong) date_* fields for anything other than a quick sanity check; if you need them, calculate them yourself.
... View more