DOMO - Timestamp

rishi_patel301

Hello,

I have data coming in from SQL with a timestamp from previous day. So the date in the table will be current date -1. I want to separate this data into 3 different tables,

Table 1 - 7 AM to 3 PM

Table 2 - 3 Pm - 11 PM

Table 3 - 11 PM to 7 AM.

How can I add a formula in the filter for tile to filter the rows by that?

More specifically I am struggling with getting the time stamps to compare.

Below is what I was trying to do. But I get a "Operator + cannot be applied to types date and timestamp"

`Time_From_Column` BETWEEN

ADDTIME(CURRENT_DATE()-1 ,STR_TO_DATE('00:06:66','%h:%i:%s'))

AND

ADDTIME(CURRENT_DATE()-1, STR_TO_DATE('00:07:05','%h:%i:%s'))

Thanks.

GrantSmith

Hi @rishi_patel301

Instead of using STR_TO_DATE just use your time string you're wanting to add to your current date

`Time_From_Column` BETWEEN

ADDTIME(CURRENT_DATE() , '00:06:66')

AND

ADDTIME(CURRENT_DATE(), '00:07:05')

rishi_patel301

But I want the time portion of time stamp to be 00:00:00

Can I set that?

GrantSmith

Another alternative is to use the HOUR function on your timestamp and put it in the corresponding bucket:

CASE WHEN HOUR(`Time_From_Column`) >= 7 AND HOUR(`Time_From_Column`) < 15 THEN '7 AM to 3 PM'
WHEN HOUR(`Time_From_Column`) >= 15 AND HOUR(`Time_From_Column`) < 23 THEN '3 PM to 11 PM'
ELSE '11 PM to 7 AM'
END