ClickHouse / ClickHouse/odbc-bridge
Milliseconds are lost while reading datetime field from MSSQL
- Dominant language
- C++
- Stars
- 4
- Forks
- 4
- PR merge metrics
- No merged PRs in 30d
Description
ClickHouse server version: 22.9.2
OS: Ubuntu 18.04
On MSSQL server there is a MSSQL table with several datetime fields. These fields contain date and time with fractional part of seconds. When I'm using isql to read from the table, all is properly:
```sql
$ isql testserver clickhouse_reader ***
SQL> select top 10 date_add from test_table
+------------------------+
| date_add |
+------------------------+
| 2017-11-09 17:06:52.887|
| 2017-12-08 11:25:36.137|
| 2017-12-22 10:15:36.223|
| 2018-01-18 03:30:36.153|
| 2018-02-01 09:44:57.940|
| 2018-02-13 03:30:36.180|
| 2018-02-27 03:30:36.947|
| 2018-03-12 12:50:36.290|
| 2018-03-27 05:10:36.250|
| 2018-04-08 05:05:36.433|
+------------------------+
```
When I'm reading from the table by clickhouse-client via odbc function, the picture differs:
```sql
SELECT
date_add,
toUnixTimestamp(date_add)
FROM odbc('DSN=testserver;Uid=clickhouse_reader;Pwd=***;Database=test_analytics', '', 'test_table')
LIMIT 10
Query id: a17bb21e-d7d3-4e07-bba2-d73b97324a29
┌────────────date_add─┬─toUnixTimestamp(date_add)─┐
│ 2017-11-09 17:06:52 │ 1510236412 │
│ 2017-12-08 11:25:36 │ 1512721536 │
│ 2017-12-22 10:15:36 │ 1513926936 │
│ 2018-01-18 03:30:36 │ 1516235436 │
│ 2018-02-01 09:44:57 │ 1517467497 │
│ 2018-02-13 03:30:36 │ 1518481836 │
│ 2018-02-27 03:30:36 │ 1519691436 │
│ 2018-03-12 12:50:36 │ 1520848236 │
│ 2018-03-27 05:10:36 │ 1522116636 │
│ 2018-04-08 05:05:36 │ 1523153136 │
└─────────────────────┴───────────────────────────┘
```
There aren't fractional parts in this case. Why?
The ODBC settings are as follows:
```sql
[testserver]
Description = Test analytics
; I use native Microsoft ODBC Driver 18 for SQL Server
Driver = MSSQL18
Server = 192.168.1.1
Port = 1433
Database = test_analytics
UID = clickhouse_reader
Pwd = ***
```
Contributor guide
Assessment
This issue has not been assessed yet.