Support for specifying time zone in client connections

[waynexia]

What problem does the new feature solve?

Currently, DB only supports using the server’s timezone for writing/querying data. However, this can be inconvenient in situations when the server and client time zones are different. Additionally, it can lead to confusion due to inconsistent behavior between timestamp numbers and strings. Therefore, the new feature aims to address these issues by allowing the specification of a timezone in client connections.

What does the feature do?

The proposed feature enables clients to specify a timezone during their connection/session establishment. This additional parameter will serve as a fallback value when the timezone is not explicitly provided in write or query operations. By supporting the specification of a timezone on the client-side, the feature enhances flexibility and convenience for users.

This feature will greatly benefit users who frequently encounter scenarios where the server and client timezones do not align. And those who need to serve clients from different timezones. It will eliminate confusion caused by inconsistent behavior related to timestamps and strings. Additionally, it will provide users with a more streamlined and efficient approach to handle discrepancies in timezones between the server and clients.

Implementation details

• Use GMT as the default timezone on server side instead of fetching it from ENV each time feat!: do not get TZ info from server local env #2905
• Add a config entry to override the default timezone on server side @Taylor-lagrange
• Support SET time_zone = xxx (TODO: verify the current state) clause Does greptimedb support timezone? #2893
• Add HTTP/gRPC header for specifying time zone feat: support HTTP&gRPC&pg set timezone #3125
• Pass timezone info in session context, all timestamp translation (string to UNIX epoch) should be aware of this info @Taylor-lagrange feat: make query be aware of timezone setting #3175
• Support override client timezone in session context, which has higher priority than the default timezone feat: support set timezone in db #2992
• More clause/protocol param to set client timezone
• Make UDF/Aggregation function context aware of timezone. feat: make query be aware of timezone setting #3175
• SQL insertion and column default constraint should respect timezone. fix: SQL insertion and column default constraint aware of timezone #3266
• Add document to describe our timezone-related behavior
• Integration tests.
• Change Range Query's default align behavior to 1970-01-01 00:00:00/user_tz feat: change Range Query’s default align behavior aware of timezone #3219
• Ingester SDK
  • SDK doesn't have this issue because the gRPC protocol is strong typed

[killme2008]

Support SET time_zone = xxx clause #2893

Already supports it in MySQL protocol.

[tisonkun]

I think the difficult part is that if we convey this timezone config in the optimizer context.

Even we support this statement in the MySQL server codepath now, it's used only for adjust outputs.

[waynexia]

I think the difficult part is that if we convey this timezone config in the optimizer context.

Correct, that's what the third task "Pass client timezone in session context" requests. Luckily we have gathered those conversion logic in a few places (ref to #2905).

[sunng87]

A few context:

1. We have time zone settings in QueryContext back in feat: time_zone variable for mysql connections #1607. Time zone aware queries should follow time zone settings from QueryContext1.
2. Currently time zone in QueryContext is only writable in MySQL protocol. We will need mechanism to write in PostgreSQL and HTTP protocols.
3. On default timezone, MySQL uses host machine time zone by default. https://dev.mysql.com/doc/refman/8.2/en/time-zone-support.html#time-zone-variables

The server system time zone. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable.

[Taylor-lagrange]

Currently supported query protocols

mysql, pg, gRPC, HTTP, OpenTSDB, influxdb line protocol

MySQL

Move Set time_zone = 'UTC' dispose logic from federated to parser, unify the behavior of pg and mysql

Set the time zone (parser dispose the SQL):

Set time_zone = 'UTC'

Query the current time zone:

1. Show time_zone / SHOW VARIABLES time_zone (parser dispose the SQL, mysql generally does not use this method to check the time zone, mainly to unify the behavior with pg)
2. SELECT @@system_time_zone, @@time_zone; (still retained in federated, because this is the customary practice of mysql)

PG

Set the time zone (parser dispose the SQL, a common way of writing pg):

SET TIMEZONE TO 'UTC'
SET TIME ZONE 'UTC'

Query the current time zone:

Show time_zone / SHOW VARIABLES time_zone (parser dispose the SQL, consistent with mysql behavior)

gRPC

Add an additional timezone header and pass it every time in RPC message to make the db aware of the time zone.

Use the gRPC client to provide functions to set the time zone. Although gRPC can also pass SQL statements, SQL statements such as SET/SHOW have no effect on gRPC.

Sqlness related: When we do sqlness integration testing, the underlying implementation uses gRPC protocol to transmit SQL queries. We still need to test some SQL statements related to setting time zones. In response to this situation, we need to modify the sqlness test code. Using a similar idea to federated, regular SQL statements such as SET/SHOW are intercepted, and the gRPC header is correctly set based on these statements, thereby simulating the effect of SQL statements such as SET/SHOW and correctly performing integration testing.

HTTP

Add a Timezone http header and pass this header every time you query

curl -X POST \
     -H 'Authorization: Basic Z3JlcHRpbWVfdXNlcjpncmVwdGltZV9wd2Q=' \
     -H 'Content-Type: application/x-www-form-urlencoded' \
     -H 'Timezone: +8:00' \
     -d 'sql=SELECT * from host WHERE ts > '1960-01-01 00:00:00'; \
     http://localhost:4000/v1/sql?db=public

OpenTSDB

Whether it is Telnet API or HTTP API, timestamps are written, regardless of time zone.

Influxdb line protocol

This protocol is a writing protocol and has nothing to do with time zones. What is written is UTC timestamp.

Time zone related behavior

1. String to timestamp conversion time zone aware
2. When converting, User timezone overwrites system timezone

Currently we don't have any support for either of these

The conversion from string to timestamp is mainly reflected in:

1. Convert the timestamp string into a timestamp during Insert
   1. Example: INSERT INTO TABLE host VALUES ("1970-01-01T00:00:00", 'host1', 0);
   2. It is relatively easy to solve, because the insert logic does not follow the query plan, as long as the insert is aware of the query context.
2. Timestamp string conversion in query plan
   1. Example: SELECT * from host WHERE ts > '1960-01-01 00:00:00';
   2. It is difficult to solve because this type of conversion is solved through the datafusion query plan and by TypeConversionRule. Time zone information can be passed in the configuration items of the optimizer rule, but the source of this time zone information is SessionState.
   3. The SessionState variable is immutable during runtime. We may be able to pass system timezone in when SessionState is initialized so that TypeConversionRule can aware system timezone, but it is difficult to make user timezone override system timezone.

[Taylor-lagrange]

According to some discussion with @waynexia:

1. Temporally move TypeConversionRule out of optimizer rule，execute this rule alone with query context timezone info.
2. implement sqlness by pg wire protocol，abandon original gRPC implementation.
   @killme2008 @sunng87

[killme2008]

Another solution is to make TypeConversionRule aware of timezone, see #3175

[killme2008]

@Taylor-lagrange Hello, as the main designer of this feature, could you write the document that describes our timezone-related behavior?

[Taylor-lagrange]

@Taylor-lagrange Hello, as the main designer of this feature, could you write the document that describes our timezone-related behavior?

I will add doc about timezone setting.

[Taylor-lagrange]

We already support set timezone in gRPC protocol, but it seems our gRPC client https://github.com/GreptimeTeam/greptimedb-ingester-go/ still not support set timezone when config client

[waynexia]

What about other languages? Like Java and Rust, cc @fengjiachun @sunng87. Let me add a section about ingester to the list

[killme2008]

We previously discussed this, but since our clients only do writing and do not require string-to-timestamp conversions, it seems that timezone setting is unnecessary.

[waynexia]

We previously discussed this, but since our clients only do writing and do not require string-to-timestamp conversions, it seems that timezone setting is unnecessary.

Makes sense. gRPC protocol is strong typed and doesn't support string as timestamp.

[MichaelScofield]

What's left of the todos in this issue is a little vague, I think we can close this issue for now.