Repository: TobikoData/sqlmesh
Pull request #2119 (2024-02-13 00:46:12)

fix: use space instead of T for timestamp format
Prior to this PR, Trino would error if we provided a timestamp since it expects the format to have a space between date and time instead of a `T`. I tested across all engines and they all accept the space so switching to that instead. 

Note that this PR does not address the issue that `ts` includes timezone but we assign it to a datatype that does not support TIMEZONE. That is being addressed in this PR: https://github.com/TobikoData/sqlmesh/pull/2112


Patch:
diff --git a/sqlmesh/core/model/definition.py b/sqlmesh/core/model/definition.py
index 32a2efa5..553513e4 100644
--- a/sqlmesh/core/model/definition.py
+++ b/sqlmesh/core/model/definition.py
@@ -34,7 +34,7 @@ from sqlmesh.core.model.meta import ModelMeta
 from sqlmesh.core.model.seed import CsvSeedReader, Seed, create_seed
 from sqlmesh.core.renderer import ExpressionRenderer, QueryRenderer
 from sqlmesh.utils import columns_to_types_all_known, str_to_bool
-from sqlmesh.utils.date import TimeLike, make_inclusive, to_datetime, to_ds, to_ts
+from sqlmesh.utils.date import TimeLike, make_inclusive, to_datetime, to_time_column
 from sqlmesh.utils.errors import ConfigError, SQLMeshError, raise_config_error
 from sqlmesh.utils.hashing import hash_data
 from sqlmesh.utils.jinja import JinjaMacroRegistry, extract_macro_references
@@ -512,17 +512,7 @@ class _Model(ModelMeta, frozen=True):
 
             time_column_type = columns_to_types[self.time_column.column]
 
-            if time_column_type.is_type(exp.DataType.Type.DATE):
-                return exp.cast(exp.Literal.string(to_ds(time)), to="date")
-            if time_column_type.this in exp.DataType.TEMPORAL_TYPES:
-                return exp.cast(exp.Literal.string(to_ts(time)), to=time_column_type.this)
-
-            if self.time_column.format:
-                time = to_datetime(time).strftime(self.time_column.format)
-            if time_column_type.this in exp.DataType.TEXT_TYPES:
-                return exp.Literal.string(time)
-            if time_column_type.this in exp.DataType.NUMERIC_TYPES:
-                return exp.Literal.number(time)
+            return to_time_column(time, time_column_type, self.time_column.format)
         return exp.convert(time)
 
     def update_schema(
diff --git a/sqlmesh/core/snapshot/definition.py b/sqlmesh/core/snapshot/definition.py
index dac7290b..c2fa1615 100644
--- a/sqlmesh/core/snapshot/definition.py
+++ b/sqlmesh/core/snapshot/definition.py
@@ -29,6 +29,7 @@ from sqlmesh.utils.date import (
     to_datetime,
     to_ds,
     to_timestamp,
+    to_ts,
     validate_date_range,
     yesterday,
 )
@@ -1349,7 +1350,8 @@ def merge_intervals(intervals: Intervals) -> Intervals:
 def _format_date_time(time_like: TimeLike, unit: t.Optional[IntervalUnit]) -> str:
     if unit is None or unit.is_date_granularity:
         return to_ds(time_like)
-    return to_datetime(time_like).isoformat()[:19]
+    # TODO: Remove `[0:19]` once `to_ts` always returns a timestamp without timezone
+    return to_ts(time_like)[0:19]
 
 
 def format_intervals(intervals: Intervals, unit: t.Optional[IntervalUnit]) -> str:
diff --git a/sqlmesh/utils/date.py b/sqlmesh/utils/date.py
index 0c366273..1d017d87 100644
--- a/sqlmesh/utils/date.py
+++ b/sqlmesh/utils/date.py
@@ -214,7 +214,7 @@ def date_dict(
         kwargs[f"{prefix}_dt"] = dt
         kwargs[f"{prefix}_date"] = to_date(dt)
         kwargs[f"{prefix}_ds"] = to_ds(time_like)
-        kwargs[f"{prefix}_ts"] = dt.isoformat()
+        kwargs[f"{prefix}_ts"] = to_ts(dt)
         kwargs[f"{prefix}_epoch"] = millis / 1000
         kwargs[f"{prefix}_millis"] = millis
         kwargs[f"{prefix}_hour"] = dt.hour
@@ -228,7 +228,7 @@ def to_ds(obj: TimeLike) -> str:
 
 def to_ts(obj: TimeLike) -> str:
     """Converts a TimeLike object into YYYY-MM-DD HH:MM:SS formatted string."""
-    return to_datetime(obj).isoformat()
+    return to_datetime(obj).isoformat(sep=" ")
 
 
 def is_date(obj: TimeLike) -> bool:
@@ -302,3 +302,25 @@ def is_catagorical_relative_expression(expression: str) -> bool:
     if not grain_kwargs:
         return False
     return not any(k in TIME_UNITS for k in grain_kwargs)
+
+
+def to_time_column(
+    time_column: t.Union[TimeLike, exp.Null],
+    time_column_type: exp.DataType,
+    time_column_format: t.Optional[str] = None,
+) -> exp.Expression:
+    """Convert a TimeLike object to the same time format and type as the model's time column."""
+    if isinstance(time_column, exp.Null):
+        return exp.cast(time_column, to=time_column_type)
+    if time_column_type.is_type(exp.DataType.Type.DATE):
+        return exp.cast(exp.Literal.string(to_ds(time_column)), to="date")
+    if time_column_type.this in exp.DataType.TEMPORAL_TYPES:
+        return exp.cast(exp.Literal.string(to_ts(time_column)), to=time_column_type.this)
+
+    if time_column_format:
+        time_column = to_datetime(time_column).strftime(time_column_format)
+    if time_column_type.this in exp.DataType.TEXT_TYPES:
+        return exp.Literal.string(time_column)
+    if time_column_type.this in exp.DataType.NUMERIC_TYPES:
+        return exp.Literal.number(time_column)
+    return exp.convert(time_column)


Test patch:
diff --git a/tests/core/test_model.py b/tests/core/test_model.py
index 8265ca55..92982c80 100644
--- a/tests/core/test_model.py
+++ b/tests/core/test_model.py
@@ -1338,7 +1338,7 @@ def test_convert_to_time_column():
     )
     model = load_sql_based_model(expressions)
     assert model.convert_to_time_column("2022-01-01") == d.parse_one(
-        "CAST('2022-01-01T00:00:00+00:00' AS TIMESTAMP)"
+        "CAST('2022-01-01 00:00:00+00:00' AS TIMESTAMP)"
     )
 
 
diff --git a/tests/core/test_state_sync.py b/tests/core/test_state_sync.py
index a450a990..05878efb 100644
--- a/tests/core/test_state_sync.py
+++ b/tests/core/test_state_sync.py
@@ -1131,7 +1131,7 @@ def test_environment_start_as_timestamp(
 
     stored_env = state_sync.get_environment(env.name)
     assert stored_env
-    assert stored_env.start_at == to_datetime(now_ts).isoformat()
+    assert stored_env.start_at == to_datetime(now_ts).isoformat(sep=" ")
 
 
 def test_unpause_snapshots(state_sync: EngineAdapterStateSync, make_snapshot: t.Callable):
diff --git a/tests/utils/test_date.py b/tests/utils/test_date.py
index 3048c02c..2f743ebc 100644
--- a/tests/utils/test_date.py
+++ b/tests/utils/test_date.py
@@ -9,6 +9,7 @@ from sqlmesh.utils.date import (
     make_inclusive,
     to_datetime,
     to_timestamp,
+    to_ts,
 )
 
 
@@ -114,3 +115,8 @@ def test_make_inclusive(start_in, end_in, start_out, end_out) -> None:
 )
 def test_is_catagorical_relative_expression(expression, result):
     assert is_catagorical_relative_expression(expression) == result
+
+
+def test_to_ts():
+    assert to_ts(datetime(2020, 1, 1).replace(tzinfo=UTC)) == "2020-01-01 00:00:00+00:00"
+    assert to_ts(datetime(2020, 1, 1).replace(tzinfo=None)) == "2020-01-01 00:00:00+00:00"