DOMO - Timestamp

rishi_patel301
rishi_patel301 Member
edited November 2021 in Magic ETL

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 Coach
    edited November 2021

    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')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • rishi_patel301
    rishi_patel301 Member

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

    Can I set that?

  • GrantSmith
    GrantSmith Coach

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories