Date Calcs in Magic ETL

Options
NewsomSolutions
NewsomSolutions Domo Employee
in Magic ETL

Does anyone have any hints or suggestions as to running time calcs, like DateDiff (minute), for Magic ETL.  I have some joins that I'm running in RedShift and I'd like to see about getting them into Magic ETL but with the lack of a function similar to DateDiff for time periods less than a day (I know there are date cals in Magic ETL) and being able to join on things other than equals (sometimes you may want to join two tables where a value is between two other values)...it makes it tough if not impossible to use Magic ETL.  Right now I'm thinking about just putting a filter after a full join on the tables joining what I can with equals and then filtering things down with the "between" option there.  

 

If anyone else has an suggestions that I haven't thought about I'd love to see them.

 

Thanks

Best Answer

  • Gimli
    Gimli Contributor
    Answer ✓

    Hi!

    In the past, I have used the "replace" tile to strip out only the time of a data/time column. I have then converted the time to milliseconds, performed time difference calculations then converted it back to readable time. 

    This was not very easy. I would recommend sticking with your current SQL queries. 

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • Gimli
    Gimli Contributor
    Answer ✓

    Hi!

    In the past, I have used the "replace" tile to strip out only the time of a data/time column. I have then converted the time to milliseconds, performed time difference calculations then converted it back to readable time. 

    This was not very easy. I would recommend sticking with your current SQL queries. 

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • NewsomSolutions
    NewsomSolutions Domo Employee

    @ilikenno thanks for that.  I didn't even think about stripping it out and doing it that way but that is an option.  I think you're right though, it may be best to just keep in SQL for sake of simplicity of troubleshooting.  Thank you.

Categories