Repository: bodo-ai/PyDough

Issue:
Add support for HOUR, MINUTE and SECOND functions in PyDough
Goal: add the following functionalities to PyDough, which should be tested e2e (see `test_pipeline.py` for examples):

- `HOUR(x)` extracts the hour component of a timestamp (like `EXTRACT(HOUR FROM x)` in SQL)
- `MINUTE(x)` extracts the hour component of a timestamp (like `EXTRACT(HOUR FROM x)` in SQL)
- `SECOND(x)` extracts the hour component of a timestamp (like `EXTRACT(HOUR FROM x)` in SQL)

The implementations for year/month/day can be used as reference. The implementations should work in ANSI or in SQLite.

All of these features need to be documented in the [function list documentation](https://github.com/bodo-ai/PyDough/blob/main/documentation/functions.md)

Discussion:

Patch:
diff --git a/demos/notebooks/2_pydough_operations.ipynb b/demos/notebooks/2_pydough_operations.ipynb
index 457533e..84c21eb 100644
--- a/demos/notebooks/2_pydough_operations.ipynb
+++ b/demos/notebooks/2_pydough_operations.ipynb
@@ -232,7 +232,7 @@
     "\n",
     "# Datetime Operations\n",
     "# Note: Since this is based on SQL lite the underlying date is a bit strange.\n",
-    "print(pydough.to_sql(lines(YEAR(ship_date), MONTH(ship_date), DAY(ship_date))))\n",
+    "print(pydough.to_sql(lines(YEAR(ship_date), MONTH(ship_date), DAY(ship_date),HOUR(ship_date),MINUTE(ship_date),SECOND(ship_date))))\n",
     "\n",
     "# Aggregation operations\n",
     "print(pydough.to_sql(TPCH(NDISTINCT(nations.comment), SUM(nations.key))))\n",
diff --git a/documentation/functions.md b/documentation/functions.md
index 3b92169..f24d303 100644
--- a/documentation/functions.md
+++ b/documentation/functions.md
@@ -25,6 +25,9 @@ Below is the list of every function/operator currently supported in PyDough as a
    * [YEAR](#year)
    * [MONTH](#month)
    * [DAY](#day)
+   * [HOUR](#hour)
+   * [MINUTE](#minute)
+   * [SECOND](#second)
 - [Conditional Functions](#conditional-functions)
    * [IFF](#iff)
    * [ISIN](#isin)
@@ -255,6 +258,36 @@ Calling `DAY` on a date/timestamp extracts the day of the month it belongs to:
 Orders(is_first_of_month = DAY(order_date) == 1)
 ```
 
+<!-- TOC --><a name="hour"></a>
+### HOUR
+
+Calling `HOUR` on a date/timestamp extracts the hour it belongs to. The range of output
+is from 0-23:
+
+```py
+Orders(is_12pm = HOUR(order_date) == 12)
+```
+
+<!-- TOC --><a name="minute"></a>
+### MINUTE
+
+Calling `MINUTE` on a date/timestamp extracts the minute. The range of output
+is from 0-59:
+
+```py
+Orders(is_half_hour = MINUTE(order_date) == 30)
+```
+
+<!-- TOC --><a name="second"></a>
+### SECOND
+
+Calling `SECOND` on a date/timestamp extracts the second. The range of output
+is from 0-59:
+
+```py
+Orders(is_lt_30_seconds = SECOND(order_date) < 30)
+```
+
 <!-- TOC --><a name="conditional-functions"></a>
 ## Conditional Functions
 
diff --git a/pydough/pydough_operators/__init__.py b/pydough/pydough_operators/__init__.py
index df9d389..637411b 100644
--- a/pydough/pydough_operators/__init__.py
+++ b/pydough/pydough_operators/__init__.py
@@ -29,6 +29,7 @@ __all__ = [
     "GRT",
     "HAS",
     "HASNOT",
+    "HOUR",
     "IFF",
     "ISIN",
     "JOIN_STRINGS",
@@ -40,6 +41,7 @@ __all__ = [
     "LOWER",
     "MAX",
     "MIN",
+    "MINUTE",
     "MOD",
     "MONOTONIC",
     "MONTH",
@@ -55,6 +57,7 @@ __all__ = [
     "RANKING",
     "ROUND",
     "RequireNumArgs",
+    "SECOND",
     "SLICE",
     "STARTSWITH",
     "SUB",
@@ -87,6 +90,7 @@ from .expression_operators import (
     GRT,
     HAS,
     HASNOT,
+    HOUR,
     IFF,
     ISIN,
     JOIN_STRINGS,
@@ -98,6 +102,7 @@ from .expression_operators import (
     LOWER,
     MAX,
     MIN,
+    MINUTE,
     MOD,
     MONOTONIC,
     MONTH,
@@ -110,6 +115,7 @@ from .expression_operators import (
     PRESENT,
     RANKING,
     ROUND,
+    SECOND,
     SLICE,
     STARTSWITH,
     SUB,
diff --git a/pydough/pydough_operators/expression_operators/README.md b/pydough/pydough_operators/expression_operators/README.md
index 4e0014b..4ba0a11 100644
--- a/pydough/pydough_operators/expression_operators/README.md
+++ b/pydough/pydough_operators/expression_operators/README.md
@@ -81,6 +81,9 @@ These functions must be called on singular data as a function.
 - `YEAR`: returns the year component of a datetime.
 - `MONTH`: returns the month component of a datetime.
 - `DAY`: returns the day component of a datetime.
+- `HOUR`: Returns the hour component of a datetime.
+- `MINUTE`: Returns the minute component of a datetime.
+- `SECOND`: Returns the second component of a datetime.
 
 ##### Conditional Functions
 
diff --git a/pydough/pydough_operators/expression_operators/__init__.py b/pydough/pydough_operators/expression_operators/__init__.py
index f60a687..283b7e5 100644
--- a/pydough/pydough_operators/expression_operators/__init__.py
+++ b/pydough/pydough_operators/expression_operators/__init__.py
@@ -26,6 +26,7 @@ __all__ = [
     "GRT",
     "HAS",
     "HASNOT",
+    "HOUR",
     "IFF",
     "ISIN",
     "JOIN_STRINGS",
@@ -37,6 +38,7 @@ __all__ = [
     "LOWER",
     "MAX",
     "MIN",
+    "MINUTE",
     "MOD",
     "MONOTONIC",
     "MONTH",
@@ -50,6 +52,7 @@ __all__ = [
     "PyDoughExpressionOperator",
     "RANKING",
     "ROUND",
+    "SECOND",
     "SLICE",
     "STARTSWITH",
     "SUB",
@@ -81,6 +84,7 @@ from .registered_expression_operators import (
     GRT,
     HAS,
     HASNOT,
+    HOUR,
     IFF,
     ISIN,
     JOIN_STRINGS,
@@ -92,6 +96,7 @@ from .registered_expression_operators import (
     LOWER,
     MAX,
     MIN,
+    MINUTE,
     MOD,
     MONOTONIC,
     MONTH,
@@ -104,6 +109,7 @@ from .registered_expression_operators import (
     PRESENT,
     RANKING,
     ROUND,
+    SECOND,
     SLICE,
     STARTSWITH,
     SUB,
diff --git a/pydough/pydough_operators/expression_operators/registered_expression_operators.py b/pydough/pydough_operators/expression_operators/registered_expression_operators.py
index 1673a78..b6b4041 100644
--- a/pydough/pydough_operators/expression_operators/registered_expression_operators.py
+++ b/pydough/pydough_operators/expression_operators/registered_expression_operators.py
@@ -21,6 +21,7 @@ __all__ = [
     "GRT",
     "HAS",
     "HASNOT",
+    "HOUR",
     "IFF",
     "ISIN",
     "JOIN_STRINGS",
@@ -32,6 +33,7 @@ __all__ = [
     "LOWER",
     "MAX",
     "MIN",
+    "MINUTE",
     "MOD",
     "MONOTONIC",
     "MONTH",
@@ -44,6 +46,7 @@ __all__ = [
     "PRESENT",
     "RANKING",
     "ROUND",
+    "SECOND",
     "SLICE",
     "STARTSWITH",
     "SUB",
@@ -129,6 +132,15 @@ MONTH = ExpressionFunctionOperator(
 DAY = ExpressionFunctionOperator(
     "DAY", False, RequireNumArgs(1), ConstantType(Int64Type())
 )
+HOUR = ExpressionFunctionOperator(
+    "HOUR", False, RequireNumArgs(1), ConstantType(Int64Type())
+)
+MINUTE = ExpressionFunctionOperator(
+    "MINUTE", False, RequireNumArgs(1), ConstantType(Int64Type())
+)
+SECOND = ExpressionFunctionOperator(
+    "SECOND", False, RequireNumArgs(1), ConstantType(Int64Type())
+)
 SLICE = ExpressionFunctionOperator(
     "SLICE", False, RequireNumArgs(4), SelectArgumentType(0)
 )
diff --git a/pydough/sqlglot/sqlglot_relational_visitor.py b/pydough/sqlglot/sqlglot_relational_visitor.py
index 5f1e17a..6d88e8c 100644
--- a/pydough/sqlglot/sqlglot_relational_visitor.py
+++ b/pydough/sqlglot/sqlglot_relational_visitor.py
@@ -171,6 +171,7 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
         new_columns: list[SQLGlotExpression],
         orig_select: Select,
         deps: set[Identifier],
+        sort: bool = True
     ) -> Select:
         """
         Attempt to merge a new select statement with an existing one.
@@ -184,6 +185,8 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
                 of the old columns in some operator other than the
                 "SELECT" component. For example a filter will need to
                 include the column names of any WHERE conditions.
+            sort (bool): If True, the existing columns in the original SELECT
+                statement get sorted alphabetically by their string representation (repr).
 
         Returns:
             Select: A final select statement that may contain the merged columns.
@@ -191,11 +194,13 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
         new_exprs, old_exprs = self._try_merge_columns(
             new_columns, orig_select.expressions, deps
         )
+        if sort:
+            old_exprs = sorted(old_exprs,key=repr)
         orig_select.set("expressions", old_exprs)
         if new_exprs is None:
             return orig_select
         else:
-            return self._build_subquery(orig_select, new_exprs)
+            return self._build_subquery(orig_select, new_exprs, sort=sort)
 
     def _convert_ordering(
         self, ordering: MutableSequence[ExpressionSortInfo]
@@ -266,6 +271,7 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
         input_expr: Select,
         column_exprs: list[SQLGlotExpression],
         alias: str | None = None,
+        sort: bool = True,
     ) -> Select:
         """
         Generate a subquery select statement with the given
@@ -276,10 +282,14 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
                 another select statement.
             column_exprs (list[SQLGlotExpression]): The columns to select.
             alias (str | None): The alias to give the subquery.
+            sort (bool): If True, the final select statement ordering is based on the
+                sorted string representation of input column expressions.
 
         Returns:
             Select: A select statement representing the subquery.
         """
+        if sort:
+            column_exprs = sorted(column_exprs,key=repr)
         return (
             Select().select(*column_exprs).from_(Subquery(this=input_expr, alias=alias))
         )
@@ -311,7 +321,7 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
     def visit_scan(self, scan: Scan) -> None:
         exprs: list[SQLGlotExpression] = [
             self._expr_visitor.relational_to_sqlglot(col, alias)
-            for alias, col in scan.columns.items()
+            for alias, col in sorted(scan.columns.items())
         ]
         query: Select = Select().select(*exprs).from_(scan.table_name)
         self._stack.append(query)
@@ -493,13 +503,13 @@ class SQLGlotRelationalVisitor(RelationalVisitor):
 
         if self._is_mergeable_ordering(ordering_exprs, input_expr):
             query = self._merge_selects(
-                exprs, input_expr, find_identifiers_in_list(ordering_exprs)
+                exprs, input_expr, find_identifiers_in_list(ordering_exprs), sort=False
             )
             if "order" in query.args:
                 # avoid repeating the order by clause
                 ordering_exprs = []
         else:
-            query = self._build_subquery(input_expr, exprs)
+            query = self._build_subquery(input_expr, exprs, sort=False)
         if ordering_exprs:
             query = query.order_by(*ordering_exprs)
         self._stack.append(query)
diff --git a/pydough/sqlglot/transform_bindings.py b/pydough/sqlglot/transform_bindings.py
index bad15a6..4e30f04 100644
--- a/pydough/sqlglot/transform_bindings.py
+++ b/pydough/sqlglot/transform_bindings.py
@@ -505,6 +505,44 @@ def convert_ndistinct(
         this=sqlglot_expressions.Distinct(expressions=[column])
     )
 
+def create_convert_time_unit_function(unit: str):
+    """
+    Creates a function that extracts a specific time unit
+    (e.g., HOUR, MINUTE, SECOND) from a SQLGlot expression.
+
+    Args:
+        `unit`: The time unit to extract. Must be one of 'HOUR', 'MINUTE',
+                or 'SECOND'.
+    Returns:
+        A function that can convert operands into a SQLGlot expression matching
+        the functionality of `EXTRACT(unit FROM expression)`.
+    """
+    def convert_time_unit(
+        raw_args: Sequence[RelationalExpression] | None,
+        sql_glot_args: Sequence[SQLGlotExpression],
+    ) -> SQLGlotExpression:
+        """
+        Converts and extracts the specific time unit from a SQLGlot expression.
+
+        Args:
+            `raw_args`: The operands passed to the function before they were converted to
+            SQLGlot expressions. (Not actively used in this implementation.)
+            `sql_glot_args`: The operands passed to the function after they were converted
+            to SQLGlot expressions. The first operand is expected to be a timestamp or
+                                    datetime.
+
+        Returns:
+            The SQLGlot expression matching the functionality of
+            `EXTRACT(unit FROM expression)` by extracting the specified time unit
+            from the first operand.
+        """
+        return sqlglot_expressions.Extract(
+            this=sqlglot_expressions.Var(this=unit),
+            expression=sql_glot_args[0]
+        )
+
+    return convert_time_unit
+
 
 class SqlGlotTransformBindings:
     """
@@ -677,6 +715,9 @@ class SqlGlotTransformBindings:
         self.bind_unop(pydop.YEAR, sqlglot_expressions.Year)
         self.bind_unop(pydop.MONTH, sqlglot_expressions.Month)
         self.bind_unop(pydop.DAY, sqlglot_expressions.Day)
+        self.bindings[pydop.HOUR] = create_convert_time_unit_function("HOUR")
+        self.bindings[pydop.MINUTE] = create_convert_time_unit_function("MINUTE")
+        self.bindings[pydop.SECOND] = create_convert_time_unit_function("SECOND")
 
         # Binary operators
         self.bind_binop(pydop.ADD, sqlglot_expressions.Add)
@@ -708,6 +749,9 @@ class SqlGlotTransformBindings:
         self.bindings[pydop.YEAR] = convert_sqlite_datetime_extract("'%Y'")
         self.bindings[pydop.MONTH] = convert_sqlite_datetime_extract("'%m'")
         self.bindings[pydop.DAY] = convert_sqlite_datetime_extract("'%d'")
+        self.bindings[pydop.HOUR] = convert_sqlite_datetime_extract("'%H'")
+        self.bindings[pydop.MINUTE] = convert_sqlite_datetime_extract("'%M'")
+        self.bindings[pydop.SECOND] = convert_sqlite_datetime_extract("'%S'")
 
         # String function overrides
         if sqlite3.sqlite_version < "3.44.1":
diff --git a/pyproject.toml b/pyproject.toml
index 499ac95..f4ffe7b 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -26,7 +26,7 @@ dependencies = ["pytz", "sqlglot", "pandas>=2.0.0", "jupyterlab"]
 Repository = "https://github.com/bodo-ai/PyDough"
 
 [tool.uv]
-dev-dependencies = ["pre-commit", "pytest", "ruff==0.6.7"]
+dev-dependencies = ["pre-commit", "pytest", "ruff==0.6.7", "pytest-repeat"]
 
 [build-system]
 requires = ["hatchling", "hatch-vcs"]


Test patch:
diff --git a/tests/simple_pydough_functions.py b/tests/simple_pydough_functions.py
index 7ec5ba0..dafe200 100644
--- a/tests/simple_pydough_functions.py
+++ b/tests/simple_pydough_functions.py
@@ -270,3 +270,18 @@ def triple_partition():
     )(supp_region, avg_percentage=AVG(cust_regions.percentage)).ORDER_BY(
         supp_region.ASC()
     )
+
+
+def hour_minute_day():
+    """
+    Return the transaction IDs with the hour, minute, and second extracted from
+    transaction timestamps for specific ticker symbols ("AAPL","GOOGL","NFLX"),
+    ordered by transaction ID in ascending order.
+    """
+    return Transactions(
+        transaction_id, HOUR(date_time), MINUTE(date_time), SECOND(date_time)
+        ).WHERE(
+         ISIN(ticker.symbol,("AAPL", "GOOGL", "NFLX"))
+        ).ORDER_BY(
+        transaction_id.ASC()
+    )
diff --git a/tests/test_logging.py b/tests/test_logging.py
index dcab130..802d6f6 100644
--- a/tests/test_logging.py
+++ b/tests/test_logging.py
@@ -155,7 +155,7 @@ def test_execute_df_logging(
         execute_df(root, sqlite_tpch_db_context, sqlite_bindings, display_sql=True)
     # Retrieve the output from the buffer
     captured_output = output_capture.getvalue()
-    required_op = """\
+    required_op = """
 [INFO] pydough.sqlglot.execute_relational: SQL query:
  SELECT
   L_RETURNFLAG,
@@ -170,53 +170,53 @@ def test_execute_df_logging(
   COUNT_ORDER
 FROM (
   SELECT
-    L_RETURNFLAG,
-    L_LINESTATUS,
-    SUM(L_QUANTITY) AS SUM_QTY,
-    SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,
+    COUNT() AS COUNT_ORDER,
     SUM(L_DISCOUNT) AS SUM_DISCOUNT,
+    SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,
+    SUM(L_QUANTITY) AS SUM_QTY,
     SUM(TEMP_COL0) AS SUM_DISC_PRICE,
     SUM(TEMP_COL1) AS SUM_CHARGE,
-    COUNT() AS COUNT_ORDER
+    L_LINESTATUS,
+    L_RETURNFLAG
   FROM (
     SELECT
-      L_QUANTITY,
-      L_EXTENDEDPRICE,
-      L_DISCOUNT,
-      L_RETURNFLAG,
-      L_LINESTATUS,
-      TEMP_COL0,
       TEMP_COL0 * (
         1 + L_TAX
-      ) AS TEMP_COL1
+      ) AS TEMP_COL1,
+      L_DISCOUNT,
+      L_EXTENDEDPRICE,
+      L_LINESTATUS,
+      L_QUANTITY,
+      L_RETURNFLAG,
+      TEMP_COL0
     FROM (
       SELECT
-        L_QUANTITY,
-        L_EXTENDEDPRICE,
-        L_DISCOUNT,
-        L_TAX,
-        L_RETURNFLAG,
-        L_LINESTATUS,
         L_EXTENDEDPRICE * (
           1 - L_DISCOUNT
-        ) AS TEMP_COL0
+        ) AS TEMP_COL0,
+        L_DISCOUNT,
+        L_EXTENDEDPRICE,
+        L_LINESTATUS,
+        L_QUANTITY,
+        L_RETURNFLAG,
+        L_TAX
       FROM (
         SELECT
-          L_QUANTITY,
-          L_EXTENDEDPRICE,
           L_DISCOUNT,
-          L_TAX,
+          L_EXTENDEDPRICE,
+          L_LINESTATUS,
+          L_QUANTITY,
           L_RETURNFLAG,
-          L_LINESTATUS
+          L_TAX
         FROM (
           SELECT
-            L_QUANTITY,
-            L_EXTENDEDPRICE,
             L_DISCOUNT,
-            L_TAX,
-            L_RETURNFLAG,
+            L_EXTENDEDPRICE,
             L_LINESTATUS,
-            L_SHIPDATE
+            L_QUANTITY,
+            L_RETURNFLAG,
+            L_SHIPDATE,
+            L_TAX
           FROM LINEITEM
         )
         WHERE
diff --git a/tests/test_pipeline.py b/tests/test_pipeline.py
index 41605af..f2751d3 100644
--- a/tests/test_pipeline.py
+++ b/tests/test_pipeline.py
@@ -16,6 +16,7 @@ from simple_pydough_functions import (
     agg_partition,
     double_partition,
     function_sampler,
+    hour_minute_day,
     percentile_customers_per_region,
     percentile_nations,
     rank_nations_by_region,
@@ -1168,3 +1169,58 @@ def test_pipeline_e2e_errors(
     with pytest.raises(Exception, match=error_msg):
         root: UnqualifiedNode = init_pydough_context(graph)(impl)()
         to_df(root, metadata=graph, database=sqlite_tpch_db_context)
+
+
+@pytest.fixture(
+    params=[
+        pytest.param(
+            (
+                hour_minute_day,
+                "Broker",
+                lambda: pd.DataFrame(
+                    {
+                        "transaction_id": [
+                            "TX001", "TX005", "TX011", "TX015", "TX021", "TX025", 
+                            "TX031", "TX033", "TX035", "TX044", "TX045", "TX049", 
+                            "TX051", "TX055"
+                        ],
+                        "_expr0": [9, 12, 9, 12, 9, 12, 0, 0, 0, 10, 10, 16, 0, 0],
+                        "_expr1": [30, 30, 30, 30, 30, 30, 0, 0, 0, 0, 30, 0, 0, 0],
+                        "_expr2": [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
+                    }
+                )
+            ),
+            id="broker_basic1",
+        ),
+  ],
+)
+def custom_defog_test_data(
+    request,
+) -> tuple[Callable[[], UnqualifiedNode],str,pd.DataFrame]:
+    """
+    Test data for test_defog_e2e. Returns a tuple of the following
+    arguments:
+    1. `unqualified_impl`: a PyDough implementation function.
+    2. `graph_name`: the name of the graph from the defog database to use.
+    3. `answer_impl`: a function that takes in nothing and returns the answer
+    to a defog query as a Pandas DataFrame.
+    """
+    return request.param
+
+
+@pytest.mark.execute
+def test_defog_e2e_with_custom_data(
+    custom_defog_test_data: tuple[Callable[[], UnqualifiedNode],str,pd.DataFrame],
+    defog_graphs: graph_fetcher,
+    sqlite_defog_connection: DatabaseContext,
+):
+    """
+    Test executing the defog analytical questions on the sqlite database,
+    comparing against the result of running the reference SQL query text on the
+    same database connector.
+    """
+    unqualified_impl, graph_name ,answer_impl = custom_defog_test_data
+    graph: GraphMetadata = defog_graphs(graph_name)
+    root: UnqualifiedNode = init_pydough_context(graph)(unqualified_impl)()
+    result: pd.DataFrame = to_df(root, metadata=graph, database=sqlite_defog_connection)
+    pd.testing.assert_frame_equal(result, answer_impl())
diff --git a/tests/test_pydough_to_sql.py b/tests/test_pydough_to_sql.py
index 34f78e1..dc18084 100644
--- a/tests/test_pydough_to_sql.py
+++ b/tests/test_pydough_to_sql.py
@@ -5,7 +5,14 @@ Test that tests the full conversion of a PyDough object to a SQL query.
 from collections.abc import Callable
 
 import pytest
-from simple_pydough_functions import rank_a, rank_b, rank_c, simple_filter, simple_scan
+from simple_pydough_functions import (
+    hour_minute_day,
+    rank_a,
+    rank_b,
+    rank_c,
+    simple_filter,
+    simple_scan,
+)
 from test_utils import (
     graph_fetcher,
 )
@@ -64,3 +71,30 @@ def test_pydough_to_sql(
     actual_sql: str = to_sql(root, metadata=graph).strip()
     expected_sql = expected_sql.strip()
     assert actual_sql == expected_sql
+
+@pytest.mark.parametrize(
+    "pydough_code,expected_sql,graph_name",
+    [
+        pytest.param(
+            hour_minute_day,
+            """SELECT transaction_id, _expr0, _expr1, _expr2 FROM (SELECT transaction_id AS ordering_0, _expr0, _expr1, _expr2, transaction_id FROM (SELECT _expr0, _expr1, _expr2, symbol, transaction_id FROM (SELECT EXTRACT(HOUR FROM date_time) AS _expr0, EXTRACT(MINUTE FROM date_time) AS _expr1, EXTRACT(SECOND FROM date_time) AS _expr2, ticker_id, transaction_id FROM (SELECT sbTxDateTime AS date_time, sbTxId AS transaction_id, sbTxTickerId AS ticker_id FROM main.sbTransaction)) LEFT JOIN (SELECT sbTickerId AS _id, sbTickerSymbol AS symbol FROM main.sbTicker) ON ticker_id = _id) WHERE symbol IN ('AAPL', 'GOOGL', 'NFLX')) ORDER BY ordering_0""",
+            "Broker",
+            id="hour_minute_day",
+        ),
+    ],
+)
+def test_pydough_to_sql_defog(
+    pydough_code: Callable[[], UnqualifiedNode],
+    expected_sql: str,
+    graph_name: str,
+    defog_graphs: graph_fetcher,
+) -> None:
+    """
+    Tests that a PyDough unqualified node can be correctly translated to its
+    sql, with the correct string representation.
+    """
+    graph: GraphMetadata = defog_graphs(graph_name)
+    root: UnqualifiedNode = init_pydough_context(graph)(pydough_code)()
+    actual_sql: str = to_sql(root, metadata=graph).strip()
+    expected_sql = expected_sql.strip()
+    assert actual_sql == expected_sql
diff --git a/tests/test_relational_nodes_to_sqlglot.py b/tests/test_relational_nodes_to_sqlglot.py
index 1075ea6..86b573e 100644
--- a/tests/test_relational_nodes_to_sqlglot.py
+++ b/tests/test_relational_nodes_to_sqlglot.py
@@ -249,9 +249,9 @@ def mkglot_func(op: type[Expression], args: list[Expression]) -> Expression:
             ),
             mkglot(
                 expressions=[
+                    set_glot_alias(mk_literal(1, False), "c"),
                     Ident(this="a"),
                     Ident(this="b"),
-                    set_glot_alias(mk_literal(1, False), "c"),
                 ],
                 _from=GlotFrom(Table(this=Ident(this="table"))),
             ),
@@ -615,8 +615,8 @@ def mkglot_func(op: type[Expression], args: list[Expression]) -> Expression:
             ),
             mkglot(
                 expressions=[
-                    Ident(this="b"),
                     set_glot_alias(mk_literal(1, False), "c"),
+                    Ident(this="b"),
                 ],
                 _from=GlotFrom(Table(this=Ident(this="table"))),
                 limit=mk_literal(2, False),
@@ -678,11 +678,11 @@ def mkglot_func(op: type[Expression], args: list[Expression]) -> Expression:
             ),
             mkglot(
                 expressions=[
-                    Ident(this="b"),
                     set_glot_alias(
                         mkglot_func(Sum, [Ident(this="a")]),
                         "a",
                     ),
+                    Ident(this="b"),
                 ],
                 group_by=[Ident(this="b")],
                 _from=GlotFrom(
@@ -759,11 +759,11 @@ def mkglot_func(op: type[Expression], args: list[Expression]) -> Expression:
                 _from=GlotFrom(
                     mkglot(
                         expressions=[
-                            Ident(this="b"),
                             set_glot_alias(
                                 mkglot_func(Sum, [Ident(this="a")]),
                                 "a",
                             ),
+                            Ident(this="b"),
                         ],
                         group_by=[Ident(this="b")],
                         _from=GlotFrom(
@@ -1297,14 +1297,14 @@ def mkglot_func(op: type[Expression], args: list[Expression]) -> Expression:
                 _from=GlotFrom(
                     mkglot(
                         expressions=[
-                            Ident(this="a"),
-                            Ident(this="b"),
                             set_glot_alias(
                                 mkglot_func(
                                     Add, [Ident(this="a"), mk_literal(1, False)]
                                 ),
                                 "c",
                             ),
+                            Ident(this="a"),
+                            Ident(this="b"),
                         ],
                         _from=GlotFrom(
                             mkglot(
@@ -1418,11 +1418,11 @@ def mkglot_func(op: type[Expression], args: list[Expression]) -> Expression:
                 _from=GlotFrom(
                     mkglot(
                         expressions=[
-                            Ident(this="b"),
                             set_glot_alias(
                                 mkglot_func(Sum, [Ident(this="a")]),
                                 "a",
                             ),
+                            Ident(this="b"),
                         ],
                         group_by=[Ident(this="b")],
                         _from=GlotFrom(
diff --git a/tests/test_relational_to_sql.py b/tests/test_relational_to_sql.py
index 8c00d76..068196c 100644
--- a/tests/test_relational_to_sql.py
+++ b/tests/test_relational_to_sql.py
@@ -115,7 +115,7 @@ def sqlite_dialect() -> SQLiteDialect:
                     ),
                 ],
             ),
-            "SELECT b FROM (SELECT a, b, a + 1 AS c FROM (SELECT a, b FROM table)) ORDER BY c",
+            "SELECT b FROM (SELECT a + 1 AS c, a, b FROM (SELECT a, b FROM table)) ORDER BY c",
             id="project_scan_with_ordering",
         ),
         pytest.param(
@@ -701,17 +701,17 @@ def test_convert_relation_to_sql(
     [
         pytest.param(
             tpch_query_1_plan(),
-            "SELECT L_RETURNFLAG, L_LINESTATUS, SUM_QTY, SUM_BASE_PRICE, SUM_DISC_PRICE, SUM_CHARGE, CAST(SUM_QTY AS REAL) / COUNT_ORDER AS AVG_QTY, CAST(SUM_BASE_PRICE AS REAL) / COUNT_ORDER AS AVG_PRICE, CAST(SUM_DISCOUNT AS REAL) / COUNT_ORDER AS AVG_DISC, COUNT_ORDER FROM (SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_DISCOUNT) AS SUM_DISCOUNT, SUM(TEMP_COL0) AS SUM_DISC_PRICE, SUM(TEMP_COL1) AS SUM_CHARGE, COUNT() AS COUNT_ORDER FROM (SELECT L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_RETURNFLAG, L_LINESTATUS, TEMP_COL0, TEMP_COL0 * (1 + L_TAX) AS TEMP_COL1 FROM (SELECT L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS TEMP_COL0 FROM (SELECT L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS FROM (SELECT L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE FROM LINEITEM) WHERE L_SHIPDATE <= '1998-12-01'))) GROUP BY L_RETURNFLAG, L_LINESTATUS) ORDER BY L_RETURNFLAG, L_LINESTATUS",
+            "SELECT L_RETURNFLAG, L_LINESTATUS, SUM_QTY, SUM_BASE_PRICE, SUM_DISC_PRICE, SUM_CHARGE, CAST(SUM_QTY AS REAL) / COUNT_ORDER AS AVG_QTY, CAST(SUM_BASE_PRICE AS REAL) / COUNT_ORDER AS AVG_PRICE, CAST(SUM_DISCOUNT AS REAL) / COUNT_ORDER AS AVG_DISC, COUNT_ORDER FROM (SELECT COUNT() AS COUNT_ORDER, SUM(L_DISCOUNT) AS SUM_DISCOUNT, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_QUANTITY) AS SUM_QTY, SUM(TEMP_COL0) AS SUM_DISC_PRICE, SUM(TEMP_COL1) AS SUM_CHARGE, L_LINESTATUS, L_RETURNFLAG FROM (SELECT TEMP_COL0 * (1 + L_TAX) AS TEMP_COL1, L_DISCOUNT, L_EXTENDEDPRICE, L_LINESTATUS, L_QUANTITY, L_RETURNFLAG, TEMP_COL0 FROM (SELECT L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS TEMP_COL0, L_DISCOUNT, L_EXTENDEDPRICE, L_LINESTATUS, L_QUANTITY, L_RETURNFLAG, L_TAX FROM (SELECT L_DISCOUNT, L_EXTENDEDPRICE, L_LINESTATUS, L_QUANTITY, L_RETURNFLAG, L_TAX FROM (SELECT L_DISCOUNT, L_EXTENDEDPRICE, L_LINESTATUS, L_QUANTITY, L_RETURNFLAG, L_SHIPDATE, L_TAX FROM LINEITEM) WHERE L_SHIPDATE <= '1998-12-01'))) GROUP BY L_RETURNFLAG, L_LINESTATUS) ORDER BY L_RETURNFLAG, L_LINESTATUS",
             id="tpch_q1",
         ),
         pytest.param(
             tpch_query_3_plan(),
-            "SELECT L_ORDERKEY, REVENUE, O_ORDERDATE, O_SHIPPRIORITY FROM (SELECT L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY, SUM(REVENUE) AS REVENUE FROM (SELECT L_ORDERKEY, REVENUE, O_ORDERDATE, O_SHIPPRIORITY FROM (SELECT L_ORDERKEY, L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS REVENUE FROM (SELECT L_ORDERKEY, L_EXTENDEDPRICE, L_DISCOUNT FROM (SELECT L_ORDERKEY, L_EXTENDEDPRICE, L_DISCOUNT, L_SHIPDATE FROM LINEITEM) WHERE L_SHIPDATE > '1995-03-15')) INNER JOIN (SELECT O_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY FROM (SELECT O_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY FROM ORDERS WHERE O_ORDERDATE < '1995-03-15') INNER JOIN (SELECT C_CUSTKEY FROM (SELECT C_CUSTKEY, C_MKTSEGMENT FROM CUSTOMER) WHERE C_MKTSEGMENT = 'BUILDING') ON O_CUSTKEY = C_CUSTKEY) ON L_ORDERKEY = O_ORDERKEY) GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY) ORDER BY REVENUE DESC, O_ORDERDATE, L_ORDERKEY LIMIT 10",
+            "SELECT L_ORDERKEY, REVENUE, O_ORDERDATE, O_SHIPPRIORITY FROM (SELECT SUM(REVENUE) AS REVENUE, L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY FROM (SELECT L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY, REVENUE FROM (SELECT L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS REVENUE, L_ORDERKEY FROM (SELECT L_DISCOUNT, L_EXTENDEDPRICE, L_ORDERKEY FROM (SELECT L_DISCOUNT, L_EXTENDEDPRICE, L_ORDERKEY, L_SHIPDATE FROM LINEITEM) WHERE L_SHIPDATE > '1995-03-15')) INNER JOIN (SELECT O_ORDERDATE, O_ORDERKEY, O_SHIPPRIORITY FROM (SELECT O_CUSTKEY, O_ORDERDATE, O_ORDERKEY, O_SHIPPRIORITY FROM ORDERS WHERE O_ORDERDATE < '1995-03-15') INNER JOIN (SELECT C_CUSTKEY FROM (SELECT C_CUSTKEY, C_MKTSEGMENT FROM CUSTOMER) WHERE C_MKTSEGMENT = 'BUILDING') ON O_CUSTKEY = C_CUSTKEY) ON L_ORDERKEY = O_ORDERKEY) GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY) ORDER BY REVENUE DESC, O_ORDERDATE, L_ORDERKEY LIMIT 10",
             id="tpch_q3",
         ),
         pytest.param(
             tpch_query_6_plan(),
-            "SELECT SUM(TEMP_COL0) AS REVENUE FROM (SELECT L_EXTENDEDPRICE * L_DISCOUNT AS TEMP_COL0 FROM (SELECT L_EXTENDEDPRICE, L_DISCOUNT FROM (SELECT L_QUANTITY, L_DISCOUNT, L_EXTENDEDPRICE, L_SHIPDATE FROM LINEITEM) WHERE (L_QUANTITY < 24) AND (L_DISCOUNT <= 0.07) AND (L_DISCOUNT >= 0.05) AND (L_SHIPDATE < '1995-01-01') AND (L_SHIPDATE >= '1994-01-01')))",
+            "SELECT SUM(TEMP_COL0) AS REVENUE FROM (SELECT L_EXTENDEDPRICE * L_DISCOUNT AS TEMP_COL0 FROM (SELECT L_DISCOUNT, L_EXTENDEDPRICE FROM (SELECT L_DISCOUNT, L_EXTENDEDPRICE, L_QUANTITY, L_SHIPDATE FROM LINEITEM) WHERE (L_QUANTITY < 24) AND (L_DISCOUNT <= 0.07) AND (L_DISCOUNT >= 0.05) AND (L_SHIPDATE < '1995-01-01') AND (L_SHIPDATE >= '1994-01-01')))",
             id="tpch_q6",
         ),
     ],
@@ -1023,7 +1023,7 @@ def test_tpch_relational_to_sql(
                     ),
                 ),
             ),
-            "SELECT a, b, r FROM (SELECT a, b, RANK() OVER (ORDER BY a) AS r FROM (SELECT a, b FROM table) WHERE b = 0) WHERE r >= 3",
+            "SELECT a, b, r FROM (SELECT RANK() OVER (ORDER BY a) AS r, a, b FROM (SELECT a, b FROM table) WHERE b = 0) WHERE r >= 3",
             id="rank_with_filters_a",
         ),
         pytest.param(
@@ -1085,7 +1085,7 @@ def test_tpch_relational_to_sql(
                     ),
                 ),
             ),
-            "SELECT a, b, r FROM (SELECT a, b, r FROM (SELECT a, b, RANK() OVER (ORDER BY a) AS r FROM (SELECT a, b FROM table)) WHERE r >= 3) WHERE b = 0",
+            "SELECT a, b, r FROM (SELECT a, b, r FROM (SELECT RANK() OVER (ORDER BY a) AS r, a, b FROM (SELECT a, b FROM table)) WHERE r >= 3) WHERE b = 0",
             id="rank_with_filters_b",
         ),
     ],