Repository: tobymao/sqlglot

Issue:
Wrong Convert for 'DATE_FORMAT' between mysql and clickhouse.(TIME_TO_STR)
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- Check if the issue still exists on main

**Fully reproducible code snippet**
```python
import sqlglot
if __name__ == '__main__':
    print(sqlglot.transpile("SELECT DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');", read="mysql", write="clickhouse")[0])
```
SQL OUTPUT:
```sql
SELECT TIME_TO_STR(now(), '%Y-%m-%d %H:%M:%S')
```

**Official Documentation**
[LINK](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#formatdatetime)


Discussion:
VaggelisD: Will amend this PR to also include `timezone`, only for Clickhouse.

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index a7b48956..d1c95b72 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -474,11 +474,31 @@ class BigQuery(Dialect):
                 if rest and this:
                     this = exp.Dot.build([this, *rest])  # type: ignore
 
-                table = exp.Table(this=this, db=db, catalog=catalog)
+                table = exp.Table(
+                    this=this, db=db, catalog=catalog, pivots=table.args.get("pivots")
+                )
                 table.meta["quoted_table"] = True
 
             return table
 
+        def _parse_column(self) -> t.Optional[exp.Expression]:
+            column = super()._parse_column()
+            if isinstance(column, exp.Column):
+                parts = column.parts
+                if any("." in p.name for p in parts):
+                    catalog, db, table, this, *rest = (
+                        exp.to_identifier(p, quoted=True)
+                        for p in split_num_words(".".join(p.name for p in parts), ".", 4)
+                    )
+
+                    if rest and this:
+                        this = exp.Dot.build([this, *rest])  # type: ignore
+
+                    column = exp.Column(this=this, table=table, db=db, catalog=catalog)
+                    column.meta["quoted_column"] = True
+
+            return column
+
         @t.overload
         def _parse_json_object(self, agg: Lit[False]) -> exp.JSONObject: ...
 
@@ -781,6 +801,16 @@ class BigQuery(Dialect):
             "within",
         }
 
+        def column_parts(self, expression: exp.Column) -> str:
+            if expression.meta.get("quoted_column"):
+                # If a column reference is of the form `dataset.table`.name, we need
+                # to preserve the quoted table path, otherwise the reference breaks
+                table_parts = ".".join(p.name for p in expression.parts[:-1])
+                table_path = self.sql(exp.Identifier(this=table_parts, quoted=True))
+                return f"{table_path}.{self.sql(expression, 'this')}"
+
+            return super().column_parts(expression)
+
         def table_parts(self, expression: exp.Table) -> str:
             # Depending on the context, `x.y` may not resolve to the same data source as `x`.`y`, so
             # we need to make sure the correct quoting is used in each case.
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 34ee5296..21bafeb8 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -6,6 +6,7 @@ from sqlglot import exp, generator, parser, tokens, transforms
 from sqlglot.dialects.dialect import (
     Dialect,
     arg_max_or_min_no_count,
+    build_formatted_time,
     date_delta_sql,
     inline_array_sql,
     json_extract_segments,
@@ -19,6 +20,16 @@ from sqlglot.helper import is_int, seq_get
 from sqlglot.tokens import Token, TokenType
 
 
+def _build_date_format(args: t.List) -> exp.TimeToStr:
+    expr = build_formatted_time(exp.TimeToStr, "clickhouse")(args)
+
+    timezone = seq_get(args, 2)
+    if timezone:
+        expr.set("timezone", timezone)
+
+    return expr
+
+
 def _lower_func(sql: str) -> str:
     index = sql.index("(")
     return sql[:index].lower() + sql[index:]
@@ -124,6 +135,8 @@ class ClickHouse(Dialect):
             "DATEDIFF": lambda args: exp.DateDiff(
                 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
             ),
+            "DATE_FORMAT": _build_date_format,
+            "FORMATDATETIME": _build_date_format,
             "JSONEXTRACTSTRING": build_json_extract_path(
                 exp.JSONExtractScalar, zero_based_indexing=False
             ),
@@ -653,6 +666,9 @@ class ClickHouse(Dialect):
             exp.StrPosition: lambda self, e: self.func(
                 "position", e.this, e.args.get("substr"), e.args.get("position")
             ),
+            exp.TimeToStr: lambda self, e: self.func(
+                "DATE_FORMAT", e.this, self.format_time(e), e.args.get("timezone")
+            ),
             exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
             exp.Xor: lambda self, e: self.func("xor", e.this, e.expression, *e.expressions),
         }
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index d97807a3..a090a99a 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5684,7 +5684,7 @@ class StddevSamp(AggFunc):
 
 
 class TimeToStr(Func):
-    arg_types = {"this": True, "format": True, "culture": False}
+    arg_types = {"this": True, "format": True, "culture": False, "timezone": False}
 
 
 class TimeToTimeStr(Func):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 23b8d9c6..39183448 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -778,14 +778,8 @@ class Generator(metaclass=_Generator):
         default = "DEFAULT " if expression.args.get("default") else ""
         return f"{default}CHARACTER SET={self.sql(expression, 'this')}"
 
-    def column_sql(self, expression: exp.Column) -> str:
-        join_mark = " (+)" if expression.args.get("join_mark") else ""
-
-        if join_mark and not self.COLUMN_JOIN_MARKS_SUPPORTED:
-            join_mark = ""
-            self.unsupported("Outer join syntax using the (+) operator is not supported.")
-
-        column = ".".join(
+    def column_parts(self, expression: exp.Column) -> str:
+        return ".".join(
             self.sql(part)
             for part in (
                 expression.args.get("catalog"),
@@ -796,7 +790,14 @@ class Generator(metaclass=_Generator):
             if part
         )
 
-        return f"{column}{join_mark}"
+    def column_sql(self, expression: exp.Column) -> str:
+        join_mark = " (+)" if expression.args.get("join_mark") else ""
+
+        if join_mark and not self.COLUMN_JOIN_MARKS_SUPPORTED:
+            join_mark = ""
+            self.unsupported("Outer join syntax using the (+) operator is not supported.")
+
+        return f"{self.column_parts(expression)}{join_mark}"
 
     def columnposition_sql(self, expression: exp.ColumnPosition) -> str:
         this = self.sql(expression, "this")


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 300d492a..973a60c9 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -57,6 +57,9 @@ class TestBigQuery(Validator):
         self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
         self.assertEqual(exp.to_table("`x`.`y`", dialect="bigquery").sql("bigquery"), "`x`.`y`")
 
+        column = self.validate_identity("SELECT `db.t`.`c` FROM `db.t`").selects[0]
+        self.assertEqual(len(column.parts), 3)
+
         select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`")
         self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF")
 
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index df3caaf6..ef4fce6d 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -409,6 +409,19 @@ class TestClickhouse(Validator):
         self.validate_identity("SELECT FORMAT")
         self.validate_identity("1 AS FORMAT").assert_is(exp.Alias)
 
+        self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d', '%T')")
+        self.validate_all(
+            "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            read={
+                "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')",
+                "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            },
+            write={
+                "clickhouse": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+                "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            },
+        )
+
     def test_cte(self):
         self.validate_identity("WITH 'x' AS foo SELECT foo")
         self.validate_identity("WITH ['c'] AS field_names SELECT field_names")
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index c0b362c4..758b60c5 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -228,6 +228,17 @@ class TestOptimizer(unittest.TestCase):
 
     @patch("sqlglot.generator.logger")
     def test_qualify_columns(self, logger):
+        self.assertEqual(
+            optimizer.qualify.qualify(
+                parse_one(
+                    "SELECT `my_db.my_table`.`my_column` FROM `my_db.my_table`",
+                    read="bigquery",
+                ),
+                dialect="bigquery",
+            ).sql(dialect="bigquery"),
+            "SELECT `my_table`.`my_column` AS `my_column` FROM `my_db.my_table` AS `my_table`",
+        )
+
         self.assertEqual(
             optimizer.qualify_columns.qualify_columns(
                 parse_one(