Repository: tobymao/sqlglot

Issue:
unit generated in oracle sql is incorrect when input is "Q"
```
In [6]: import sqlglot as sg, sqlglot.expressions as sge

In [7]: sg.func("trunc", sg.to_identifier("x"), sge.convert("Q")).sql("oracle")
Out[7]: "TRUNC(x, 'Q')"

In [8]: sg.func("trunc", sg.to_identifier("x"), sge.convert("Q"), dialect="oracle").sql("oracle")
Out[8]: "TRUNC(x, 'QUARTER')"
```

`ROUND`/`TRUNC` docs showing units: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html#GUID-8E10AB76-21DA-490F-A389-023B648DDEF8

Discussion:
VaggelisD: I agree, as discussed in Slack the time units are not handled very well currently (e.g code duplication between `exp.TimeUnit` and `exp.DateTrunc`, unable to override per dialect etc).

However, I entertained the idea of refactoring and it seems like a sizable task; We'd have to move part of that code to `Dialect` and rework `exp.TimeUnit` to be normalized during construction per dialect, which I'm assuming would have to go through builders. This will disrupt most of the unit-based Expression parsing today afaict, right?

It might be worth exploring asap if more edge cases like these creep up.

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 982907d0..1025a075 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -121,7 +121,9 @@ class Oracle(Dialect):
             "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
             "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
             "TRUNC": lambda args: exp.DateTrunc(
-                unit=seq_get(args, 1) or exp.Literal.string("DD"), this=seq_get(args, 0)
+                unit=seq_get(args, 1) or exp.Literal.string("DD"),
+                this=seq_get(args, 0),
+                unabbreviate=False,
             ),
         }
 
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 0924fd53..540d5331 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -1087,3 +1087,12 @@ class Snowflake(Dialect):
             return self.func(
                 f"{safe_prefix}TO_TIMESTAMP", expression.this, self.format_time(expression)
             )
+
+        def timestampsub_sql(self, expression: exp.TimestampSub):
+            return self.sql(
+                exp.TimestampAdd(
+                    this=expression.this,
+                    expression=expression.expression * -1,
+                    unit=expression.unit,
+                )
+            )
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 9a9073a4..794f6d12 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5503,11 +5503,17 @@ class DateTrunc(Func):
     arg_types = {"unit": True, "this": True, "zone": False}
 
     def __init__(self, **args):
+        # Across most dialects it's safe to unabbreviate the unit (e.g. 'Q' -> 'QUARTER') except Oracle
+        # https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html
+        unabbreviate = args.pop("unabbreviate", True)
+
         unit = args.get("unit")
         if isinstance(unit, TimeUnit.VAR_LIKE):
-            args["unit"] = Literal.string(
-                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
-            )
+            unit_name = unit.name.upper()
+            if unabbreviate and unit_name in TimeUnit.UNABBREVIATED_UNIT_NAME:
+                unit_name = TimeUnit.UNABBREVIATED_UNIT_NAME[unit_name]
+
+            args["unit"] = Literal.string(unit_name)
         elif isinstance(unit, Week):
             unit.set("this", Literal.string(unit.this.name.upper()))
 


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index cf38f95e..96c5a4e4 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -697,6 +697,14 @@ LANGUAGE js AS
             write={
                 "bigquery": "SELECT TIMESTAMP_SUB(CAST('2008-12-25 15:30:00+00' AS TIMESTAMP), INTERVAL '10' MINUTE)",
                 "mysql": "SELECT DATE_SUB(TIMESTAMP('2008-12-25 15:30:00+00'), INTERVAL '10' MINUTE)",
+                "snowflake": "SELECT TIMESTAMPADD(MINUTE, '10' * -1, CAST('2008-12-25 15:30:00+00' AS TIMESTAMPTZ))",
+            },
+        )
+        self.validate_all(
+            'SELECT TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL col MINUTE)',
+            write={
+                "bigquery": "SELECT TIMESTAMP_SUB(CAST('2008-12-25 15:30:00+00' AS TIMESTAMP), INTERVAL col MINUTE)",
+                "snowflake": "SELECT TIMESTAMPADD(MINUTE, col * -1, CAST('2008-12-25 15:30:00+00' AS TIMESTAMPTZ))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 36ce5d02..0784810a 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -119,13 +119,6 @@ class TestOracle(Validator):
                 "tsql": UnsupportedError,
             },
         )
-        self.validate_all(
-            "TRUNC(SYSDATE, 'YEAR')",
-            write={
-                "clickhouse": "DATE_TRUNC('YEAR', CURRENT_TIMESTAMP())",
-                "oracle": "TRUNC(SYSDATE, 'YEAR')",
-            },
-        )
         self.validate_all(
             "SELECT * FROM test WHERE MOD(col1, 4) = 3",
             read={
@@ -632,3 +625,20 @@ WHERE
         self.validate_identity("GRANT UPDATE, TRIGGER ON TABLE t TO anita, zhi")
         self.validate_identity("GRANT EXECUTE ON PROCEDURE p TO george")
         self.validate_identity("GRANT USAGE ON SEQUENCE order_id TO sales_role")
+
+    def test_datetrunc(self):
+        self.validate_all(
+            "TRUNC(SYSDATE, 'YEAR')",
+            write={
+                "clickhouse": "DATE_TRUNC('YEAR', CURRENT_TIMESTAMP())",
+                "oracle": "TRUNC(SYSDATE, 'YEAR')",
+            },
+        )
+
+        # Make sure units are not normalized e.g 'Q' -> 'QUARTER' and 'W' -> 'WEEK'
+        # https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html
+        for unit in (
+            "'Q'",
+            "'W'",
+        ):
+            self.validate_identity(f"TRUNC(x, {unit})")