Repository: tobymao/sqlglot
Pull request: feat(mysql): Transpile TimestampTrunc
Fixes #3366

MySQL cannot generate `exp.TimestampTrunc` using an existing function; However, one can simulate it with a combination of date add & diff ([source](https://stackoverflow.com/a/32955740))

Docs for `DATE_TRUNC`
-----------
- [Postgres](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC)
- [Presto](https://prestodb.io/docs/current/functions/datetime.html#date_trunc) 
- [Snowflake](https://docs.snowflake.com/en/sql-reference/functions/date_trunc)
- [Databricks / Spark](https://docs.databricks.com/en/sql/language-manual/functions/date_trunc.html)

Problem statement:
Timestamp trunc method issue from postgres to MySQL
I was trying to convert an SQL query from Postgres to MySQL. Check the following code snippet

```
import sqlglot
sqlglot.transpile("SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40') FROM dual", read="postgres", write="mysql") 
```

This returns

`["SELECT TIMESTAMP_TRUNC(CAST('2001-02-16 20:38:40' AS DATETIME), HOUR) FROM dual"]`

But MySQL doesn't have TIMESTAMP_TRUNC method. Please look into this.

Hints from issue discussion:
I have a similar issue

```
import sqlglot

print(sqlglot.transpile("SELECT mt.mode_was, AVG(EXTRACT(epoch FROM (mt.cross_time::TIMESTAMP - mt.second_cross_time::TIMESTAMP))) AS average_time FROM main_table mt GROUP BY mt.mode_was ORDER BY average_time DESC LIMIT 1", read="postgres", write="mysql")[0])
```

this returns
`SELECT mt.mode_was, AVG(EXTRACT(epoch FROM (CAST(mt.cross_time AS DATETIME) - CAST(mt.second_cross_time AS DATETIME)))) AS average_time FROM main_table AS mt GROUP BY mt.mode_was ORDER BY CASE WHEN average_time IS NULL THEN 1 ELSE 0 END DESC, average_time DESC LIMIT 1`
What's the issue @FaizelK?
@georgesittas 
epoch is not a thing in mysql, i wanted to convert that query to mysql, but it still kept epoch. when running this my mysql here are the results because  MySQL does not support the EXTRACT(epoch FROM :

`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'epoch FROM (CAST(mt.cross_time AS DATETIME) - CAST(mt.second_cross_time AS DATET' at line 1`

Hi @FaizelK, can you please open a separate issue describing the faulty behavior? Please include a minimum reproducible example, what you would ideally want to transpile to as well as any docs validating that.

Thanks!

Commit message: feat(mysql): Transpile TimestampTrunc

Patch to review:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 03576d29e5..fd92243559 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -867,3 +867,29 @@ def chr_sql(self, expression: exp.Chr) -> str:
             charset = expression.args.get("charset")
             using = f" USING {self.sql(charset)}" if charset else ""
             return f"CHAR({this}{using})"
+
+        def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
+            this = self.sql(expression, "this")
+            unit = expression.args.get("unit")
+            unix_timestart = "'1900-01-01 00:00:00'"
+
+            mysql_unit = unit
+            if isinstance(unit, exp.Var):
+                # Match abbrevations, most coming from Snowflake
+                unit = unit.this.upper()
+                if unit.startswith("Y"):
+                    mysql_unit = "YEAR"
+                elif unit == "MM" or unit.startswith("MON"):
+                    mysql_unit = "MONTH"
+                elif unit.startswith("W"):
+                    mysql_unit = "WEEK"
+                elif unit.startswith("D"):
+                    mysql_unit = "DAY"
+                elif unit.startswith("MIN"):
+                    mysql_unit = "MINUTE"
+                elif unit.startswith("SEC"):
+                    mysql_unit = "SECOND"
+                elif unit.startswith("MS"):
+                    mysql_unit = "MILLISECOND"
+
+            return f"DATE_ADD({unix_timestart}, interval TIMESTAMPDIFF({mysql_unit}, {unix_timestart}, {this}) {mysql_unit})"
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index e8af5c644e..48d254955d 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1109,3 +1109,20 @@ def test_safe_div(self):
                 "tsql": "CAST(a AS FLOAT) / NULLIF(b, 0)",
             },
         )
+
+    def test_timestamp_trunc(self):
+        for dialect in ("postgres", "snowflake", "duckdb"):
+            for unit in (
+                "MILLISECOND",
+                "SECOND",
+                "DAY",
+                "MONTH",
+                "YEAR",
+            ):
+                with self.subTest(f"MySQL -> {dialect} Timestamp Trunc with unit {unit}: "):
+                    self.validate_all(
+                        f"DATE_ADD('1900-01-01 00:00:00', interval TIMESTAMPDIFF({unit}, '1900-01-01 00:00:00', CAST('2001-02-16 20:38:40' AS DATETIME)) {unit})",
+                        read={
+                            dialect: f"DATE_TRUNC({unit}, TIMESTAMP '2001-02-16 20:38:40')",
+                        },
+                    )


Code review comments:
diff_hunk: @@ -867,3 +867,29 @@ def chr_sql(self, expression: exp.Chr) -> str:
             charset = expression.args.get("charset")
             using = f" USING {self.sql(charset)}" if charset else ""
             return f"CHAR({this}{using})"
+
+        def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
+            this = self.sql(expression, "this")
+            unit = expression.args.get("unit")
+            unix_timestart = "'1900-01-01 00:00:00'"
+
+            mysql_unit = unit
+            if isinstance(unit, exp.Var):
+                # Match abbrevations, most coming from Snowflake
path: sqlglot/dialects/mysql.py
text: @user1:
This shouldn't be needed, abbreviated units are [canonicalized](https://github.com/tobymao/sqlglot/blob/e82a30b6563547daea0bb087e1b6b5bf3b0532d3/sqlglot/dialects/snowflake.py#L144) at parse time for Snowflake. If for some reason they weren't in what you've tested, then we should fix that instead.

diff_hunk: @@ -867,3 +867,29 @@ def chr_sql(self, expression: exp.Chr) -> str:
             charset = expression.args.get("charset")
             using = f" USING {self.sql(charset)}" if charset else ""
             return f"CHAR({this}{using})"
+
+        def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
+            this = self.sql(expression, "this")
+            unit = expression.args.get("unit")
+            unix_timestart = "'1900-01-01 00:00:00'"
+
+            mysql_unit = unit
+            if isinstance(unit, exp.Var):
+                # Match abbrevations, most coming from Snowflake
+                unit = unit.this.upper()
+                if unit.startswith("Y"):
+                    mysql_unit = "YEAR"
+                elif unit == "MM" or unit.startswith("MON"):
+                    mysql_unit = "MONTH"
+                elif unit.startswith("W"):
+                    mysql_unit = "WEEK"
+                elif unit.startswith("D"):
+                    mysql_unit = "DAY"
+                elif unit.startswith("MIN"):
+                    mysql_unit = "MINUTE"
+                elif unit.startswith("SEC"):
+                    mysql_unit = "SECOND"
+                elif unit.startswith("MS"):
+                    mysql_unit = "MILLISECOND"
+
+            return f"DATE_ADD({unix_timestart}, interval TIMESTAMPDIFF({mysql_unit}, {unix_timestart}, {this}) {mysql_unit})"
path: sqlglot/dialects/mysql.py
text: @user1:
Let's generate this programmatically by leveraging `exp` builders and `self.func`

diff_hunk: @@ -867,3 +867,29 @@ def chr_sql(self, expression: exp.Chr) -> str:
             charset = expression.args.get("charset")
             using = f" USING {self.sql(charset)}" if charset else ""
             return f"CHAR({this}{using})"
+
+        def timestamptrunc_sql(self, expression: exp.TimestampTrunc) -> str:
+            this = self.sql(expression, "this")
+            unit = expression.args.get("unit")
+            unix_timestart = "'1900-01-01 00:00:00'"
path: sqlglot/dialects/mysql.py
text: @user1:
```suggestion
            unix_timestart = exp.Literal.string('1900-01-01 00:00:00')
```

@user1:
We should comment that this value was arbitrarily picked as an "old-enough date" (AFAICT), so as to avoid getting back a negative number from the `TIMESTAMPDIFF`.

Let's also rename this to something better while we're at it - `unix_timestart` is a little confusing given that this isn't 1970-01-01. I'd probably do `start_ts` & add this comment to clear any confusion.