empty output in CH because of different timezone in cluster and on carbon-clickhouse/graphite-clichouse/carbonapi node

[Vlggg]

Hello folks!

I've tried to update graphite-clickhouse from 0.12.0 to 0.13.2 and found interesting behavior. graphite-clickhouse, carbon-clickhouse and carbonapi run on node A with timezone UTC, at the same time clickhouse cluster works on several nodes with timezone PDT(UTC-7)

Query id: 97aa4f48-ab46-4727-bbbb-d5499a9f5e69

┌─timezone()──────────┐
│ America/Los_Angeles │
└─────────────────────┘

and everything works fine but only for specific time ranges.

Example:

SELECT *
FROM graphite.diamond_sharded_D
WHERE (Path IN ('carbon.agents.hostname.tcp.errors')) AND ((Time >= 1650776160) AND (Time <= 1650779759))
LIMIT 1

Query id: af7c3016-48e8-4691-a07c-bf4a90f05b4b

Row 1:
──────
Path:      carbon.agents.hostname.tcp.errors
Value:     0
Time:      1650776220
Date:      2022-04-24
Timestamp: 1650776262


SELECT count(*)
FROM graphite.diamond_sharded_D
WHERE (Path IN ('carbon.agents.hostname.tcp.errors')) AND ((Time >= 1650776160) AND (Time <= 1650779759))

Query id: cfa66a36-4aba-40e5-bb1a-d9650e7ff107

┌─count()─┐
│      60 │
└─────────┘

we have metrics in time period - from 1650776160 to 1650779759. Now if we run query as graphite-clickhouse do then

WITH anyResample(1650776160, 1650779759, 60)(toUInt32(intDiv(Time, 60) * 60), Time) AS mask
SELECT
    Path,
    arrayFilter(m -> (m != 0), mask) AS times,
    arrayFilter((v, m) -> (m != 0), avgResample(1650776160, 1650779759, 60)(Value, Time), mask) AS values
FROM graphite.diamond_sharded_D
PREWHERE (Date >= toDate(1650776160)) AND (Date <= toDate(1650779759))
WHERE (Path IN ('carbon.agents.hostname.tcp.errors')) AND ((Time >= 1650776160) AND (Time <= 1650779759))
GROUP BY Path

Query id: db27df23-36cb-4333-beed-6d641e07d4be

Ok.

0 rows in set. Elapsed: 0.532 sec. Processed 163.84 thousand rows, 15.17 MB (308.11 thousand rows/s., 28.52 MB/s.) 

As you can see there is no rows in answer but if we set timezone from carbon-clikchouse node in PREWHERE part of query, we have correct response. PREWHERE Date >= toDate(1650776160,'UTC') AND Date <= toDate(1650779759,'UTC') example:

WITH anyResample(1650776160, 1650779759, 60)(toUInt32(intDiv(Time, 60) * 60), Time) AS mask
SELECT
    Path,
    arrayFilter(m -> (m != 0), mask) AS times,
    arrayFilter((v, m) -> (m != 0), avgResample(1650776160, 1650779759, 60)(Value, Time), mask) AS values
FROM graphite.diamond_sharded_D
PREWHERE (Date >= toDate(1650776160, 'UTC')) AND (Date <= toDate(1650779759, 'UTC'))
WHERE (Path IN ('carbon.agents.hostname.tcp.errors')) AND ((Time >= 1650776160) AND (Time <= 1650779759))
GROUP BY Path

Query id: 737ae298-9556-4f2e-a60a-2e12882ea20e

┌─Path───────────────────────────────────────────────────────┬─times──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─values────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ carbon.agents.hostname.tcp.errors │ [1650776160,1650776220,1650776280,1650776340,1650776400,1650776460,1650776520,1650776580,1650776640,1650776700,1650776760,1650776820,1650776880,1650776940,1650777000,1650777060,1650777120,1650777180,1650777240,1650777300,1650777360,1650777420,1650777480,1650777540,1650777600,1650777660,1650777720,1650777780,1650777840,1650777900,1650777960,1650778020,1650778080,1650778140,1650778200,1650778260,1650778320,1650778380,1650778440,1650778500,1650778560,1650778620,1650778680,1650778740,1650778800,1650778860,1650778920,1650778980,1650779040,1650779100,1650779160,1650779220,1650779280,1650779340,1650779400,1650779460,1650779520,1650779580,1650779640,1650779700] │ [4,0,0,4,0,0,5,0,0,1,2,0,1,2,2,1,2,1,1,3,0,0,4,0,0,3,0,1,3,3,0,1,2,2,2,1,3,3,1,3,3,0,2,5,1,0,2,1,1,3,1,0,1,2,0,1,2,0,1,2] │
└────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

I made a hot fix for that but this works only for our env and itself not a good solution)

--- pkg/where/where.go  2022-04-22 10:33:14.772160554 +0300
+++ pkg/where/where.go  2022-04-29 18:49:09.202685254 +0300
@@ -178,7 +178,7 @@

 func DateBetween(field string, from int64, until int64) string {
    return fmt.Sprintf(
-       "%s >= toDate(%d) AND %s <= toDate(%d)",
+       "%s >= toDate(%d, 'UTC') AND %s <= toDate(%d, 'UTC')",
        field, from, field, until,
    )
 }

[msaf1980]

Thank you for detailed report. I work for fix this issue.

[msaf1980]

@Vlggg Can you run queries on your clickhouse ?

select toDate(1650776160,'UTC') Must return 2022-04-24

select toDate(1650776160) Must return 2022-04-23. And it's a problem.

select toDate(1650776160,'Etc/GMT+7') return 2022-04-23, so may be your solution is work. Or may be format date on graphite-clickhouse (against UTC).

[msaf1980]

@Vlggg Can Your build and test graphite-clickhouse from https://github.com/msaf1980/graphite-clickhouse/tree/fix_daystart ? Bug description: Date not always stored in UTC. But your graphite-clickhouse and carbon-clickhouse in one timezone, so fix must work. Also in new carbon-clickhouse added options for store Date in UTC (and options for graphite-clickhouse also).