DateDiff inside MySQL DataFlow

user056607

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?

GrantSmith

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)

user056607

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

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