Skip to content

Get database system date

This operation provides a database-sourced "now" for consumers that need the server's clock rather than the application server's clock — for example, for audit timestamps.

Field Value
Operation ID OP-NH-12
Trigger Consumer reads IRepository<TEntity>.SystemDate (which reads ISessionHelper.SystemDate)
Inputs —
Outputs A DateTime from the database server, or the application server's local time as a fallback
Primary components SessionHelperBase.SystemDate, OracleSessionHelper, SqlServerSessionHelper, PostgreSqlSessionHelper
Entry point src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/SessionHelperBase.cs

Steps

  1. SessionHelperBase.SystemDate runs Session.CreateSqlQuery(DateSql).UniqueResult() every time it is read — there is no caching (BR-NH-17)
  2. DateSql is set by the concrete subclass constructor: SQL-001 for OracleSessionHelper, SQL-002 for PostgreSqlSessionHelper, SQL-003 for SqlServerSessionHelper.
  3. Convert the raw query result with Convert.ToDateTime(sysDate).
  4. If the converted result equals DateTime.MinValue or DateTime.MaxValue: return DateTime.Now (application server local time) instead (BR-NH-18)
  5. Otherwise: return the converted database result.

PostgreSQL caveat: PostgreSqlSessionHelper.DateSql is Select current_date, which returns a DATE-only value (midnight, no time-of-day component) — unlike Oracle's sysdate and SQL Server's GetDate(), which both return full date+time. Consumers relying on SystemDate for audit timestamps that need time-of-day precision should not use the PostgreSQL engine helper for that purpose.

Note: SessionHelperBase also exposes a UseDatabaseForSystemDate property, set in the constructor but never read anywhere else in the class or in the SystemDate getter — it is a confirmed-unused flag, not merely suspected to be unused.

The fallback is narrowly scoped: it triggers only when Convert.ToDateTime(sysDate) succeeds and the resulting value equals exactly DateTime.MinValue or DateTime.MaxValue. A failed conversion (for example a malformed or unexpected query result) or a query/connectivity exception is not caught anywhere in SystemDate and propagates to the caller unhandled.

Process flow

The diagram below visualizes the query/conversion steps and the sentinel-value branch that decides between the database result and the local-time fallback.

flowchart TD
    Start(["SystemDate read"]) --> RunQuery["CreateSqlQuery(DateSql).UniqueResult()"]
    RunQuery --> Convert["Convert.ToDateTime(result)"]
    Convert --> Sentinel{"MinValue or<br>MaxValue?"}
    Sentinel -->|Yes| Fallback(["Return DateTime.Now"])
    Sentinel -->|No| ReturnDb(["Return database result"])

Failure and edge-case behavior

  • A database connectivity failure during the query surfaces as whatever exception NHibernate/the ADO.NET driver raises; there is no try/catch around the query itself.
  • A NULL/sentinel result from the database is masked by the local-time fallback rather than raised as an error, which can hide real data problems.*
  • The UseDatabaseForSystemDate property is confirmed dead/unused state in the current implementation: it is assigned in the constructor but never read by SystemDate or anywhere else in the class.
  • On PostgreSQL specifically, when the query returns a successful, non-sentinel result, SystemDate returns midnight (date-only); this is a property of the current_date SQL expression's successful result, not of the fallback logic. This "always midnight" characterization describes only that successful-result path — it does not describe the sentinel-fallback path (step 3 above), which instead returns DateTime.Now (the application server's local time, including time-of-day), nor the unhandled-exception path for a query/conversion failure.

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources

  • src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/SessionHelperBase.cs
  • src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/OracleSessionHelper.cs
  • src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/SQLSessionHelper.cs
  • src/SAIF.NetCore.Nhibernate/SAIF.NetCore.NHibernate/SAIF.NetCore.NHibernate/SessionHelpers/PostgreSqlSessionHelper.cs