Extracting the time from date and adding in IFELSE statement

Question & Answer
,
1

Hi Team,
Can you help me with this.
I have date in one column in this format: April 24, 2024 12:24pm.

I need to extract the time from this column, and then add it in IFELSE statement, so that I can separate the timings into Night, early and Late shift.

For instance,
if the time is between 4 and 14:45, it need to be in Early Shift.
If the time is between 14:45 and 23:30, it need to be in Early Shift.

However, the only solution I could find to extract the time from date is to convert it to string first(please see below), which makes it difficult for me to add the string in IF ELSE statement.

concat(toString(extract(‘HH’,{YOURS_DATE_FIELD})),‘:’,toString(extract(‘MI’,{YOURS_DATE_FIELD})))

Problem by using the above query: 8:02 am is shown as 8:2, which is hard to compare in IF ELSE statement.

Any workaround for this?

2

Hi @cijod - I believe you need extract the hour first and do the comparison and then have a inner logic to compare with minutes.

By saying this let’s hear from other experts @David_Wong @Biswajit_1993 @ErikG @DylanM @duncan

Regards - Sanjeeb

3

Hi @cijod,

Do you need to convert everything to string? If you just need to compare hours and minutes and don’t actually need to display the time, you can use the extract function and keep everything as integers.

Hour

extract('HH', {timestamp})

Minute

extract('MI', {timestamp})

Shift Type

ifelse(
	Hour >= 4 AND Hour <= 14 AND Minute >= 0 AND Minute < 45, 'Early Shift',
	Hour >= 14 AND Hour <= 23 AND Minute >= 45 AND Minute < 30, 'Night Shift',
	'add other conditions'
)

You probably have to refine it to cover all your scenarios but this should give you an idea. Instead of one single ifelse, you may need a separate ifelse to compare the minutes. Test it and see what you get.

3 Likes
4

Hi David, Thanks for looking into this. I have tried this, but the minutes area is not working correctly. I will try to see how I can manage to include minutes as well in this If else and let you know if I get stuck here.