Pillar eats up milliseconds

[zhabinka]

Greetings!

We encountered unexpected behavior. The data type DateTime64 allows milliseconds, but Pillar rounds it to seconds.

For example input data:

created_1 = "2023-11-30T13:45:00.500000Z"
created_2 = "2023-11-30T13:45:00.750000Z"
created_3 = "2023-11-30T13:45:00.850000Z"
created_4 = "2023-11-30T13:45:01.200000Z"

In ClickHouse it saved like:

uid	created_at
t1	2023-11-30 13:45:00.000
t2	2023-11-30 13:45:00.000
t3	2023-11-30 13:45:00.000
t4	2023-11-30 13:45:01.000

There are may be a reason for this behavior. Can you explain why it works this way?

We have problem to build pagination based on date when we insert more than 1 records per second.

[yzh44yzh]

Actually, we know why it happens. It is because of this:
https://github.com/balance-platform/pillar/blob/master/lib/pillar/type_convert/to_clickhouse.ex#L40

And we what to know the reasoning behind this code. It is unclear why you decided to drop milliseconds.

[sofakingworld]

@yzh44yzh @zhabinka Hello!

The answer is quite simple, the DateTime64 type was added to clickhouse later than the convert function in this library.

https://github.com/balance-platform/pillar/blob/master/lib/pillar/type_convert/to_clickhouse.ex#L40 (March 2020)
ClickHouse/ClickHouse#11512 (June 2020)

What workaround I can suggest:

1. Pre-convertation of "created_at" into String, Clickhouse should eat it
2. Upgrage query builder to work with "hints" or special data_structure

For Example:

Pillar.query(conn, 
  "INSERT INTO table (id, created_at) SELECT {id}, {created_at}",
  %{id: 123, created_at: %Pillar.Arg{value: DateTime.utc_now(), target_type: :DateTime64}
)

[yzh44yzh]

Ok, thank you. We did number 1 and it works for us.

However, may be it is time to change something in pillar )

[sofakingworld]

Okay, glad it helped!

We are not actively using clickhouse right now, so the library is not actively developing. In any case, we do not refuse support, if there are people willing to contribute, we will help, merge and publish an updated version on hex)

[yzh44yzh]

ok, got it

[sofakingworld]

@yzh44yzh @zhabinka Hello!

If it's still relevant, it's now possible to define a protocol for write data into Clickhouse

https://github.com/balance-platform/pillar?tab=readme-ov-file#custom-type-conversions