How to define time period?

[simon1524]

For example, the "SUBMIT_DATE" is split by date and time. Then define some period of time as a value(A/B/C). Can this be achieved?

(SUBMIT_DATE="2021-03-09 14:30:48.0")  ==> Split to "2021-03-09" and "14:30:48.0"

0:00:00 - 8:00:00 = A

8:00:00 - 16:00:00 = B

16:00:00 - 0:00:00 = C

[yuanliu]

Can you explain what "Then define some period of time as a value(A/B/C)" means?  You have already defined A, B, and C in your description.  What is missing?

To split SUBMIT_DATE is simple,

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval part1 = mvindex(SUBMIT_DATE, 0), part2 = mvindex(SUBMIT_DATE, 1)

[simon1524]

I want to split （SUBMIT_DATE） into “date” and “time”.
Then define a value for 8 hours period, such as below.
0:00:00 - 8:00:00 = A

8:00:00 - 16:00:00 = B

16:00:00 - 0:00:00 = C

[yuanliu]

Still unclear what "a value for 8 hours period" is supposed to mean.  In number of seconds?  An time (epoch) value starting from that "date" with offset of 8 hours? Some sort of string (as you have already defined)?  There can be millions of interpretations of this phrase.

[simon1524]

According to the data on the system, it should be accurate to the second.

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval part1 = mvindex(SUBMIT_DATE, 0), part2 = mvindex(SUBMIT_DATE, 1)

According to the function you give

"00:00:00 > part2 >= 08:00:00" as A

"08:00:00 > part2 >= 16:00:00" as B

"16:00:00 > part2 >= 00:00:00" as C

I wonder how to define it...

[yuanliu · Accepted solution]

If I understand this correctly, you want to define a function that outputs "A", "B", or "C" according to which 8-hour interval the time of day in SUBMIT_DATE falls into.  In other words, you are looking for a case function.

| eval SUBMIT_DATE = split(SUBMIT_DATE, " ")
| eval date = mvindex(SUBMITE_DATE, 0), time_of_day = strptime(mvindex(SUBMIT_DATE, 1), "%H:%M:%S")
| eval shift = case(time_of_day < 28800, "A", 28800 <= time_of_day AND time_of_day < 57600, "B", true(), "C")

Does this help? Note that time_of_day is in number of seconds from 00:00:00 whereas date is still in string format.  If you want to convert that to epoch, use strptime again.

Also note that is perhaps not the most elegant to split SUBMIT_DATE into substrings before converting to numeric.  But the formula should work.

[simon1524]

Thank you!

Got the first step done by your query.

| eval SUBMITED_DATE = split(SUBMITED_DATE, " ")
| eval date = mvindex(SUBMITED_DATE, 0), time_of_day = strptime(mvindex(SUBMITED_DATE, 1), "%H:%M:%S")
| eval shift = case(time_of_day < 1664258400, "A", 1664258400 <= time_of_day AND time_of_day < 1664287200, "B", true(), "C")

The next step is to count the number of each month

| timechart span = 1mon count(eval(shift = "A")) as first_shift, count(eval(shift = "B")) as second_shift, count(eval(shift = "C")) as third_shift
| tail 12
| sort _time

As you see my query, I want to count every shift in every month of the last year. But there is something wrong with above query...

[yuanliu]

Without an illustration, it is unclear what "count every shift in every month" really means.  If you want to know how many events per month are in each shift, i.e., count by shift, you can use

| timechart span=1mon count by shift
| tail 12

 Is this what you require?

[simon1524]

Thank you very much! The result is showing now~