search

xo / usql

Universal command-line interface for SQL databases
MIT License
8.96k stars 350 forks source link

mysql time_zone setting not respected for select return #315

Closed zhangyangyu closed 7 months ago

zhangyangyu commented 2 years ago

For mysql, if using usql to query a timestamp column, it represents the return value with timezone, but the timezone is wrong.

create table t(a timestamp, b datetime);
insert into t values('2022-02-16 5:52', '2022-02-16 5:52');
select * from t;
             a             |             b
---------------------------+---------------------------
 2022-02-16T05:52:00+08:00 | 2022-02-16T05:52:00+08:00
set time_zone='Europe/Helsinki';
select * from t;
             a             |             b
---------------------------+---------------------------
 2022-02-15T23:52:00+08:00 | 2022-02-16T05:52:00+08:00

Note the time is changed but time_zone still not.

kenshaw commented 1 year ago

@zhangyangyu usql does not know anything about the server's configuration.

Please note that there are many unique commands for various SQL servers and their respective clients that are sometimes recognized/parsed/intercepted by the client, and some that are 100% delegated to the actual server. I believe set here is the prototypical example of such -- in some SQL servers, a set query is 100% handled by the client, and for many others it can be a hybrid, or is 100% handled by the server.

For usql, all configuration variables/parameters are handled using \set or \pset.

I'll look into adding a setting that changes this on usql, but it would be a usql setting, not a MySQL setting. I'm also hesitant to enable something like this by default, as the user really needs to decide if they want to present the time relative to the user, or just dump/relay whatever the server says is "the time". I can see the relative merits of both, but I do think that having the server as the "source of truth" is likely always the right scenario.

Understand that one of the difficulties with this, is that if there was a usql setting for a "timezone appearance", there would be equally challenging issues. Consider: if a user had a "timezone appearance" setting on usql, what should the expected behavior be when a user then inserts/modifies/etc. a value? Should usql try to intercept and change that value? As of today, usql has no underlying understanding of SQL, and implementing such logic would require a full fledged SQL parser.

I have quite a bit of experience with the PostgreSQL code base, especially with psql, and if I remember correctly, I believe that the server handles all of those translations from the user when using a server-side SET. I am not sure how the mysql command line client might treat these values, if it is modifying it on the client side before sending it off to the server or altering it before displaying it.