Instants, local times, dates and times of day

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Create.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_POSTGRESQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_POSTGRESQL environment variable to your PostgreSQL connection string.");

await new RealCase3_DatesAndTimes().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Services;
using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Create.RealCases;

/// <summary>
/// Dates and times, in four scenarios: an order placed in Manila written three ways (instants
/// are stored as UTC), calendar values (a birthday, an opening time, a delivery window, a local event time), and what
/// ARX refuses: default(DateTime) and a date given as a string.
/// </summary>
public sealed class RealCase3_DatesAndTimes : ISample
{
    public string Name => "create-real-3-dates-and-times";

    public string Title => "Instants, local times, dates and times of day";

    public async Task RunAsync(SampleContext dbContext)
    {
        // The customer the orders and the contact belong to.
        var account = new ArxEntity("account");
        account["name"] = "Tailspin Toys";
        var accountId = await dbContext.Service.CreateAsync(account);

        await Scenario1_TheSameInstantThreeWaysAsync(dbContext, accountId);

        await Scenario2_CalendarValuesAsync(dbContext, accountId);

        await Scenario3_DefaultDateTimeIsRefusedAsync(dbContext, accountId);

        await Scenario4_ADateGivenAsAStringIsRefusedAsync(dbContext);
    }

    /// <summary>1. An instant goes into timestamptz. These three orders were placed at the same moment, written three ways.</summary>
    private static async Task Scenario1_TheSameInstantThreeWaysAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. The same instant written three ways into sales_order.ordered_on (timestamptz)");

        var fromOffset = new ArxEntity("sales_order");
        fromOffset["order_number"] = "SO-1001";
        fromOffset["account_id"] = new ArxEntityReference("account", accountId);
        fromOffset["ordered_on"] = new DateTimeOffset(2026, 11, 7, 18, 0, 0, TimeSpan.FromHours(8));            // Manila time

        var fromUtc = new ArxEntity("sales_order");
        fromUtc["order_number"] = "SO-1002";
        fromUtc["account_id"] = new ArxEntityReference("account", accountId);
        fromUtc["ordered_on"] = new DateTime(2026, 11, 7, 10, 0, 0, DateTimeKind.Utc);

        var fromLocal = new ArxEntity("sales_order");
        fromLocal["order_number"] = "SO-1003";
        fromLocal["account_id"] = new ArxEntityReference("account", accountId);
        fromLocal["ordered_on"] = new DateTime(2026, 11, 7, 10, 0, 0, DateTimeKind.Utc).ToLocalTime();          // ARX converts Local to UTC

        var orders = new List<ArxEntity> { fromOffset, fromUtc, fromLocal };
        var ids = await dbContext.Service.CreateMultipleAsync(orders);

        for (var i = 0; i < orders.Count; i++)
        {
            var stored = await dbContext.Direct.TextRowAsync("sales_order", ids[i].AsInt64(), "ordered_on");
            Show($"{orders[i]["order_number"]}: stored {stored["ordered_on"]}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SET TIME ZONE 'UTC';
         * SELECT order_number, ordered_on
         * FROM sales_order
         * WHERE order_number IN ('SO-1001', 'SO-1002', 'SO-1003')
         * ORDER BY order_number;
         * -- Expect: 3 rows, all 2026-11-07 10:00:00+00.
         * ===========================================================
         */
    }

    /// <summary>2. Calendar values have their own types. A wall-clock time with no zone (an Unspecified DateTime) goes into timestamp.</summary>
    private static async Task Scenario2_CalendarValuesAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. A birthday, an opening time, a delivery window and a local event time");

        var contact = new ArxEntity("contact");
        contact["account_id"] = new ArxEntityReference("account", accountId);
        contact["first_name"] = "Maria";
        contact["last_name"] = "Santos";
        contact["birthday"] = new DateOnly(1990, 2, 28);
        var contactId = await dbContext.Service.CreateAsync(contact);

        var schedule = new ArxEntity("data_type_showcase");
        schedule["time_value"] = new TimeOnly(9, 0);
        schedule["interval_value"] = TimeSpan.FromMinutes(90);
        schedule["timestamp_value"] = new DateTime(2026, 12, 24, 18, 0, 0, DateTimeKind.Unspecified);
        var scheduleId = await dbContext.Service.CreateAsync(schedule);

        var times = await dbContext.Direct.TextRowAsync("data_type_showcase", scheduleId.AsInt64(), "time_value", "interval_value", "timestamp_value");
        Show($"birthday {(await dbContext.Direct.TextRowAsync("contact", contactId.AsInt64(), "birthday"))["birthday"]}, opens {times["time_value"]}, "
            + $"window {times["interval_value"]}, event {times["timestamp_value"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, birthday
         * FROM contact
         * WHERE first_name = 'Maria' AND last_name = 'Santos';
         *
         * SELECT time_value, interval_value, timestamp_value
         * FROM data_type_showcase
         * WHERE timestamp_value = '2026-12-24 18:00:00';
         * -- Expect: birthday 1990-02-28; 09:00:00, 01:30:00, 2026-12-24 18:00:00.
         * ===========================================================
         */
    }

    /// <summary>3. default(DateTime) means "not set", and PostgreSQL would store -infinity: refused before anything is sent.</summary>
    private static async Task Scenario3_DefaultDateTimeIsRefusedAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. default(DateTime) is refused: use null for \"no value\"");

        var unset = new ArxEntity("sales_order");
        unset["order_number"] = "SO-1004";
        unset["account_id"] = new ArxEntityReference("account", accountId);
        unset["ordered_on"] = default(DateTime);

        try
        {
            await dbContext.Service.CreateAsync(unset);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidDateTimeValue)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM sales_order
         * WHERE order_number = 'SO-1004';
         * -- Expect: 0: default(DateTime) was refused before anything was sent.
         * ===========================================================
         */
    }

    /// <summary>4. Strings are never parsed as dates. Parse them yourself, with the format you mean.</summary>
    private static async Task Scenario4_ADateGivenAsAStringIsRefusedAsync(SampleContext dbContext)
    {
        Step("4. A date given as a string is refused; DateOnly.ParseExact works");

        var fromCsv = new ArxEntity("contact");
        fromCsv["first_name"] = "Leo";
        fromCsv["last_name"] = "Park";
        fromCsv["birthday"] = "1992-07-01";

        try
        {
            await dbContext.Service.CreateAsync(fromCsv);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.DataTypeMismatch)
        {
            ShowError(error);
        }

        fromCsv["birthday"] = DateOnly.ParseExact("1992-07-01", "yyyy-MM-dd");
        var id = await dbContext.Service.CreateAsync(fromCsv);
        Show($"stored contact {id}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, birthday
         * FROM contact
         * WHERE first_name = 'Leo' AND last_name = 'Park';
         * -- Expect: 1 row, birthday 1992-07-01 (the second attempt, with DateOnly.ParseExact).
         * ===========================================================
         */
    }
}

  > 1. The same instant written three ways into sales_order.ordered_on (timestamptz)
      SO-1001: stored 2026-11-07 10:00:00+00
      SO-1002: stored 2026-11-07 10:00:00+00
      SO-1003: stored 2026-11-07 10:00:00+00
  > 2. A birthday, an opening time, a delivery window and a local event time
      birthday 1990-02-28, opens 09:00:00, window 01:30:00, event 2026-12-24 18:00:00
  > 3. default(DateTime) is refused: use null for "no value"
      caught ArxValidationException: ARX1013 InvalidDateTimeValue: Attribute 'ordered_on' on 'sales_order' is default(DateTime) (DateTime.MinValue), which PostgreSQL would store as -infinity. Set a real value, null, or DateTime.SpecifyKind(DateTime.MinValue, DateTimeKind.Utc) to mean -infinity.
  > 4. A date given as a string is refused; DateOnly.ParseExact works
      caught ArxDatabaseException: ARX4004 DataTypeMismatch: Create on 'contact' has a value of the wrong type: column "birthday" is of type date but expression is of type text.
      stored contact 2