Presto JDBC: Timezone displays incurrect even after setting -Duser.timezone=+0800

Answered

Current time is `2018-11-27 14:52:11` timezone=+0800

But
1. `select now()` is `2018-11-27 06:52:11` (Wrong)
2.
```sql
-- result: 2018-11-27 00:00:07, 2018-11-27 06:52:37 (Wrong)
select
min(sale_time),
max(sale_time)
from sales
where sale_time > current_date and sale_time <= now()
```

 

After setting VM options to `-Duser.timezone=+0800`

1. `select now()` is `2018-11-27 06:52:11` (Wrong)
2.
```sql
-- result: 2018-11-27 00:00:07, 2018-11-27 14:52:57 (Right)
select
min(sale_time),
max(sale_time)
from sales
where sale_time > current_date and sale_time <= now()
```

Any solutions?

 

Better view:

 

 

10 comments
Comment actions Permalink

Could you specify your presto db jdbc driver version?

0
Comment actions Permalink

It's 0.213, same issue using 0.211, 0.214. I think it's not about the jdbc version. Please check this out:

-- _col0                              _col1
-- 2018-11-30 01:24:35 true
select
now(),
now() > cast('2018-11-30 09:18:10' as timestamp);

It works if use Java code (VM options: -Duser.timezone=+0800)

ResultSet result = stat.executeQuery("select now()");
while (result.next()) {
System.out.println(result.getString(1));
}
// 2018-11-30 09:23:26.447 +08:00
0
Comment actions Permalink

I reproduced the issue. Follow https://youtrack.jetbrains.com/issue/DBE-7504 to get updates

0
Comment actions Permalink

using the 2021.2 RC this does not appear to be fixed by DBE-7504

0
Comment actions Permalink

Alexga,

Everything is working as expected in 2021.2 RC.

Output of Presto CLI:

Output of DataGrip 2021.2 RC:

 

Could you provide an example to reproduce your issue?

0
Comment actions Permalink

Appreciate the follow up. Apologize for not adding an example in the first place.

I'm running 2021.2 (no longer the RC) at this point but am still able to recreate. 

output from CLI:

Output from DataGrip 2021.2:

0
Comment actions Permalink

Alexga,

I see. Could you leave a comment in https://youtrack.jetbrains.com/issue/DBE-7504 and attach IDE and SQL logs for investigation?

0
Comment actions Permalink

vasily chernov, I see you copied the above over, do you need server logs as well? happy to provide them if needed

0
Comment actions Permalink

Alexga,  it would be great if you provide exact server version, server timezone, your client timezone and JDBC driver version. Could you do that?

0
Comment actions Permalink

current version: 

2021-07-29T21:46:03.594Z INFO main io.trino.server.Server Trino version: 359

server timezone:

2021-07-29T21:52:32.133Z INFO main Bootstrap sql.forced-session-time-zone ---- ---- User session time zone overriding value sent by client

and

trino> select current_timezone();
_col0
-------
UTC
(1 row)

Query 20210729_215302_00000_thvmf, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.56 [0 rows, 0B] [0 rows/s, 0B/s]

client timezone:

no vm options

jdbc driver:

This is the provided driver, but I've also tried it with ver 359

0

Please sign in to leave a comment.