Repository: meltano/sdk

Issue:
bug: JSONified replication value comparison swallows microseconds from datetime objects
### Singer SDK Version

0.45.10

### Is this a regression?

- [ ] Yes

### Python Version

NA

### Bug scope

Taps (catalog, state, etc.)

### Operating System

NA

### Description

> Having an issue with bookmark comparison on a sql tap/target.
> ```
> singer_sdk.exceptions.InvalidStreamSortException: Unsorted data detected in stream. Latest value '2025-04-16T11:54:35.060000+00:00' is smaller than previous max '2025-04-16T11:54:35.063000+00:00
> ```
> It looks like an issue on the constraint or the stream sorter that doesn't honor microseconds in the mssql datetime datatype.

This is happening in

https://github.com/meltano/sdk/blob/dc7fd0d139dbdefbc291772d777d6d3e3c8b941b/singer_sdk/helpers/_typing.py#L45-L47

## Links

- https://meltano.slack.com/archives/C069CQNHDNF/p1745360849599039
- https://discuss.meltano.com/t/27567128/having-an-issue-with-bookmark-comparison-on-a-sql-tap-target#460ed3cf-fe9d-458d-a905-342ba87abb80


### Link to Slack/Linen

_No response_

Discussion:

Solution patch:
diff --git a/singer_sdk/helpers/_typing.py b/singer_sdk/helpers/_typing.py
index a2ae8513..1c8f9f46 100644
--- a/singer_sdk/helpers/_typing.py
+++ b/singer_sdk/helpers/_typing.py
@@ -44,7 +44,10 @@ def to_json_compatible(val: t.Any) -> t.Any:  # noqa: ANN401
     """Return as string if datetime. JSON does not support proper datetime types."""
     if isinstance(val, (datetime.datetime,)):
         # Make naive datetimes UTC
-        return (val.replace(tzinfo=UTC) if val.tzinfo is None else val).isoformat("T")
+        return (val.replace(tzinfo=UTC) if val.tzinfo is None else val).isoformat(
+            "T",
+            timespec="microseconds",
+        )
     if isinstance(val, (uuid.UUID,)):
         return str(val)
     return val


Test patch:
diff --git a/tests/core/test_record_typing.py b/tests/core/test_record_typing.py
index ac222f91..1702608f 100644
--- a/tests/core/test_record_typing.py
+++ b/tests/core/test_record_typing.py
@@ -21,21 +21,21 @@ from singer_sdk.helpers._typing import (
     "record,schema,expected_row,ignore_props_message",
     [
         (
-            {"updatedAt": parse("2021-08-25T20:05:28+00:00")},
+            {"updatedAt": parse("2021-08-25T20:05:28.000000+00:00")},
             {"properties": {"updatedAt": True}},
-            {"updatedAt": "2021-08-25T20:05:28+00:00"},
+            {"updatedAt": "2021-08-25T20:05:28.000000+00:00"},
             None,
         ),
         (
-            {"updatedAt": parse("2021-08-25T20:05:28Z")},
+            {"updatedAt": parse("2021-08-25T20:05:28.000000Z")},
             {"properties": {"updatedAt": True}},
-            {"updatedAt": "2021-08-25T20:05:28+00:00"},
+            {"updatedAt": "2021-08-25T20:05:28.000000+00:00"},
             None,
         ),
         (
-            {"updatedAt": parse("2021-08-25T20:05:28")},
+            {"updatedAt": parse("2021-08-25T20:05:28.000000")},
             {"properties": {"updatedAt": True}},
-            {"updatedAt": "2021-08-25T20:05:28+00:00"},
+            {"updatedAt": "2021-08-25T20:05:28.000000+00:00"},
             None,
         ),
         (
@@ -84,21 +84,24 @@ def test_conform_record_data_types(
 @pytest.mark.parametrize(
     "datetime_val,expected",
     [
-        (parse("2021-08-25T20:05:28+00:00"), "2021-08-25T20:05:28+00:00"),
-        (parse("2021-08-25T20:05:28+07:00"), "2021-08-25T20:05:28+07:00"),
+        (parse("2021-08-25T20:05:28.000000+00:00"), "2021-08-25T20:05:28.000000+00:00"),
+        (parse("2021-08-25T20:05:28.000000+07:00"), "2021-08-25T20:05:28.000000+07:00"),
         (
             datetime.strptime(  # noqa: DTZ007
-                "2021-08-25T20:05:28",
-                "%Y-%m-%dT%H:%M:%S",
+                "2021-08-25T20:05:28.000000",
+                "%Y-%m-%dT%H:%M:%S.%f",
             ),
-            "2021-08-25T20:05:28+00:00",
+            "2021-08-25T20:05:28.000000+00:00",
         ),
         (
-            datetime.strptime("2021-08-25T20:05:28-03:00", "%Y-%m-%dT%H:%M:%S%z"),
-            "2021-08-25T20:05:28-03:00",
+            datetime.strptime(
+                "2021-08-25T20:05:28.000000-03:00",
+                "%Y-%m-%dT%H:%M:%S.%f%z",
+            ),
+            "2021-08-25T20:05:28.000000-03:00",
         ),
-        ("2021-08-25T20:05:28", "2021-08-25T20:05:28"),
-        ("2021-08-25T20:05:28Z", "2021-08-25T20:05:28Z"),
+        ("2021-08-25T20:05:28.000000", "2021-08-25T20:05:28.000000"),
+        ("2021-08-25T20:05:28.000000Z", "2021-08-25T20:05:28.000000Z"),
     ],
 )
 def test_to_json_compatible(datetime_val, expected):
diff --git a/tests/core/test_state_handling.py b/tests/core/test_state_handling.py
index 9fa44c37..67301862 100644
--- a/tests/core/test_state_handling.py
+++ b/tests/core/test_state_handling.py
@@ -2,6 +2,7 @@
 
 from __future__ import annotations
 
+import datetime
 import logging
 import uuid
 
@@ -174,3 +175,28 @@ def test_uuidv7_replication_value():
     )
 
     assert stream_state["replication_key_value"] == new_string_val
+
+
+def test_datetime_with_microseconds():
+    stream_state = {
+        "replication_key": "updated_at",
+        "replication_key_value": "2021-05-17T20:41:16.000000+00:00",
+    }
+    latest_record = {
+        "updated_at": datetime.datetime(
+            2021,
+            5,
+            17,
+            20,
+            41,
+            16,
+            tzinfo=datetime.timezone.utc,
+        )
+    }
+    _state.increment_state(
+        stream_state,
+        latest_record=latest_record,
+        replication_key="updated_at",
+        is_sorted=True,
+        check_sorted=True,
+    )
diff --git a/tests/core/test_typing.py b/tests/core/test_typing.py
index 502538c8..44f344b9 100644
--- a/tests/core/test_typing.py
+++ b/tests/core/test_typing.py
@@ -297,7 +297,7 @@ def test_conform_object_additional_properties():
         pytest.param(
             datetime.datetime(2020, 5, 17, tzinfo=datetime.timezone.utc),
             {"type": "string"},
-            "2020-05-17T00:00:00+00:00",
+            "2020-05-17T00:00:00.000000+00:00",
             id="datetime_to_string",
         ),
         pytest.param(