DOMO - Timestamp

rishi_patel301
edited November 17 in Dataflows

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.

Answers

  • GrantSmith
    GrantSmith Indiana 🔴
    edited November 17

    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')
    
    
    
  • But I want the time portion of time stamp to be 00:00:00

    Can I set that?

  • GrantSmith
    GrantSmith Indiana 🔴

    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