I have a ClickHouse table that contains the following schema:
`Start` DateTime64(9),
`End` DateTime64(9),
`Duration` Int64 DEFAULT toInt64((toDecimal64(End, 9) - toDecimal64(Start, 9)) * 1000000000),
Start and End are DateTime64(9) fields with nanosecond precision, and I also need to store the difference of these in a Duration field which I'd like ClickHouse to generate automatically. I had trouble finding the best way to convert DateTime64(9) fields to nanosecond epoch timestamps, and eventually cobbled together a solution that converts them to decimals and multiplies by a constant to give the difference in nanoseconds as an integer.
This works, I seem to be getting the correct Duration values, but the * 1000000000 part seems like a smell to me. Is there a better way to do this that I'm not aware of? I looked through ClickHouse's date functions and couldn't find ones that deal with nanosecond precision, only toUnixTimestamp() which is only seconds precision.
There are some nano-specific functions (that could not be referenced in docs), let's observe them for ClickHouse version 22.8.15.23:
SELECT *
FROM system.functions
WHERE name ILIKE '%nano%'
/*
┌─name────────────────────┬─is_aggregate─┬─case_insensitive─┬─alias_to─┬─create_query─┬─origin─┐
│ addNanoseconds │ 0 │ 0 │ │ │ System │
│ fromUnixTimestamp64Nano │ 0 │ 0 │ │ │ System │
│ subtractNanoseconds │ 0 │ 0 │ │ │ System │
│ toUnixTimestamp64Nano │ 0 │ 0 │ │ │ System │
│ toStartOfNanosecond │ 0 │ 0 │ │ │ System │
│ toIntervalNanosecond │ 0 │ 0 │ │ │ System │
└─────────────────────────┴──────────────┴──────────────────┴──────────┴──────────────┴────────┘
*/
This one should work for you:
SELECT
now64() AS start,
addNanoseconds(start, 12345) AS end,
toUnixTimestamp64Nano(end) - toUnixTimestamp64Nano(start) AS duration
/*
┌───────────────────start─┬───────────────────────────end─┬─duration─┐
│ 2023-11-27 21:27:23.958 │ 2023-11-27 21:27:23.958012345 │ 12345 │
└─────────────────────────┴───────────────────────────────┴──────────┘
*/