Repository: tobymao/sqlglot

Issue:
Bug in DATE_BIN translation from postgres to duckdb
**Fully reproducible code snippet**
```python
from sqlglot import parse_one
query = "SELECT DATE_BIN('30 days, 0:00:00', events.timestamp, (SELECT MIN(events.timestamp) AS min_1 FROM events)) AS bin FROM table"
print(parse_one(query, dialect='postgres').sql(dialect='duckdb'))
``` 
The output is: 
SELECT DATE_BIN('30 days, 0:00:00', events.timestamp, (SELECT MIN(events.timestamp) AS min_1 FROM events)) AS bin FROM "table"

while the expected output should be:
SELECT TIME_BUCKET('2592000000 milliseconds', events.timestamp, (SELECT MIN(events.timestamp) AS min_1 FROM events)) AS bin FROM "table"
as duckdb doesn't know to handle intervals in this structure, but it does know to handle milliseconds intervals

**Official Documentation**
1. duckdb docs - https://duckdb.org/docs/sql/functions/timestamp.html#time_bucketbucket_width-timestamp-origin
2. postgres docs - https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-BIN

Hints from issue comments:
@tobymao: @dor-bernstein these are simply unimplemented, feel free to make a PR for these translation issues, but we won't tackle them all due to prioritization from our side.

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 8abe946a03..31c0903faf 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -408,6 +408,7 @@ class Parser(parser.Parser):
             "STRUCT_PACK": exp.Struct.from_arg_list,
             "STR_SPLIT": exp.Split.from_arg_list,
             "STR_SPLIT_REGEX": exp.RegexpSplit.from_arg_list,
+            "TIME_BUCKET": exp.DateBin.from_arg_list,
             "TO_TIMESTAMP": exp.UnixToTime.from_arg_list,
             "UNNEST": exp.Explode.from_arg_list,
             "XOR": binary_from_function(exp.BitwiseXor),
@@ -664,6 +665,7 @@ class Generator(generator.Generator):
             ),
             exp.JSONObjectAgg: rename_func("JSON_GROUP_OBJECT"),
             exp.JSONBObjectAgg: rename_func("JSON_GROUP_OBJECT"),
+            exp.DateBin: rename_func("TIME_BUCKET"),
         }
 
         SUPPORTED_JSON_PATH_PARTS = {
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index b30dc6b84e..6a6723aa24 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -967,14 +967,22 @@ def scope_resolution(self, rhs: str, scope_name: str) -> str:
             return f"{scope_name}::{rhs}"
 
         def select_sql(self, expression: exp.Select) -> str:
-            if expression.args.get("offset"):
+            limit = expression.args.get("limit")
+            offset = expression.args.get("offset")
+
+            if isinstance(limit, exp.Fetch) and not offset:
+                # Dialects like Oracle can FETCH directly from a row set but
+                # T-SQL requires an ORDER BY + OFFSET clause in order to FETCH
+                offset = exp.Offset(expression=exp.Literal.number(0))
+                expression.set("offset", offset)
+
+            if offset:
                 if not expression.args.get("order"):
                     # ORDER BY is required in order to use OFFSET in a query, so we use
                     # a noop order by, since we don't really care about the order.
                     # See: https://www.microsoftpressstore.com/articles/article.aspx?p=2314819
                     expression.order_by(exp.select(exp.null()).subquery(), copy=False)
 
-                limit = expression.args.get("limit")
                 if isinstance(limit, exp.Limit):
                     # TOP and OFFSET can't be combined, we need use FETCH instead of TOP
                     # we replace here because otherwise TOP would be generated in select_sql
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index be4f1b374c..f2b6898068 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5640,6 +5640,10 @@ class DateAdd(Func, IntervalOp):
     arg_types = {"this": True, "expression": True, "unit": False}
 
 
+class DateBin(Func, IntervalOp):
+    arg_types = {"this": True, "expression": True, "unit": False, "zone": False}
+
+
 class DateSub(Func, IntervalOp):
     arg_types = {"this": True, "expression": True, "unit": False}
 


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index ac748cdd72..982b133235 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -300,6 +300,14 @@ def test_oracle(self):
             "SELECT /*+ ORDERED */ * /* test */ FROM tbl",
         )
 
+        self.validate_all(
+            "SELECT * FROM t FETCH FIRST 10 ROWS ONLY",
+            write={
+                "oracle": "SELECT * FROM t FETCH FIRST 10 ROWS ONLY",
+                "tsql": "SELECT * FROM t ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY",
+            },
+        )
+
     def test_join_marker(self):
         self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y")
 
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 8ca32ff354..195df32348 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -857,6 +857,14 @@ def test_postgres(self):
             },
         )
 
+        self.validate_all(
+            "SELECT DATE_BIN('30 days', timestamp_col, (SELECT MIN(TIMESTAMP) from table)) FROM table",
+            write={
+                "postgres": "SELECT DATE_BIN('30 days', timestamp_col, (SELECT MIN(TIMESTAMP) FROM table)) FROM table",
+                "duckdb": 'SELECT TIME_BUCKET(\'30 days\', timestamp_col, (SELECT MIN(TIMESTAMP) FROM "table")) FROM "table"',
+            },
+        )
+
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier
         self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(