Rollup Lambda wrong sql query when using timezone

[ernriveron]

Describe the bug
Incorrect SQL query in Rollup Lambda when using time zone. The Lambda query range is calculated incorrectly.

To Reproduce

Enviroment

CUBEJS_SCHEDULED_REFRESH_TIMEZONES=Europe/Madrid

Test Cube

cube(`Test`, {
    dataSource: `Test`,
    sql: `
        select '2025-01-15T10:23:45.000Z'::timestamp(3) as "createdAt", '1' as id
        union all
        select '2025-02-05T14:12:30.000Z'::timestamp(3) as "createdAt", '2' as id
        union all         
        select '2025-02-25T08:45:10.000Z'::timestamp(3) as "createdAt", '3' as id
        union all
        select '2025-03-10T11:34:56.000Z'::timestamp(3) as "createdAt", '4' as id
        union all
        select '2025-03-25T19:05:15.000Z'::timestamp(3) as "createdAt", '5' as id
        union all
        select '2025-04-01T06:50:00.000Z'::timestamp(3) as "createdAt", '6' as id
        union all
        select '2025-04-15T13:20:40.000Z'::timestamp(3) as "createdAt", '7' as id
        union all
        select '2025-05-05T17:45:30.000Z'::timestamp(3) as "createdAt", '8' as id
        union all
        select '2025-05-20T09:10:20.000Z'::timestamp(3) as "createdAt", '9' as id
        union all
        select '2025-05-30T23:59:59.000Z'::timestamp(3) as "createdAt", '10' as id        
    `,
    measures: {
        count: {
            type: 'count'
        }
    },
    dimensions: {
        createdAt: {
            sql: `${CUBE}."createdAt"`,
            type: `time`
        },
        id: {
            sql: `${CUBE}.id`,
            type: `string`
        },
    },
    preAggregations: {
        lambda: { // Speed layer: For data in the last 24h.
            type: `rollup_lambda`,
            union_with_source_data: true,
            rollups: [CUBE.main],
        },
        main: { // Batch layer: For data from 2021-01-01 until 24h ago.
            measures: [
                CUBE.count
            ],
            dimensions: [
                CUBE.id,
                CUBE.createdAt,
            ],
            timeDimension: CUBE.createdAt,
            granularity: `hour`,
            partitionGranularity: `month`,
            refreshKey: {
                every: `55 minutes`,
            },
            /*build_range_end: {
                sql: `SELECT NOW() - INTERVAL '24 hour'`,
            },*/
        }
    }
});

Generated sql query to build last partition

Build range end is not localized

   SELECT
       "test"."createdAt" "test__created_at", 
       "test".id "test__id", 
       date_trunc('hour', ("test"."createdAt"::timestamptz AT TIME ZONE 'Europe/Madrid')) "test__created_at_hour", 
       count(*) "test__count"
     FROM
       (
         select '2025-01-15T10:23:45.000Z'::timestamp(3) as "createdAt", '1' as id
         union all
         select '2025-02-05T14:12:30.000Z'::timestamp(3) as "createdAt", '2' as id
         union all
         select '2025-02-25T08:45:10.000Z'::timestamp(3) as "createdAt", '3' as id
         union all
         select '2025-03-10T11:34:56.000Z'::timestamp(3) as "createdAt", '4' as id
         union all
         select '2025-03-25T19:05:15.000Z'::timestamp(3) as "createdAt", '5' as id
         union all
         select '2025-04-01T06:50:00.000Z'::timestamp(3) as "createdAt", '6' as id
         union all
         select '2025-04-15T13:20:40.000Z'::timestamp(3) as "createdAt", '7' as id
         union all
         select '2025-05-05T17:45:30.000Z'::timestamp(3) as "createdAt", '8' as id
         union all
         select '2025-05-20T09:10:20.000Z'::timestamp(3) as "createdAt", '9' as id
         union all
         select '2025-05-30T23:59:59.000Z'::timestamp(3) as "createdAt", '10' as id
     ) AS "test"  WHERE ("test"."createdAt" >= '2025-04-30T22:00:00.000Z'::timestamptz AND "test"."createdAt" <= '2025-05-30T23:59:59.000Z'::timestamptz) GROUP BY 1, 2, 3

Generated Lambda sql query

{
  "timezone": "Europe/Madrid",
  "measures": [
    "Test.count"
  ]
}

SELECT
    "test"."createdAt" "test__created_at",
    "test".id "test__id",
    date_trunc(
        'hour', (
            "test"."createdAt":: timestamptz AT TIME ZONE 'Europe/Madrid'
        )
    ) "test__created_at_hour",
    count(*) "test__count"
FROM (
        select
            '2025-01-15T10:23:45.000Z':: timestamp(3) as "createdAt",
            '1' as id
        union all
        select
            '2025-02-05T14:12:30.000Z':: timestamp(3) as "createdAt",
            '2' as id
        union all
        select
            '2025-02-25T08:45:10.000Z':: timestamp(3) as "createdAt",
            '3' as id
        union all
        select
            '2025-03-10T11:34:56.000Z':: timestamp(3) as "createdAt",
            '4' as id
        union all
        select
            '2025-03-25T19:05:15.000Z':: timestamp(3) as "createdAt",
            '5' as id
        union all
        select
            '2025-04-01T06:50:00.000Z':: timestamp(3) as "createdAt",
            '6' as id
        union all
        select
            '2025-04-15T13:20:40.000Z':: timestamp(3) as "createdAt",
            '7' as id
        union all
        select
            '2025-05-05T17:45:30.000Z':: timestamp(3) as "createdAt",
            '8' as id
        union all
        select
            '2025-05-20T09:10:20.000Z':: timestamp(3) as "createdAt",
            '9' as id
        union all
        select
            '2025-05-30T23:59:59.000Z':: timestamp(3) as "createdAt",
            '10' as id
    ) AS "test"
WHERE (
        "test"."createdAt" > '2025-05-31T01:59:59.000Z':: timestamptz
    )
GROUP BY 1, 2, 3
LIMIT 200000

In a test cube we are not able to reproduce the availability of new inserted data but as we can see the end of build range for pre-aggregation was '2025-05-30T23:59:59.000Z' and the start of generated lambda query was '2025-05-31T01:59:59.000Z', so the end of build range is the maximum non-localized value for the time dimension and the start of lambda query is the maximum localized value for the time dimension resulting in missing records between '2025-05-30T23:59:59.000Z' and '2025-05-31T01:59:59.000Z'.

Version:
since 1.2.18

Additional context
Postgres as data source

[igorlukanin]

@ernriveron Thanks for reporting! Could you please provide more info to reproduce? Ideally, a minimal data model with a few rows of data and a query that demonstrates erroneous behavior.

[ernriveron]

I have updated the description.

[igorlukanin]

Thanks @ernriveron!

So, it looks like the lambda query is missing the AT TIME ZONE part in the filter in WHERE.

Here's probably the problematic line:

cube/packages/cubejs-schema-compiler/src/adapter/BaseQuery.js

Line 999 in e76c6eb

values: [FROM_PARTITION_RANGE]

. The time zone should have been used to convert this parameter.

[ernriveron]

I believe the issue is more complex and is related to the database server's output when using a time zone conversion statement.

In this function, there is an assumption that any timestamp without time zone information in its value is expressed in UTC. However, this assumption does not hold true for PostgreSQL when using the AT TIME ZONE operator.

According to PostgreSQL documentation, when you use the AT TIME ZONE operator on a timestamp with time zone, the result is a timestamp without time zone expressed in the time zone provided as a parameter.

Here is what the documentation states:

• timestamp with time zone AT TIME ZONE zone → timestamp without time zone
  • Converts the given timestamp with time zone to a timestamp without time zone, as the time would appear in the specified zone.
  • Example: timestamp with time zone '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver' → 2001-02-16 18:38:40

As a result, when Cube executes queries to retrieve the min/max values for the time dimension, these queries use the AT TIME ZONE operator, which returns timestamps without time zone information. This leads the localTimestampToUtc function to incorrectly interpret these values as UTC, even though they are not.

Thanks @ernriveron!

So, it looks like the lambda query is missing the AT TIME ZONE part in the filter in WHERE.

Here's probably the problematic line:

cube/packages/cubejs-schema-compiler/src/adapter/BaseQuery.js

Line 999 in e76c6eb

values: [FROM_PARTITION_RANGE]
. The time zone should have been used to convert this parameter.