`DateTime` class is too limited for esoteric Data API usage

[toptobes]

Will let @sl-at-ibm explain further. The following is copied from the internal document:

The wide range of timestamps in Data API are not covered by the DateTime native class. 

Probably calls for a custom data type. 

Error is System.ArgumentOutOfRangeException : Value to add was out of range. (Parameter 'value').

Talk to Stefano for more (incl. repro), and compare here:
(https://docs.datastax.com/en/astra-db-serverless/api-reference/python-client.html#dataapitimestamp-and-datetimes).

When reading a timestamp from a table, below the millisecond level the value differs from what was read (and from what is in Cassandra). 

Could call for a custom data type (currently it's stdlib).

Note from me:

• This can be handled post-GA by allowing for an opt-in custom "DataAPIDateTime" or "DataAPITimestamp" dataclass (the clients have a precedent of prefixing our custom datatypes with DataAPI*)

Should be handled by @a-random-steve

[sl-at-ibm]

A collection-related example to show the extreme year values:

public class ExtremeTimestampsObject
{
    [DocumentId]
    public string _id {get; set;}
    public DateTime dt_far_past { get; set; }
    public DateTime dt_far_future { get; set; }
    public string far_past_desc { get; set; }
    public string far_future_desc { get; set; }
}

[...]

    [Fact(Skip = "Unskip manually when needed.")]
    public async Task SteoCollectionExtremeTimestampsTest()
    {
        /* Setup requires astrapy (or curl if you want):

            from astrapy.data_types import DataAPITimestamp

            tsa=DataAPITimestamp.from_string('+50000-01-01T01:01:01.001Z')
            tsb=DataAPITimestamp.from_string('-50000-01-01T01:01:01.001Z')

            the_doc = {
                "_id": "from_astrapy",
                "dt_far_past": tsb,
                "dt_far_future": tsa,
                "far_past_desc": f"From '{tsb.to_string()}', timestamp_ms={tsb.timestamp_ms}",
                "far_future_desc": f"From '{tsa.to_string()}', timestamp_ms={tsa.timestamp_ms}",
            }

            coll=database.create_collection("extreme_timestamps_coll")
            coll.insert_one(the_doc)

        **Equivalently** insert this with curl to Data API once collection created:
            curl -XPOST \
                $ASTRA_DB_URL/api/json/v1/default_keyspace/extreme_timestamps_coll  \
                -H "token: $ASTRA_DB_TOKEN"  \
                -H "Content-Type: application/json"  \
                -d '{
                "insertOne": {
                    "document": {
                        "_id": "from_astrapy",
                        "dt_far_past": {
                            "$date": -1640014815538999
                        },
                        "dt_far_future": {
                            "$date": 1515680384461001
                        },
                        "far_past_desc": "From '-50000-01-01T01:01:01.001Z', timestamp_ms=-1640014815538999",
                        "far_future_desc": "From '+50000-01-01T01:01:01.001Z', timestamp_ms=1515680384461001"
                    }
                }
            }'
        */
        try
        {
            var collection = await fixture.Database.CreateCollectionAsync<ExtremeTimestampsObject>("extreme_timestamps_coll");

            var filter = Builders<ExtremeTimestampsObject>.Filter.Eq(d => d._id, "from_astrapy");
            var et_read = await collection.FindOneAsync(filter); // <== FAILS due to timestamp on collection out of bounds for the DateTime class
        }
        finally
        {
            // var names = await fixture.Database.ListCollectionNamesAsync();
        }
    }

A table-related test (adapted from another test, other troubles commented out; read occurs within a UDT but should not be relevant). This shows the loss of sub-millisecond precision:

    [Fact(Skip = "Unskip manually when needed.")]
    public async Task SteoTableUDTDatetimeLimitationsTest()
    {
        var tableName = "datetime_limitations_table";
        try
        {
            List<UdtTest> items = new List<UdtTest>() {
                new()
                {
                    Id = 0,
                    Udt = new TypesTester
                    {
                        String = "Test 1",
                        //Inet = IPAddress.Parse("192.168.0.1"),
                        Int = int.MaxValue,
                        TinyInt = byte.MaxValue,
                        SmallInt = short.MaxValue,
                        BigInt = long.MaxValue,
                        Decimal = decimal.MaxValue,
                        Double = double.MaxValue,
                        Float = float.MaxValue,
                        Boolean = false,
                        UUID = Guid.NewGuid(),
                        Duration = Duration.Parse("12y3mo1d12h30m5s12ms7us1ns"),
                        Timestamp = DateTime.Now,
                        Date = DateOnly.FromDateTime(DateTime.Now),
                        Time = TimeOnly.FromDateTime(DateTime.Now),
                        MaybeTimestamp = null,
                        MaybeDate = DateOnly.FromDateTime(DateTime.Now),
                        MaybeTime = null,
                        TimestampWithKind = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Utc),
                    },
                    UdtList = new List<SimpleUdt>
                    {
                        new SimpleUdt
                        {
                            Name = "List Test 1",
                            Number = 1001
                        },
                        new SimpleUdt
                        {
                            Name = "List Test 2",
                            Number = 1002
                        }
                    },
                },
                new()
                {
                    Id = 1,
                    Udt = new TypesTester
                    {
                        String = "Test 2",
                        //Inet = IPAddress.Parse("192.168.0.2"),
                        Int = int.MaxValue,
                        TinyInt = byte.MaxValue,
                        SmallInt = short.MaxValue,
                        BigInt = long.MaxValue,
                        Decimal = decimal.MaxValue,
                        Double = double.MaxValue,
                        Float = float.MaxValue,
                        Boolean = false,
                        UUID = Guid.NewGuid(),
                        Duration = Duration.Parse("12y3mo1d12h30m5s12ms7us2ns"),
                        Timestamp = DateTime.Now,
                        Date = DateOnly.FromDateTime(DateTime.Now),
                        Time = TimeOnly.FromDateTime(DateTime.Now),
                        MaybeTimestamp = null,
                        MaybeDate = DateOnly.FromDateTime(DateTime.Now),
                        MaybeTime = null,
                        TimestampWithKind = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Utc),
                    },
                    UdtList = new List<SimpleUdt>
                    {
                        new SimpleUdt
                        {
                            Name = "List Test 2 dot 1",
                            Number = 2001
                        },
                        new SimpleUdt
                        {
                            Name = "List Test 2 dot 2",
                            Number = 2002
                        }
                    },
                },
                new()
                {
                    Id = 2,
                    Udt = new TypesTester
                    {
                        String = "Test 3",
                        //Inet = IPAddress.Parse("192.168.0.3"),
                        Int = int.MaxValue,
                        TinyInt = byte.MaxValue,
                        SmallInt = short.MaxValue,
                        BigInt = long.MaxValue,
                        Decimal = decimal.MaxValue,
                        Double = double.MaxValue,
                        Float = float.MaxValue,
                        Boolean = false,
                        UUID = Guid.NewGuid(),
                        Duration = Duration.Parse("12y3mo1d12h30m5s12ms7us2ns"),
                        Timestamp = DateTime.Now,
                        Date = DateOnly.FromDateTime(DateTime.Now),
                        MaybeTimestamp = null,
                        MaybeDate = DateOnly.FromDateTime(DateTime.Now),
                        MaybeTime = null,
                        TimestampWithKind = DateTime.SpecifyKind(DateTime.Now, DateTimeKind.Utc),
                    },
                    UdtList = new List<SimpleUdt>
                    {
                        new SimpleUdt
                        {
                            Name = "List Test 3 dot 1",
                            Number = 3001
                        },
                        new SimpleUdt
                        {
                            Name = "List Test 3 dot 2",
                            Number = 3002
                        }
                    },
                },
            };

            var table = await fixture.Database.CreateTableAsync<UdtTest>(tableName, new CreateTableCommandOptions()
            {
                SkipIfExists = true
            });
            var insertResult = await table.InsertManyAsync(items);
            Assert.Equal(items.Count, insertResult.InsertedIds.Count);
            var filter = Builders<UdtTest>.Filter.Eq(b => b.Udt.String, "Test 3");

            // STEO added read-back-and-check
            var myFilter = Builders<UdtTest>.Filter.Eq(b => b.Id, 2);
            var result = await table.FindOneAsync(myFilter);
            Assert.NotNull(result);
            Assert.Equal(2, result.Id);

            // detailed
            Assert.Equal(result.Udt.String, items[2].Udt.String);
            Assert.Equal(result.Udt.Int, items[2].Udt.Int);
            Assert.Equal(result.Udt.TinyInt, items[2].Udt.TinyInt);
            Assert.Equal(result.Udt.SmallInt, items[2].Udt.SmallInt);
            Assert.Equal(result.Udt.BigInt, items[2].Udt.BigInt);
            Assert.Equal(result.Udt.Decimal, items[2].Udt.Decimal);
            Assert.Equal(result.Udt.Double, items[2].Udt.Double);
            Assert.Equal(result.Udt.Float, items[2].Udt.Float);
            Assert.Equal(result.Udt.Boolean, items[2].Udt.Boolean);
            Assert.Equal(result.Udt.UUID, items[2].Udt.UUID);
            Assert.Equal(result.Udt.Duration, items[2].Udt.Duration);
            Assert.Equal(result.Udt.Date, items[2].Udt.Date);
            Assert.Equal(result.Udt.Time, items[2].Udt.Time);
            Assert.Equal(result.Udt.MaybeTimestamp, items[2].Udt.MaybeTimestamp);
            Assert.Equal(result.Udt.MaybeDate, items[2].Udt.MaybeDate);
            Assert.Equal(result.Udt.MaybeTime, items[2].Udt.MaybeTime);
            Assert.Equal(result.Udt.TimestampWithKind, items[2].Udt.TimestampWithKind); // FAILS because of loss of precision with timestamps
            /* The failure looks like this:
                Assert.Equal() Failure: Values differ
                Expected: 2026-03-05T15:22:27.9300000Z
                Actual:   2026-03-05T15:22:27.9301995Z
            */

            Assert.Equal(2, result.UdtList.Count);
            Assert.Equal(
                result.UdtList[1].Name,
                items[2].UdtList[1].Name
            );
            Assert.Equal(
                result.UdtList[1].Number,
                items[2].UdtList[1].Number
            );
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            throw;
        }
        finally
        {
            // await fixture.Database.DropTableAsync(tableName);
            // await fixture.Database.DropTypeAsync<TypesTester>();
            // await fixture.Database.DropTypeAsync<SimpleUdt>();
        }
    }

Note: this Python unit test may come handy for the extreme timestamp strings (e.g. if more than 4 digits, sign is always required...)