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.MySQL;
using ArxSamples.MySQL.Operations.Create.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL 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.MySQL.SampleOutput;

namespace ArxSamples.MySQL.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 and MySQL refuse: default(DateTime), and a date string MySQL can't read.
/// </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 a TIMESTAMP, which MySQL stores as UTC. 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 (TIMESTAMP(6))");

        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 = '+00:00';
         * 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.000000.
         * ===========================================================
         */
    }

    /// <summary>2. Calendar values have their own types. A wall-clock time with no zone (an Unspecified DateTime) goes into a DATETIME, which MySQL never converts.</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["datetime_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", "datetime_value");
        Show($"birthday {(await dbContext.Direct.TextRowAsync("contact", contactId.AsInt64(), "birthday"))["birthday"]}, opens {times["time_value"]}, "
            + $"event {times["datetime_value"]}");

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

    /// <summary>3. default(DateTime) means "not set", and MySQL would store 0001-01-01: 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. ARX never parses strings as dates. MySQL reads a string itself, year first only: '07/01/1992' from a US CSV is no
    /// date to it (ARX4003). Parse it 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"] = "07/01/1992";

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

        fromCsv["birthday"] = DateOnly.ParseExact("07/01/1992", "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture);
        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 (TIMESTAMP(6))
      SO-1001: stored 2026-11-07 10:00:00.000000
      SO-1002: stored 2026-11-07 10:00:00.000000
      SO-1003: stored 2026-11-07 10:00:00.000000
  > 2. A birthday, an opening time, a delivery window and a local event time
      birthday 1990-02-28, opens 09:00:00.000000, event 2026-12-24 18:00:00.000000
  > 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 means "not set" (MySQL's DATETIME starts in the year 1000). Set a real value or null.
  > 4. A date given as a string is refused; DateOnly.ParseExact works
      caught ArxDatabaseException: ARX4003 InvalidValueFormat: Create on 'contact' has a value column 'birthday' can't read: not a valid date or number, or a date outside the column's range.
      stored contact 2