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

Answer 1

@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.