Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
mahnoor1279
Contributor III

How to create a time range by looking the timestamp

I want to create a  shifts by looking the timestamp  i.e (morning 9am to 3pm , evening(4pm to 11pm), night (11pm to 9am)

i am also attaching the file kindly please help me out from this .

 

Thanks in advance

5 Replies
arulsettu
Master III

Maybe like this. you can change the shifts as per need

 

load*,
if(hour >='10 am' and hour <= '12 pm','morning 10am to 12pm',
If(hour >='02 pm' and hour <= '04 pm','evening 2pm to 4pm',
If(hour >='04 pm' and hour <= '05 pm','evening 4pm to 5pm'
))) as bucket;
Load *,
Time(Timestamp#(time,'hh:mm:ss tt'),'hh tt') as hour
Inline [
time
4:06:37 pm
5:52:22 pm
10:00:14 am
10:03:50 am
4:54:34 pm
4:54:44 pm
4:37:26 pm
3:39:02 pm
2:00:09 pm
3:44:42 pm
10:12:50 am
12:02:13 pm
12:03:16 pm
12:08:39 pm
];

mahnoor1279
Contributor III
Author

This doesn't show any result 

arulsettu
Master III

Did you replaced the columns in above script? share the sample app

mahnoor1279
Contributor III
Author

Time(Timestamp#(START_DATETIME,'hh:mm:ss tt'),'hh tt') as hour,

 

 

if(hour >='9 AM' and hour <= '15 PM','morning 9am to 3pm',
 If(hour>='15 PM' and hour <= '23 PM','evening 3pm to 11pm',
If(hour >='23 PM' and hour<= '9 AM','evening 11pm to 9am'))) as bucket,

 

 

does this seems ok ?

arulsettu
Master III

Can you share sample data?