Substraction DateTime64 from DateTime64 in SQL clickhouse BD

Question

I trying to find to calculate time difference in milliseconds betweent timestamps of two tables. like this,

SELECT  value, (table1.time - table2.time)  AS time_delta

but i get error :

llegal types DateTime64(9) and DateTime64(9) of arguments of function minus:

so i can't substract DateTime64 in clickhouse.

Second way i tryed use DATEDIFF , but this func is limited by "SECONDS", i need values in "MILLISECONDS"

this is supported, but i get zeros in diff, because difference is too low(few millisecond):

SELECT  value, dateDiff(SECOND  , table1.time, table2.platform_time)  AS time_delta

this is not supported:

SELECT  value, dateDiff(MILLISECOND  , table1.time, table2.time)  AS time_delta

What's a better way to resolve my problem?

P.S i also tryed convert values to float, it's work , but looks strange,

SELECT  value, (toFloat64(table1.time) - toFloat64(table2.time))  AS time_delta

as result i get somethink like this:

value        time
51167477    -0.10901069641113281

score 2 · Answer 1 · answered Jul 01 '22 at 19:41

@ditrauth Try casting to Float64, as the subsecond portion that you are looking for is stored as a decimal. Aslo, you want DateTime64(3) for milliseconds, see the docs. see below:

CREATE TABLE dt( 
                start DateTime64(3, 'Asia/Istanbul'),
                end DateTime64(3, 'Asia/Istanbul')
)
ENGINE = MergeTree ORDER BY end

insert into dt values (1546300800123, 1546300800125),
                      (1546300800123, 1546300800133)

SELECT
    start,
    CAST(start, 'Float64'),
    end,
    CAST(end, 'Float64'),
    CAST(end, 'Float64') - CAST(start, 'Float64') AS diff
FROM dt

┌───────────────────start─┬─CAST(start, 'Float64')─┬─────────────────────end─┬─CAST(end, 'Float64')─┬─────────────────diff─┐
│ 2019-01-01 03:00:00.123 │         1546300800.123 │ 2019-01-01 03:00:00.125 │       1546300800.125 │ 0.002000093460083008 │
│ 2019-01-01 03:00:00.123 │         1546300800.123 │ 2019-01-01 03:00:00.133 │       1546300800.133 │ 0.009999990463256836 │
└─────────────────────────┴────────────────────────┴─────────────────────────┴──────────────────────┴──────────────────────┘

2 rows in set. Elapsed: 0.001 sec.

Victor · Answer 2 · 2023-03-03T11:25:24.950

Cast to Float64 should be avoided if the accuracy of the calculations is important. Instead it's better cast to Decimal128(9) (if you use nanoseconds) or Decimal64(6) (if you use microseconds).

CREATE TABLE dt
(
    start DateTime64(9, 'UTC'),
    end   DateTime64(9, 'UTC')
)
ENGINE = MergeTree

SELECT
    CAST(start, 'Decimal128(9)') AS start_dec,
    CAST(end, 'Decimal128(9)') AS end_dec,
    end_dec - start_dec AS diff
FROM dt

Source: Decimal(P, S).

NB: Because modern CPUs do not support 128-bit integers natively, operations on Decimal128 are emulated. Because of this Decimal128 works significantly slower than Decimal32/Decimal64.

Substraction DateTime64 from DateTime64 in SQL clickhouse BD

2 Answers2