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?
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)
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?
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
