Wrong databricks time range filters.

[hienphdev]

Describe the bug

Cube.js is emitting an inclusive, UTC-conversion-wrapped end-date filter that doesn’t match the user’s configured range. Instead of generating a clean half-open or matching inclusive bound, it always wraps both ends in from_utc_timestamp(replace(…),'UTC') and uses an inclusive <= on the last millisecond of the next period.

---

To Reproduce

1. Define a Cube.js measure over a time dimension:

   cube(`Orders`, {
     sql: `
       SELECT
         id,
         amount,
         status,
         transaction_timestamp
       FROM some_table
     `,
     measures: {
       netSales: { sql: `amount`, type: `sum` }
     },
     dimensions: {
       transactionTimestamp: { sql: `transaction_timestamp`, type: `time` }
     }
   });

2. Issue a query for January 2023:

SELECT
  DATE_TRUNC('MONTH', transaction_timestamp) AS dt,
  MEASURE(Orders.netSales)              AS "Net Sales"
FROM Orders
WHERE Orders.transactionTimestamp >= '2023-01-01'
  AND Orders.transactionTimestamp <  '2023-02-01'
GROUP BY 1
ORDER BY 1 DESC;

3. Inspect the generated SQL in the query inspector or logs. You’ll see:

SELECT
      date_trunc('month',
        from_utc_timestamp(`Orders`.transaction_timestamp,'UTC')
      )                             AS Orders__transaction_timestamp_month,
      SUM(net_sales)                AS Orders__net_sales
FROM <CATALOG>.<DATASET>.<TABLE> AS `Orders`
WHERE (
  Orders.transaction_timestamp >=
    from_utc_timestamp(
      replace(replace('2023-01-01T00:00:00.000Z','T',' '),'Z',''),
      'UTC'
    )
  AND Orders.transaction_timestamp <=
    from_utc_timestamp(
      replace(replace('2023-02-01T23:59:59.999Z','T',' '),'Z',''),
      'UTC'
    )
)
GROUP BY 1
ORDER BY 1 DESC
LIMIT 50000

4. Note that the upper bound includes February 1st 23:59:59.999Z (the next period) rather than ending at January 31st.

Cube Version

v1.3.18

[igorlukanin]

I was able to reproduce this with 1.3.23. Running the following query...

SELECT
  DATE_TRUNC('MONTH', transactionTimestamp) AS dt,
  MEASURE(Orders.netSales) AS "Net Sales"
FROM
  Orders
WHERE
  Orders.transactionTimestamp >= '2023-01-01'
  AND Orders.transactionTimestamp < '2023-02-01'
GROUP BY
  1
ORDER BY
  1 DESC;

...generates the following SQL with <= 2023-02-01T23:59:59.999Z, which looks incorrect:

SELECT
  date_trunc(
    'month',
    from_utc_timestamp(`orders`.transaction_timestamp, 'UTC')
  ) `orders__transaction_timestamp_month`,
  sum(`orders`.amount) `orders__net_sales`
FROM
  (
    SELECT
      id,
      id AS amount,
      status,
      created_at AS transaction_timestamp
    FROM
      ECOM.ORDERS
  ) AS `orders`
WHERE
  (
    `orders`.transaction_timestamp >= from_utc_timestamp(
      replace(
        replace('2023-01-01T00:00:00.000Z', 'T', ' '),
        'Z',
        ''
      ),
      'UTC'
    )
    AND `orders`.transaction_timestamp <= from_utc_timestamp(
      replace(
        replace('2023-02-01T23:59:59.999Z', 'T', ' '),
        'Z',
        ''
      ),
      'UTC'
    )
  )
GROUP BY
  1
ORDER BY
  1 DESC
LIMIT
  50000