timezone support in InfuxDB3 driver

nkostis commented 8 months ago

Specifications

Client Version: 0.4.0
InfluxDB Version: 3.x serverless
Platform: Windows client

InfluxDB driver 3 does not seem to properly support timezoned queries even though:

InfluxQL as a query language does support it:
- it works against InfluxDB 1.X
- the query is accepted by driver v3 (no exceptions, returns results)
the database itself supports it, since the same database returns correct results when queried with InfluxDB driver v2 (Flux query)

Code sample to reproduce problem

/* InfluxDB driver v2 (Flux) towards InfluxDB3 */
var influx3_drv2 = // build driver v2 (flux) connection 
var flux_query = @"from(bucket: ""bucket"")
|> range(start: -15d)
|> filter(fn: (r) => r._measurement == ""env"")
|> filter(fn: (r) => r._field == ""co2"")
|> filter(fn: (r) => (r.id == ""835""))
|> aggregateWindow(every: 1d, location: {zone: ""Europe/Athens"", offset: 0h}, fn: mean, timeSrc: ""_start"", createEmpty: true)
                          |> yield(name: ""query1"")";

var influx3_drv2_results = await influx3_drv2.QueryAsync(flux_query);
//inspect results (see expected behavior)

/* InfluxDB driver v3 (InfluxQL) towards same InfluxDB3 */
var influxdst3_drv3 = new InfluxDB3.Client.InfluxDBClient(/* conn. parameters */);
var influxql_query = "select mean(co2) as mean_co2 FROM env WHERE time > now() - 15d and id = '835' group by time(1d) TZ('Europe/Athens')";

var influx3_drv3_results = influxdst3_drv3.QueryPoints(influxql_query , InfluxDB3.Client.Query.QueryType.InfluxQL, "yodifem_dw").ToBlockingEnumerable().ToList();
//inspect results (see actual behavior)

Expected behavior

[
    "2024-02-02T21:21:36Z 508.5",
    "2024-02-02T22:00:00Z 481.3333333333333",
    "2024-02-03T22:00:00Z 498.46875",
    "2024-02-04T22:00:00Z 604.8315789473684",
    "2024-02-05T22:00:00Z 535.3958333333334",
    "2024-02-06T22:00:00Z 533.8210526315789",
    "2024-02-07T22:00:00Z 563.40625",
    "2024-02-08T22:00:00Z 512.5106382978723",
    "2024-02-09T22:00:00Z 414.5",
    "2024-02-10T22:00:00Z 421.15625",
    "2024-02-11T22:00:00Z 493.25531914893617",
    "2024-02-12T22:00:00Z 515.3936170212766",
    "2024-02-13T22:00:00Z 522.1041666666666",
    "2024-02-14T22:00:00Z 607.1052631578947",
    "2024-02-15T22:00:00Z 621.9270833333334",
    "2024-02-16T22:00:00Z 491.9247311827957"
]

results analysis:

results are identical to known correct values (from customer-verified InfluxDB2 Flux query and by having same data in InfluxDB 1.8.x and InfluxDB 2)
timestamps are correctly shifted so that day switches at the requested timezone (2 hours after UTC)

Actual behavior

[
    "2024-02-02T00:00:00Z 471.6",
    "2024-02-03T00:00:00Z 486.8333333333333",
    "2024-02-04T00:00:00Z 499.1770833333333",
    "2024-02-05T00:00:00Z 602.1052631578947",
    "2024-02-06T00:00:00Z 530.1041666666666",
    "2024-02-07T00:00:00Z 532.0736842105263",
    "2024-02-08T00:00:00Z 565.0833333333334",
    "2024-02-09T00:00:00Z 510.5851063829787",
    "2024-02-10T00:00:00Z 411.4270833333333",
    "2024-02-11T00:00:00Z 421.25",
    "2024-02-12T00:00:00Z 494.5744680851064",
    "2024-02-13T00:00:00Z 522.6276595744681",
    "2024-02-14T00:00:00Z 515.8645833333334",
    "2024-02-15T00:00:00Z 611.0105263157894",
    "2024-02-16T00:00:00Z 624.3020833333334",
    "2024-02-17T00:00:00Z 492.81176470588235"
]

results analysis:

results differ from known correct values
timestamps show day switches at 12am UTC, instead of at the requested timezone

Additional info

i'd be happy to provide more info. Timezone support is the sole factor keeping my company at driver v2 (against InfluxDB 3, resulting is severely reduced performance) The reasoning behind marking as 'bug' (instead of feature request) is because the driver accepts the Timezone-enabled query but returns wrong results

bednar commented 8 months ago

Hello @nkostis,

Thank you for using our client and for bringing this issue to our attention.

The issue with the TZ clause you've encountered is related to the InfluxDB Serverless platform itself. Currently, InfluxDB Serverless does not support the TZ clause, which is why you're experiencing this limitation.

For further details and official documentation on this matter, please visit: Time and Timezone Reference for InfluxDB Cloud Serverless. This page provides comprehensive information about time and timezone handling in InfluxDB Cloud Serverless, including the current limitations regarding the TZ clause.

We understand how important it is to have comprehensive functionality for your data queries, and we appreciate your patience as the InfluxDB team works on expanding the capabilities of the Serverless platform.

Best Regards.

nkostis commented 8 months ago

hi Jakub, thanks for your response, it's understood (if unfortunate) i will open a ticket with Influx Support, referencing this github issue, in order to get a timeline feel if possible

nkostis commented 7 months ago

dear @bednar a side-question: how does driver v2 (Flux) work against Influx 3 cloud serverless with timezones? (Does it retrieve raw data and convert client-side?)

bednar commented 7 months ago

The v2 drive can only works with TSM storage engine.

nkostis commented 7 months ago

v2 driver can work with Influx3 serverless, it's what we currently use, in production -- but it's slow. It's going through an adaptation layer of some sort but details are known by Influx the company

InfluxCommunity / influxdb3-csharp