Cross-dialect datetime addition

  • 25 February 2018
  • 4 replies

I’m trying to write a derived-table query that will be valid in both MySQL and Snowflake. Mostly I’ve got it but I’m getting stuck on date_add, specifically on yielding the datetime that’s exactly 24 hours before a column’s datetime. The two dialects have different functions for that. So I thought of converting the column to seconds-since-the-epoch, subtracting 246060, and converting back, but it turns out the two dialects have different functions for that, too. Regex may be doable, but before I go down that path I’m seeking other suggestions, please.

4 replies

Userlevel 1

With the right permissions, you should be able to create user-defined functions in one or both databases so that you have compatibility across dialects. For example, you could define a Snowflake UDF named date_add that takes the same inputs as the MySQL function.

Thanks, @ted.conbeer!

Userlevel 3

@menashe I was also able to test this on both MySQL and Snowflake, that uses string function to perform the operation:

select CONCAT(CONCAT(SUBSTRING('2017-06-15 12:20:13', 1, 5), 
(SUBSTRING('2017-06-15 12:20', 6, 2) -1)),
SUBSTRING('2017-06-15 12:20:13', 8, 12) )

this gives us: 2017-5-15 12:20:13 on both databases.

{% if _dialect._name == 'mysql' %} -- mysql_function
{% elsif _dialect._name == 'snowflake' %} -- snowflake_function
{% endif %}