DateDiff inside MySQL DataFlow

user056607
user056607 Member
edited March 2023 in Datasets

Hi, I'm trying to use the datediff() function inside a MySQL dataflow. However, the program is not recognizing the datediff() function and it isn't working properly. Can someone help? Is this a bug?

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user056607 

     

    When subtracting two dates in MySQL it should return a single number representing the day difference. So you can try something like:

     

    `Cancellation Date` - `Publish Date` <= 90

     

     

    Alternatively you can utilize UNIX_TIMESTAMP to calculate the number of seconds since 1970-01-01 and then calculate the second difference.

     

    UNIX_TIMESTAMP(`Cancellation Date`) - UNIX_TIMESTAMP(`Publish Date`) <= (90 * 24 * 60 * 60)

    (90 days, 24 hours in a day, 60 minutes in an hour, 60 seconds in a minute)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user056607
    user056607 Member

    I tired both of your methods. Utilizing the subtracting of dates directly, something I tried prior to posting this question, presents wrong and very weird results. For example, Oct 15 2019 - Sep 26 2019 = 89. 89? Where is Domo getting 89? Those dates are clearly less than 30 days apart. The Unix method sounded promising as it is something I haven't tried yet. However, Domo MySQL DataFlow doesn't recognize the function just the same as DATEDIFF() which is the whole reason I'm writing this question. Help?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Just to clear up any confusion ... it's not 'Domo MySql' it's MySQL 5.6.28-76.1-56  -- just run SELECT VERSION()

     

    Your ETL is being processed in a MySQL database engine, it is not 'Domo flavored' in any way.  Except that the platform is automatically converting your SELECT statement into a SELECT INTO TABLE.

     

    It's unclear why, Oct 15 2019 - Sep 26 2019 gets converted into 89. 89 without seeing the actual SQL you put in.  The fact that it's a decimal is extra suspect.  Make sure that your date columns are actually date and not Text that is getting an implicit conversion applied.

     

    Unix method is guaranteed to work, have a google for Unix Timestamp MySQL. -- it exists same for DateDiff --https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

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

Categories