Complex expression to check if dates are not equal

[bandtank]

There are two datetime fields in my entity called dateStart and dateStartBill. I need an expression that returns true if the days are not equal. For example, if dateStart is 2026-06-01 and dateStartBill is 2026-06-02, the expression should return true. Here is what I have so far:

OR:(
    NOT_EQUAL:(
        DAYOFWEEK:(TZ:(dateStart, "America/Denver")),
        DAYOFWEEK:(TZ:(dateStartBill, "America/Denver"))
    ),
    NOT_EQUAL:(
        DAYOFWEEK:(TZ:(dateEnd, "America/Denver")),
        DAYOFWEEK:(TZ:(dateEndBill, "America/Denver"))
    )
)

Unfortunately, that does not work because too many results appear that should not match the expression. Timezone awareness made the results better, but it is still incorrect:



This is the query I am trying to reproduce:

SELECT
  id, date_start, date_start_bill, date_end, date_end_bill
FROM
  session
WHERE (
  DATE(CONVERT_TZ(date_start, 'UTC', 'America/Denver')) <>
    DATE(CONVERT_TZ(date_start_bill, 'UTC', 'America/Denver'))

  OR

  DATE(CONVERT_TZ(date_end, 'UTC', 'America/Denver')) <>
    DATE(CONVERT_TZ(date_end_bill, 'UTC', 'America/Denver'))
)

which works well:

[yuri]

Named time zones are not supported, only offset values supported. Though it is possible to add a custom function: https://docs.espocrm.com/user-guide/...stom-functions.

[bandtank]

I couldn't get the expression to work. I built a custom report instead. Unfortunately, I had to use a fixed timezone offset, so this is likely to be wrong in rare situations.

custom/Espo/Modules/Odyssey/Reports/SessionDatesNotEqualBillingDates.php

<?php
namespace Espo\Modules\Odyssey\Reports;

use Espo\Core\Select\SelectBuilderFactory;
use Espo\Core\Select\SearchParams;
use Espo\Entities\User;
use Espo\ORM\EntityCollection;
use Espo\ORM\EntityManager;
use Espo\ORM\Query\Part\Expression as Expr;
use Espo\ORM\Query\Select as Query;
use Espo\ORM\Query\SelectBuilder as QueryBuilder;

use Espo\Modules\Advanced\Reports\ListReport;
use Espo\Modules\Advanced\Tools\Report\ListType\Result;

use Espo\Modules\Odyssey\Entities\Session as CSession;

class SessionDatesNotEqualBillingDates implements ListReport
{
  public function __construct(
    private EntityManager $entityManager,
    private SelectBuilderFactory $selectBuilderFactory,
  ) {}

  public function run(?SearchParams $searchParams, ?User $user): Result
  {
    $sessions = $this->getSessions($searchParams, $user);
    $count = $this->getCount($searchParams, $user);

    return new Result($sessions, $count);
  }

  private function getCount(?SearchParams $searchParams, ?User $user): int
  {
    $searchParams = $searchParams->withMaxSize(null);

    $queryBuilder = $this->getQueryBuilder($searchParams, $user);
    $query = $queryBuilder->build();

    return $this->entityManager
      ->getRDBRepository(CSession::ENTITY_TYPE)
      ->clone($query)
      ->count();
  }

  private function getSessions(?SearchParams $searchParams, ?User $user): EntityCollection
  {
    $queryBuilder = $this->getQueryBuilder($searchParams, $user);
    $queryBuilder->order('dateStart', 'ASC');
    $query = $queryBuilder->build();

    return $this->entityManager
      ->getRDBRepository(CSession::ENTITY_TYPE)
      ->clone($query)
      ->find();
  }

  private function getQueryBuilder(?SearchParams $searchParams, ?User $user): QueryBuilder
  {
    $selectBuilder = $this->selectBuilderFactory
      ->create()
      ->from(CSession::ENTITY_TYPE)
      ->withStrictAccessControl()
      ->withSearchParams($searchParams);
      ;

    if($user)
      $selectBuilder->forUser($user);

    $queryBuilder = $selectBuilder->buildQueryBuilder();

    $queryBuilder->where(
      Expr::or(
        Expr::notEqual(
          Expr::date(Expr::convertTimezone(Expr::column('dateStart'), -6)),
          Expr::date(Expr::convertTimezone(Expr::column('dateStartBill'), -6))
        ),
        Expr::notEqual(
          Expr::date(Expr::convertTimezone(Expr::column('dateEnd'), -6)),
          Expr::date(Expr::convertTimezone(Expr::column('dateEndBill'), -6))
        )
      )
    );

    return $queryBuilder;
  }

custom/Espo/Modules/Odyssey/Resources/metadata/app/advancedReport.json

{
    "internalReports": {
        "Odyssey:SessionDatesNotEqualBillingDates": {
            "className": "Espo\\Modules\\Odyssey\\Reports\\SessionDatesNotEqualBillingDates",
            "entityType": "Session",
            "type": "List",
            "runtimeFilters": [
              "dateStart",
              "dateStartBill",
              "dateEnd",
              "dateEndBill",
              "type",
              "account"
            ],
            "columns": [
              "name",
              "dateStart",
              "dateStartBill",
              "dateEnd",
              "dateEndBill"
            ]
        }
    }
}

custom/Espo/Modules/Odyssey/Resources/i18n/en_US/Report.json

{
    "internalReports": {
        "Odyssey:SessionDatesNotEqualBillingDates": "Session Dates Not Equal Billing Dates"
    }
}

[bandtank]

For anyone who may be interested, it is possible to do this directly with SQL, which avoids the timezone issue. More would need to be done to use the search parameters, but the important parts work.

  private function getSessionsSql(?SearchParams $searchParams, ?User $user): EntityCollection {
    $sql = "
      SELECT
          id
        , deleted
        , name
        , date_start
        , date_start_bill
        , date_end, date_end_bill

      FROM
        session

      WHERE
        (
          DATE(CONVERT_TZ(date_start, 'UTC', 'America/Denver'))
            <> DATE(CONVERT_TZ(date_start_bill, 'UTC', 'America/Denver'))

          OR

          DATE(CONVERT_TZ(date_end, 'UTC', 'America/Denver'))
            <> DATE(CONVERT_TZ(date_end_bill, 'UTC', 'America/Denver'))
        )

      ORDER BY
        date_start
        ";

    $pdo = $this->entityManager->getPDO();
    $sth = $pdo->prepare($sql);
    $sth->execute();

    $ids = array();
    foreach($sth->fetchAll() as $row)
      $ids[] = $row["id"];

    return $this->entityManager->getRDBRepository(CSession::ENTITY_TYPE)
      ->where(array(
        "id" => $ids,
      ))
      ->find();
  } 

[bandtank]

I eventually got the expression to work, but it is not as good as the custom report. There does not appear to be a date function, so it may not be possible to compare the full dates. There are only DAYOFWEEK and DAYOFMONTH functions. For my use case, that's probably good enough, but if dateStart = 2026-01-01 and dateStartBill = 2026-02-01, the record would not match even though it should.

OR:(
    NOT_EQUAL:(
        DAYOFMONTH:(TZ:(dateStart, -6)),
        DAYOFMONTH:(TZ:(dateStartBill, -6))
    ),
    NOT_EQUAL:(
        DAYOFMONTH:(TZ:(dateEnd, -6)),
        DAYOFMONTH:(TZ:(dateEndBill, -6))
    )
)