strongduanmu edited a comment on issue #11794:
URL:
https://github.com/apache/shardingsphere/issues/11794#issuecomment-918940633
When I tested, I found another problem, that is, calcite has problems with
time processing.
```sql
mysql> insert into t_single(single_id, content, date) values(1, '11',
'2021-09-14');
Query OK, 1 row affected (0.03 sec)
```
I executed the following SQL and inserted a record with the date
`2021-09-14` into the t_single table, but when I query it through calcite, it
returns `2021-09-13`.
```sql
mysql> select * from t_single s inner join t_order_test t ON s.single_id =
t.test_id;
+---------+------------+-----------+----------+---------+
| content | date | single_id | content0 | test_id |
+---------+------------+-----------+----------+---------+
| 11 | 2021-09-13 | 1 | 11 | 1 |
+---------+------------+-----------+----------+---------+
mysql> select * from t_single s inner join t_order_test t ON s.single_id =
t.test_id WHERE create_date_time = '2021-09-14';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 1
Current database: sharding_db
+---------+---------------------+-----------+----------+---------+
| content | create_date_time | single_id | content0 | test_id |
+---------+---------------------+-----------+----------+---------+
| 11 | 2021-09-13 16:00:00 | 1 | 11 | 1 |
+---------+---------------------+-----------+----------+---------+
```
The DateAccessor logic is as follows:
```java
@Override public Date getDate(Calendar calendar) throws SQLException {
java.sql.Date date = (Date) getObject();
if (date == null) {
return null;
}
if (calendar != null) {
long v = date.getTime();
v -= calendar.getTimeZone().getOffset(v);
date = new Date(v);
}
return date;
}
```
When calculating the time, the difference between the current time and the
standard time is subtracted, which will cause the result to be 8 hours less
than the real time (because we are in the GMT+8). The solution is to set the
time zone to GMT in the Calcite url parameter.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
