--use_client_time_zone does not affect all date-related functionality

[George3d6]

After starting the client with said flag = 1 I got the following:

┌─toDateTime(1492124400)─┐
│ 2017-04-13 23:00:00 │
└────────────────────────┘

┌─toDate(1492124400)─┐
│ 2017-04-14 │
└────────────────────┘

This behavior seems rather unpredictable, is this to be expected or is this a bug ?

[ztlpn]

Yes, this is a bug/limitation of how timezones currently work. The problem is that the client cannot set the server-side session timezone. All it can change is the timezone of client-side operations (that is, parsing and formatting).

Here is a good mental model to help you avoid such surprises:

• There are two timezones, server-side and client-side. Most of the time they are equal, but you can change client timezone with --use_client_time_zone
• Client-server protocol uses Unix timestamps to exchange DateTime values and days since epoch to exchange Date values. These are considered timezone-independent.
• All operations except parsing and formatting are handled by the server.

So in your case toDate() value is calculated by the server using the server timezone and sent to the client. The value is then formatted by the client (and Date values are formatted without regard for timezone).

[alexey-milovidov]

You always could specify time zone explicitly, for example:

toDate(1492124400, 'America/New_York')

2017-04-13

[stiv-yakovenko]

Please add jdbc connection property, because this problem provokes writing guly code...

[den-crane]

Please add jdbc connection property, because this problem provokes writing guly code...

jdbc has these properties
ClickHouse/clickhouse-java#202