ClickHouse / ClickHouse/odbc-bridge

Milliseconds are lost while reading datetime field from MSSQL

Open
#8 3 comments 1 reaction 0 assignees View on GitHub
question
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.