test/Npgsql.Tests/Types/DateTimeTests.cs - main - dev-npgsql/npgsql

test/Npgsql.Tests/Types/DateTimeTests.cs
565 строк25 KB
Nino Floris
Introduce bind operation on PgConverter surface (#6566)
11 май 2026, 20:01
11 май 2026, 20:010f4cfd8
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Npgsql.Internal;
using Npgsql.Internal.Postgres;
using NpgsqlTypes;
using NUnit.Framework;
using static Npgsql.Tests.TestUtil;

namespace Npgsql.Tests.Types;

public class DateTimeTests : TestBase
{
    #region Date

    [Test]
    public Task Date_as_DateOnly()
        => AssertType(new DateOnly(2020, 10, 1), "2020-10-01", "date", dbType: DbType.Date);

    [Test]
    public Task Date_as_DateTime()
        => AssertType(new DateTime(2020, 10, 1), "2020-10-01",
            "date", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.Date, DbType.DateTime2), valueTypeEqualsFieldType: false);

    [Test]
    public Task Date_as_DateTime_with_date_and_time_before_2000()
        => AssertTypeWrite(new DateTime(1980, 10, 1, 11, 0, 0), "1980-10-01",
            "date", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.Date, DbType.DateTime2));

    // Internal PostgreSQL representation (days since 2020-01-01), for out-of-range values.
    [Test]
    public Task Date_as_int()
        => AssertType(7579, "2020-10-01",
            "date", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.Date, DbType.Int32), valueTypeEqualsFieldType: false);

    [Test]
    public Task Daterange_as_NpgsqlRange_of_DateOnly()
        => AssertType(
            new NpgsqlRange<DateOnly>(new(2002, 3, 4), true, new(2002, 3, 6), false),
            "[2002-03-04,2002-03-06)",
            "daterange",
            skipArrayCheck: true); // NpgsqlRange<T>[] is mapped to multirange by default, not array; test separately

    [Test]
    public Task Daterange_array_as_NpgsqlRange_of_DateOnly_array()
        => AssertType(
            new[]
            {
                new NpgsqlRange<DateOnly>(new(2002, 3, 4), true, new(2002, 3, 6), false),
                new NpgsqlRange<DateOnly>(new(2002, 3, 8), true, new(2002, 3, 9), false)
            },
            """{"[2002-03-04,2002-03-06)","[2002-03-08,2002-03-09)"}""",
            "daterange[]", dataTypeInference: DataTypeInference.Mismatch,
            valueTypeEqualsFieldType: false);

    [Test]
    public Task Daterange_as_NpgsqlRange_of_DateTime()
        => AssertType(
            new NpgsqlRange<DateTime>(new(2002, 3, 4), true, new(2002, 3, 6), false),
            "[2002-03-04,2002-03-06)",
            "daterange", dataTypeInference: DataTypeInference.Mismatch,
            valueTypeEqualsFieldType: false);

    [Test]
    public async Task Datemultirange_as_array_of_NpgsqlRange_of_DateOnly()
    {
        await using var conn = await OpenConnectionAsync();
        MinimumPgVersion(conn, "14.0", "Multirange types were introduced in PostgreSQL 14");

        await AssertType(
            new[]
            {
                new NpgsqlRange<DateOnly>(new(2002, 3, 4), true, new(2002, 3, 6), false),
                new NpgsqlRange<DateOnly>(new(2002, 3, 8), true, new(2002, 3, 11), false)
            },
            "{[2002-03-04,2002-03-06),[2002-03-08,2002-03-11)}",
            "datemultirange");
    }

    [Test]
    public async Task Datemultirange_as_array_of_NpgsqlRange_of_DateTime()
    {
        await using var conn = await OpenConnectionAsync();
        MinimumPgVersion(conn, "14.0", "Multirange types were introduced in PostgreSQL 14");

        await AssertType(
            new[]
            {
                new NpgsqlRange<DateTime>(new(2002, 3, 4), true, new(2002, 3, 6), false),
                new NpgsqlRange<DateTime>(new(2002, 3, 8), true, new(2002, 3, 11), false)
            },
            "{[2002-03-04,2002-03-06),[2002-03-08,2002-03-11)}",
            "datemultirange", dataTypeInference: DataTypeInference.Mismatch,
            valueTypeEqualsFieldType: false);
    }

    #endregion

    #region Time

    [Test]
    public Task Time_as_TimeOnly()
        => AssertType(new TimeOnly(10, 45, 34, 500), "10:45:34.5",
            "time without time zone",
            dbType: DbType.Time);

    [Test]
    public Task Time_as_TimeSpan()
        => AssertType(new TimeSpan(0, 10, 45, 34, 500), "10:45:34.5",
            "time without time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.Time, DbType.Object), valueTypeEqualsFieldType: false);

    #endregion

    #region Time with timezone

    static readonly TestCaseData[] TimeTzValues =
    [
        new TestCaseData(new DateTimeOffset(1, 1, 2, 13, 3, 45, 510, TimeSpan.FromHours(2)), "13:03:45.51+02")
            .SetName("Timezone"),
        new TestCaseData(new DateTimeOffset(1, 1, 2, 1, 0, 45, 510, TimeSpan.FromHours(-3)), "01:00:45.51-03")
            .SetName("Negative_timezone"),
        new TestCaseData(new DateTimeOffset(1212720130000, TimeSpan.Zero), "09:41:12.013+00")
            .SetName("Utc"),
        new TestCaseData(new DateTimeOffset(1, 1, 2, 1, 0, 0, new TimeSpan(0, 2, 0, 0)), "01:00:00+02")
            .SetName("Before_utc_zero")
    ];

    [Test, TestCaseSource(nameof(TimeTzValues))]
    public Task TimeTz_as_DateTimeOffset(DateTimeOffset time, string sqlLiteral)
        => AssertType(time, sqlLiteral,
            "time with time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.Object, DbType.DateTime));

    #endregion

    #region Timestamp

    static readonly TestCaseData[] TimestampValues =
    [
        new TestCaseData(new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Unspecified), "1998-04-12 13:26:38")
            .SetName("Timestamp_pre2000"),
        new TestCaseData(new DateTime(2015, 1, 27, 8, 45, 12, 345, DateTimeKind.Unspecified), "2015-01-27 08:45:12.345")
            .SetName("Timestamp_post2000"),
        new TestCaseData(new DateTime(2013, 7, 25, 0, 0, 0, DateTimeKind.Unspecified), "2013-07-25 00:00:00")
            .SetName("Timestamp_date_only")
    ];

    [Test, TestCaseSource(nameof(TimestampValues))]
    public async Task Timestamp_as_DateTime(DateTime dateTime, string sqlLiteral)
    {
        await AssertType(dateTime, sqlLiteral, "timestamp without time zone", dbType: DbType.DateTime2,
            // Explicitly check kind as well.
            comparer: (actual, expected) => actual.Kind == expected.Kind && actual.Equals(expected));

        await AssertType(
            new List<DateTime> { dateTime, dateTime }, $$"""{"{{sqlLiteral}}","{{sqlLiteral}}"}""", "timestamp without time zone[]",
            valueTypeEqualsFieldType: false);
    }

    [Test]
    public Task Timestamp_cannot_write_utc_DateTime()
        => AssertTypeUnsupportedWrite<DateTime, InvalidCastException, ArgumentException>(new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc), "timestamp without time zone");

    [Test]
    public Task Timestamp_as_long()
        => AssertType(-54297202000000, "1998-04-12 13:26:38",
            "timestamp without time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.DateTime2, DbType.Int64), valueTypeEqualsFieldType: false);

    [Test]
    public Task Timestamp_cannot_use_as_DateTimeOffset()
        => AssertTypeUnsupported(
            new DateTimeOffset(1998, 4, 12, 13, 26, 38, TimeSpan.Zero),
            "1998-04-12 13:26:38",
            "timestamp without time zone");

    [Test]
    public Task Tsrange_as_NpgsqlRange_of_DateTime()
        => AssertType(
            new NpgsqlRange<DateTime>(
                new(1998, 4, 12, 13, 26, 38, DateTimeKind.Local),
                new(1998, 4, 12, 15, 26, 38, DateTimeKind.Local)),
            @"[""1998-04-12 13:26:38"",""1998-04-12 15:26:38""]",
            "tsrange",
            skipArrayCheck: true); // NpgsqlRange<T>[] is mapped to multirange by default, not array; test separately

    [Test]
    public Task Tsrange_array_as_NpgsqlRange_of_DateTime_array()
        => AssertType(
            new[]
            {
                new NpgsqlRange<DateTime>(
                    new(1998, 4, 12, 13, 26, 38, DateTimeKind.Local),
                    new(1998, 4, 12, 15, 26, 38, DateTimeKind.Local)),
                new NpgsqlRange<DateTime>(
                    new(1998, 4, 13, 13, 26, 38, DateTimeKind.Local),
                    new(1998, 4, 13, 15, 26, 38, DateTimeKind.Local)),
            },
            """{"[\"1998-04-12 13:26:38\",\"1998-04-12 15:26:38\"]","[\"1998-04-13 13:26:38\",\"1998-04-13 15:26:38\"]"}""",
            "tsrange[]",
            dataTypeInference: DataTypeInference.Mismatch);

    [Test]
    public async Task Tsmultirange_as_array_of_NpgsqlRange_of_DateTime()
    {
        await using var conn = await OpenConnectionAsync();
        MinimumPgVersion(conn, "14.0", "Multirange types were introduced in PostgreSQL 14");

        await AssertType(
            new[]
            {
                new NpgsqlRange<DateTime>(
                    new(1998, 4, 12, 13, 26, 38, DateTimeKind.Local),
                    new(1998, 4, 12, 15, 26, 38, DateTimeKind.Local)),
                new NpgsqlRange<DateTime>(
                    new(1998, 4, 13, 13, 26, 38, DateTimeKind.Local),
                    new(1998, 4, 13, 15, 26, 38, DateTimeKind.Local)),
            },
            @"{[""1998-04-12 13:26:38"",""1998-04-12 15:26:38""],[""1998-04-13 13:26:38"",""1998-04-13 15:26:38""]}",
            "tsmultirange");
    }

    // Pins the structural property the composite fast-path depends on: the registered DateTime
    // providers (timestamp / timestamptz, plus their range and multirange variants) are
    // pgTypeIdClassified, so a resolution with a decided pgTypeId erases the provider and returns
    // a PgConcreteTypeInfo. Composite fields always resolve with a decided pgTypeId from metadata,
    // so this is what makes a DateTime field non-provider-backed. Array compositions over an erased
    // element propagate the erasure via the dual-mapper composer's concrete branch.
    [Test]
    public async Task DateTime_resolution_is_erased_to_concrete_when_pgTypeId_is_decided()
    {
        await using var dataSource = CreateDataSource();
        await using var _ = await dataSource.OpenConnectionAsync();
        var options = dataSource.CurrentReloadableState.SerializerOptions;

        Assert.That(options.GetTypeInfo(typeof(DateTime), DataTypeNames.Timestamp), Is.TypeOf<PgConcreteTypeInfo>());
        Assert.That(options.GetTypeInfo(typeof(DateTime), DataTypeNames.TimestampTz), Is.TypeOf<PgConcreteTypeInfo>());

        var timestampArray = DataTypeNames.Timestamp.ToArrayName();
        Assert.That(options.GetTypeInfo(typeof(DateTime[]), timestampArray), Is.TypeOf<PgConcreteTypeInfo>());
        Assert.That(options.GetTypeInfo(typeof(DateTime?[]), timestampArray), Is.TypeOf<PgConcreteTypeInfo>());
        Assert.That(options.GetTypeInfo(typeof(List<DateTime>), timestampArray), Is.TypeOf<PgConcreteTypeInfo>());

        // No decided pgTypeId — wrapper short-circuits, provider stays alive for late selection.
        Assert.That(options.GetDefaultTypeInfo(typeof(DateTime)), Is.TypeOf<PgProviderTypeInfo>());
        Assert.That(options.GetDefaultTypeInfo(typeof(DateTime[])), Is.TypeOf<PgProviderTypeInfo>());
    }

    #endregion

    #region Timestamp with timezone

    // Note that the below text representations are local (according to TimeZone, which is set to Europe/Berlin in this test class),
    // because that's how PG does timestamptz *text* representation.
    static readonly TestCaseData[] TimestampTzWriteValues =
    [
        new TestCaseData(new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc), "1998-04-12 15:26:38+02")
            .SetName("Timestamptz_write_pre2000"),
        new TestCaseData(new DateTime(2015, 1, 27, 8, 45, 12, 345, DateTimeKind.Utc), "2015-01-27 09:45:12.345+01")
            .SetName("Timestamptz_write_post2000"),
        new TestCaseData(new DateTime(2013, 7, 25, 0, 0, 0, DateTimeKind.Utc), "2013-07-25 02:00:00+02")
            .SetName("Timestamptz_write_date_only")
    ];

    [Test, TestCaseSource(nameof(TimestampTzWriteValues))]
    public async Task Timestamptz_as_DateTime(DateTime dateTime, string sqlLiteral)
    {
        await AssertType(dateTime, sqlLiteral, "timestamp with time zone", dbType: DbType.DateTime,
            // Explicitly check kind as well.
            comparer: (actual, expected) => actual.Kind == expected.Kind && actual.Equals(expected));

        await AssertType(
            new List<DateTime> { dateTime, dateTime }, $$"""{"{{sqlLiteral}}","{{sqlLiteral}}"}""", "timestamp with time zone[]",
            valueTypeEqualsFieldType: false);

    }

    [Test]
    public async Task Timestamptz_infinity_as_DateTime()
    {
        await AssertType(DateTime.MinValue, "-infinity",
            "timestamp with time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.DateTime, DbType.DateTime2));
        await AssertType(DateTime.MaxValue, "infinity",
            "timestamp with time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.DateTime, DbType.DateTime2));
    }

    [Test]
    public async Task Timestamptz_cannot_write_non_utc_DateTime()
    {
        await AssertTypeUnsupportedWrite<DateTime, InvalidCastException, ArgumentException>(new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Unspecified), "timestamp with time zone");
        await AssertTypeUnsupportedWrite<DateTime, InvalidCastException, ArgumentException>(new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Local), "timestamp with time zone");
    }

    [Test]
    public async Task Timestamptz_as_DateTimeOffset_utc()
    {
        var dateTimeOffset = await AssertType(
            new DateTimeOffset(1998, 4, 12, 13, 26, 38, TimeSpan.Zero),
            "1998-04-12 15:26:38+02",
            "timestamp with time zone",
            dbType: DbType.DateTime,
            valueTypeEqualsFieldType: false);

        Assert.That(dateTimeOffset.Offset, Is.EqualTo(TimeSpan.Zero));
    }

    [Test]
    public Task Timestamptz_as_DateTimeOffset_utc_with_DbType_DateTimeOffset()
        => AssertTypeWrite(
            new DateTimeOffset(1998, 4, 12, 13, 26, 38, TimeSpan.Zero),
            "1998-04-12 15:26:38+02",
            "timestamp with time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.DateTime, DbType.DateTime, DbType.DateTimeOffset));

    [Test]
    public Task Timestamptz_cannot_write_non_utc_DateTimeOffset()
        => AssertTypeUnsupportedWrite<DateTimeOffset, InvalidCastException, ArgumentException>(new DateTimeOffset(1998, 4, 12, 13, 26, 38, TimeSpan.FromHours(2)));

    [Test]
    public Task Timestamptz_as_long()
        => AssertType(-54297202000000, "1998-04-12 15:26:38+02",
            "timestamp with time zone", dataTypeInference: DataTypeInference.Mismatch,
            dbType: new(DbType.DateTime, DbType.Int64), valueTypeEqualsFieldType: false);

    [Test]
    public async Task Timestamptz_array_as_DateTimeOffset_array()
    {
        var dateTimeOffsets = await AssertType(
            new[]
            {
                new DateTimeOffset(1998, 4, 12, 13, 26, 38, TimeSpan.Zero),
                new DateTimeOffset(1999, 4, 12, 13, 26, 38, TimeSpan.Zero)
            },
            """{"1998-04-12 15:26:38+02","1999-04-12 15:26:38+02"}""",
            "timestamp with time zone[]",
            valueTypeEqualsFieldType: false);

        Assert.That(dateTimeOffsets[0].Offset, Is.EqualTo(TimeSpan.Zero));
        Assert.That(dateTimeOffsets[1].Offset, Is.EqualTo(TimeSpan.Zero));
    }

    [Test]
    public Task Tstzrange_as_NpgsqlRange_of_DateTime()
        => AssertType(
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            @"[""1998-04-12 15:26:38+02"",""1998-04-12 17:26:38+02""]",
            "tstzrange",
            skipArrayCheck: true); // NpgsqlRange<T>[] is mapped to multirange by default, not array; test separately

    [Test]
    public Task Tstzrange_array_as_NpgsqlRange_of_DateTime_array()
        => AssertType(
            new[]
            {
                new NpgsqlRange<DateTime>(
                    new(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                    new(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
                new NpgsqlRange<DateTime>(
                    new(1998, 4, 13, 13, 26, 38, DateTimeKind.Utc),
                    new(1998, 4, 13, 15, 26, 38, DateTimeKind.Utc)),
            },
            """{"[\"1998-04-12 15:26:38+02\",\"1998-04-12 17:26:38+02\"]","[\"1998-04-13 15:26:38+02\",\"1998-04-13 17:26:38+02\"]"}""",
            "tstzrange[]",
            dataTypeInference: DataTypeInference.Mismatch);

    [Test]
    public async Task Tstzmultirange_as_array_of_NpgsqlRange_of_DateTime()
    {
        await using var conn = await OpenConnectionAsync();
        MinimumPgVersion(conn, "14.0", "Multirange types were introduced in PostgreSQL 14");

        await AssertType(
            new[]
            {
                new NpgsqlRange<DateTime>(
                    new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                    new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
                new NpgsqlRange<DateTime>(
                    new DateTime(1998, 4, 13, 13, 26, 38, DateTimeKind.Utc),
                    new DateTime(1998, 4, 13, 15, 26, 38, DateTimeKind.Utc)),
            },
            @"{[""1998-04-12 15:26:38+02"",""1998-04-12 17:26:38+02""],[""1998-04-13 15:26:38+02"",""1998-04-13 17:26:38+02""]}",
            "tstzmultirange");
    }

    [Test]
    public Task Cannot_mix_DateTime_Kinds_in_array()
        => AssertTypeUnsupportedWrite<DateTime[], InvalidCastException, ArgumentException>([
            new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
            new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Local)
        ]);


    [Test]
    public Task Cannot_mix_DateTime_Kinds_in_range()
        => AssertTypeUnsupportedWrite<NpgsqlRange<DateTime>, InvalidCastException, ArgumentException>(new NpgsqlRange<DateTime>(
            new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
            new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Local)));

    [Test]
    public async Task Cannot_mix_DateTime_Kinds_in_multirange()
    {
        await using var conn = await OpenConnectionAsync();
        MinimumPgVersion(conn, "14.0", "Multirange types were introduced in PostgreSQL 14");

        await AssertTypeUnsupportedWrite<NpgsqlRange<DateTime>[], InvalidCastException, ArgumentException>([
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                new DateTime(1998, 4, 12, 15, 26, 38, DateTimeKind.Utc)),
            new NpgsqlRange<DateTime>(
                new DateTime(1998, 4, 13, 13, 26, 38, DateTimeKind.Local),
                new DateTime(1998, 4, 13, 15, 26, 38, DateTimeKind.Local))
        ]);
    }

    [Test]
    public void NpgsqlParameterDbType_is_value_dependent_datetime_or_datetime2()
    {
        var localtimestamp = new NpgsqlParameter { Value = DateTime.Now };
        var unspecifiedtimestamp = new NpgsqlParameter { Value = new DateTime() };
        Assert.That(localtimestamp.DbType, Is.EqualTo(DbType.DateTime2));
        Assert.That(unspecifiedtimestamp.DbType, Is.EqualTo(DbType.DateTime2));

        // We don't support any DateTimeOffset other than offset 0 which maps to timestamptz,
        // we might add an exception for offset == DateTimeOffset.Now.Offset (local offset) mapping to timestamp at some point.
        // var dtotimestamp = new NpgsqlParameter { Value = DateTimeOffset.Now };
        // Assert.AreEqual(DbType.DateTime2, dtotimestamp.DbType);

        var timestamptz = new NpgsqlParameter { Value = DateTime.UtcNow };
        var dtotimestamptz = new NpgsqlParameter { Value = DateTimeOffset.UtcNow };
        Assert.That(timestamptz.DbType, Is.EqualTo(DbType.DateTime));
        Assert.That(dtotimestamptz.DbType, Is.EqualTo(DbType.DateTime));
    }

    [Test]
    public void NpgsqlParameterNpgsqlDbType_is_value_dependent_timestamp_or_timestamptz()
    {
        var localtimestamp = new NpgsqlParameter { Value = DateTime.Now };
        var unspecifiedtimestamp = new NpgsqlParameter { Value = new DateTime() };
        Assert.That(localtimestamp.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
        Assert.That(unspecifiedtimestamp.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));

        var timestamptz = new NpgsqlParameter { Value = DateTime.UtcNow };
        var dtotimestamptz = new NpgsqlParameter { Value = DateTimeOffset.UtcNow };
        Assert.That(timestamptz.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.TimestampTz));
        Assert.That(dtotimestamptz.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.TimestampTz));
    }

    [Test]
    public async Task Array_of_nullable_timestamptz()
    {
        await using var datasource = CreateDataSource(csb =>
        {
            csb.ArrayNullabilityMode = ArrayNullabilityMode.PerInstance;
            csb.Timezone = "Europe/Berlin";
        });
        await AssertType(datasource,
            new DateTime?[]
            {
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
                null
            },
            @"{""1998-04-12 15:26:38+02"",NULL}",
            "timestamp with time zone[]");

        // Make sure delayed converter resolution works when null precedes a non-null value.
        // We expect the resolution of null values to not lock in the default type timestamp.
        // This would cause the subsequent non-null value to fail to convert, as it requires timestamptz.
        await AssertType(datasource,
            new DateTime?[]
            {
                null,
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc)
            },
            @"{NULL,""1998-04-12 15:26:38+02""}",
            "timestamp with time zone[]");

        await AssertType(datasource,
            new DateTime?[]
            {
                new DateTime(1998, 4, 12, 13, 26, 38, DateTimeKind.Utc),
            },
            @"{""1998-04-12 15:26:38+02""}",
            "timestamp with time zone[]", valueTypeEqualsFieldType: false); // we write DateTime?[], but will read DateTime[] from GetValue
    }

    #endregion

    #region Interval

    static readonly TestCaseData[] IntervalValues =
    [
        new TestCaseData(new TimeSpan(0, 2, 3, 4, 5), "02:03:04.005")
            .SetName("Interval_time_only"),
        new TestCaseData(new TimeSpan(1, 2, 3, 4, 5), "1 day 02:03:04.005")
            .SetName("Interval_with_day"),
        new TestCaseData(new TimeSpan(61, 2, 3, 4, 5), "61 days 02:03:04.005")
            .SetName("Interval_with_many_days"),
        new TestCaseData(new TimeSpan(new TimeSpan(2, 3, 4).Ticks + 10), "02:03:04.000001")
            .SetName("Interval_with_microsecond")
    ];

    [Test, TestCaseSource(nameof(IntervalValues))]
    public Task Interval_as_TimeSpan(TimeSpan timeSpan, string sqlLiteral)
        => AssertType(timeSpan, sqlLiteral, "interval");

    [Test]
    public Task Interval_write_as_TimeSpan_truncates_ticks()
        => AssertTypeWrite(
            new TimeSpan(new TimeSpan(2, 3, 4).Ticks + 1),
            "02:03:04",
            "interval");

    [Test]
    public Task Interval_as_NpgsqlInterval()
        => AssertType(
            new NpgsqlInterval(2, 15, 7384005000),
            "2 mons 15 days 02:03:04.005", "interval", valueTypeEqualsFieldType: false);

    [Test]
    public Task Interval_with_months_cannot_read_as_TimeSpan()
        => AssertTypeUnsupportedRead<TimeSpan, InvalidCastException>("1 month 2 days", "interval");

    #endregion

    protected override async ValueTask<NpgsqlConnection> OpenConnectionAsync()
    {
        var conn = await base.OpenConnectionAsync();
        await conn.ExecuteNonQueryAsync("SET TimeZone='Europe/Berlin'");
        return conn;
    }

    protected override NpgsqlConnection OpenConnection()
        => throw new NotSupportedException();
}