Dates and times in conditions

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.RetrieveMultiple.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 RealCase4_DatesInConditions().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.Models;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// Dates in conditions, in three scenarios: a date column against a <c>DateOnly</c>; a <c>TIMESTAMP</c> column against
/// a UTC moment; and "on this day" for a moment, comparing only its date part. No hidden offset is added.
/// </summary>
public sealed class RealCase4_DatesInConditions : ISample
{
    public string Name => "retrieve-multiple-real-4-dates-in-conditions";

    public string Title => "Dates and times in conditions";

    public async Task RunAsync(SampleContext dbContext)
    {
        var accounts = new List<ArxEntity>();

        foreach (var (number, founded) in new[] { ("ACC-R651", new DateOnly(1999, 12, 31)), ("ACC-R652", new DateOnly(2000, 1, 1)), ("ACC-R653", new DateOnly(2010, 6, 15)) })
        {
            var account = new ArxEntity("account");
            account["name"] = "Dated " + number;
            account["account_number"] = number;
            account["founded_on"] = founded;
            account["modified_on"] = new DateTime(2026, 2, 1, 23, 30, 0, DateTimeKind.Utc).AddDays(int.Parse(number[^1..]) - 1);
            accounts.Add(account);
        }

        await dbContext.Service.CreateMultipleAsync(accounts);

        await Scenario1_ADateColumnAsync(dbContext);

        await Scenario2_AMomentInUtcAsync(dbContext);

        await Scenario3_OnThisDayAsync(dbContext);
    }

    private static async Task<string> NumbersAsync(SampleContext dbContext, string attribute, ArxConditionOperator op, object value)
    {
        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumn("account_number");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R65");
        query.Criteria.AddCondition(attribute, op, value);
        query.AddOrder("account_number", ArxOrderType.Ascending);
        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        return string.Join(", ", rows.Entities.Select(x => x["account_number"]));
    }

    /// <summary>1. Founded in 2000 or later: a date column and a DateOnly value.</summary>
    private static async Task Scenario1_ADateColumnAsync(SampleContext dbContext)
    {
        Step("1. Founded on or after 1 January 2000");
        Show(await NumbersAsync(dbContext, "founded_on", ArxConditionOperator.GreaterEqual, new DateOnly(2000, 1, 1)));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number
         * FROM account
         * WHERE account_number LIKE 'ACC-R65%' AND founded_on >= '2000-01-01'
         * ORDER BY account_number;
         * -- Expect: 2 rows, ACC-R652, ACC-R653.
         * ===========================================================
         */
    }

    /// <summary>2. Changed after a UTC moment: a TIMESTAMP column and a UTC DateTime, compared to the second.</summary>
    private static async Task Scenario2_AMomentInUtcAsync(SampleContext dbContext)
    {
        Step("2. Changed after 2 February 2026, 23:00 UTC");
        Show(await NumbersAsync(dbContext, "modified_on", ArxConditionOperator.GreaterThan, new DateTime(2026, 2, 2, 23, 0, 0, DateTimeKind.Utc)));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number
         * FROM account
         * WHERE account_number LIKE 'ACC-R65%' AND modified_on > TIMESTAMP '2026-02-02 23:00:00+00:00'
         * ORDER BY account_number;
         * -- Expect: 2 rows, ACC-R652 (23:30 on the 2nd), ACC-R653.
         * ===========================================================
         */
    }

    /// <summary>3. Changed on 1 February: only the date part of the moment is compared.</summary>
    private static async Task Scenario3_OnThisDayAsync(SampleContext dbContext)
    {
        Step("3. Changed on 1 February 2026 (any time that day)");
        var onTheDay = new ArxDateTimeConditionOption { Value = new DateTime(2026, 2, 1), ComparingType = ArxDateTimeConditionOptionType.DateOnly };
        Show(await NumbersAsync(dbContext, "modified_on", ArxConditionOperator.Equal, onTheDay));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number
         * FROM account
         * WHERE account_number LIKE 'ACC-R65%' AND CAST(modified_on AS date) = '2026-02-01'
         * ORDER BY account_number;
         * -- Expect: 1 row, ACC-R651 (ARX connections run in UTC).
         * ===========================================================
         */
    }
}

  > 1. Founded on or after 1 January 2000
      ACC-R652, ACC-R653
  > 2. Changed after 2 February 2026, 23:00 UTC
      ACC-R652, ACC-R653
  > 3. Changed on 1 February 2026 (any time that day)
      ACC-R651