How to create Morning and Afternoon fields in data load based on time of day?

[mikegrattan]

My Time field is formatted as follows:

time(Date#(textBetween(observation_time,'Last Updated on ', 'P'),'MMM DD YYYY, hh:mm tt')) as Time

I would like to create another field that can have the values of Morning or Afternoon, depending on the Time field.  If the time is between 04:00:00 AM and 11:59:59 AM, then it is morning.  If it is between 12:00:00 PM and 5:00:00 PM, then it is evening.

Help is appreciated very much.  Thank you.

[Anonymous]

if(Wildmatch(YourTimeField,'*AM*'),'Morning','Afternoon') as "Morning | Afternoon"

 (example with Now())

[mikegrattan]

Thank you for your reply.  That looks like it would be great for generic AM/PM filtering but we wanted specific time frames for Morning vs Afternoon.  Is there another approach that would provide that functionality?

[Anonymous · Accepted solution]

You can build this as a master Item or in the data load:

= if( Time(Frac(observation_time))> '4:00:00 AM' and Time(Frac(observation_time))<'11:59:59 AM', 'Morning'
,if( Time(Frac(observation_time))> '12:00:00 PM' and Time(Frac(observation_time))< '5:00:00 PM', 'Evening','N/A'))

[mikegrattan]

That works; I was not aware of the Frac function so I learned something new today.  Thanks!