Repository: tobymao/sqlglot

Issue:
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.


Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 03576d29..76e87d66 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -668,6 +668,7 @@ class MySQL(Dialect):
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
+            exp.ArrayAgg: rename_func("GROUP_CONCAT"),
             exp.CurrentDate: no_paren_current_date_sql,
             exp.DateDiff: _remove_ts_or_ds_to_date(
                 lambda self, e: self.func("DATEDIFF", e.this, e.expression), ("this", "expression")
@@ -782,6 +783,13 @@ class MySQL(Dialect):
             exp.DataType.Type.TIMESTAMPLTZ,
         }
 
+        def extract_sql(self, expression: exp.Extract) -> str:
+            unit = expression.name
+            if unit and unit.lower() == "epoch":
+                return self.func("UNIX_TIMESTAMP", expression.expression)
+
+            return super().extract_sql(expression)
+
         def datatype_sql(self, expression: exp.DataType) -> str:
             # https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
             result = super().datatype_sql(expression)
@@ -867,3 +875,16 @@ class MySQL(Dialect):
             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:
+            unit = expression.args.get("unit")
+
+            # Pick an old-enough date to avoid negative timestamp diffs
+            start_ts = "'0000-01-01 00:00:00'"
+
+            # Source: https://stackoverflow.com/a/32955740
+            timestamp_diff = build_date_delta(exp.TimestampDiff)([unit, start_ts, expression.this])
+            interval = exp.Interval(this=timestamp_diff, unit=unit)
+            dateadd = build_date_delta_with_interval(exp.DateAdd)([start_ts, interval])
+
+            return self.sql(dateadd)


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index e8af5c64..d6adea53 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -598,6 +598,19 @@ class TestMySQL(Validator):
         )
 
     def test_mysql(self):
+        self.validate_all(
+            "SELECT department, GROUP_CONCAT(name) AS employee_names FROM data GROUP BY department",
+            read={
+                "postgres": "SELECT department, array_agg(name) AS employee_names FROM data GROUP BY department",
+            },
+        )
+        self.validate_all(
+            "SELECT UNIX_TIMESTAMP(CAST('2024-04-29 12:00:00' AS DATETIME))",
+            read={
+                "mysql": "SELECT UNIX_TIMESTAMP(CAST('2024-04-29 12:00:00' AS DATETIME))",
+                "postgres": "SELECT EXTRACT(epoch FROM TIMESTAMP '2024-04-29 12:00:00')",
+            },
+        )
         self.validate_all(
             "SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]')",
             read={
@@ -1109,3 +1122,23 @@ COMMENT='客户账户表'"""
                 "tsql": "CAST(a AS FLOAT) / NULLIF(b, 0)",
             },
         )
+
+    def test_timestamp_trunc(self):
+        for dialect in ("postgres", "snowflake", "duckdb", "spark", "databricks"):
+            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('0000-01-01 00:00:00', INTERVAL (TIMESTAMPDIFF({unit}, '0000-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')",
+                        },
+                        write={
+                            "mysql": f"DATE_ADD('0000-01-01 00:00:00', INTERVAL (TIMESTAMPDIFF({unit}, '0000-01-01 00:00:00', CAST('2001-02-16 20:38:40' AS DATETIME))) {unit})",
+                        },
+                    )