SQL Clickhouse BD中的DateTime64的dateTime64 sudTraction dateTime64

发布于 2025-02-12 09:37:01 字数 843 浏览 2 评论 0原文

我试图找到两张表的时间戳的毫秒毫秒的时间差。这样，

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

但是我会遇到错误：

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

所以我无法在Clickhouse中提取DateTime64。

我尝试使用日期的第二种方法，但是该功能受到“秒”的限制，我需要“毫秒”中的值，

但我会得到零零的零，因为差异太低（几毫秒）：

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

不支持：这是不支持的：

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

解决我的问题的更好方法是什么？

PS我还尝试将值转换为float，这是工作，但看起来很奇怪，

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

因此我会这样思考：

value        time
51167477    -0.10901069641113281

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

幸福还没到 2025-02-19 09:37:01

@ditrauth尝试将铸造铸造到float64，作为您正在寻找的子代数作为小数存储。 ASLO，您需要DateTime64（3）对于毫秒，请参见。见下文：

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.

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

回复收藏 0 原文

情绪操控生活 2025-02-19 09:37:01

如果计算的准确性很重要，则应避免使用float64。取而代之的是，最好将其施放到Demimal128（9）（如果使用纳秒）或decimal64（6）（如果使用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

来源： Decimal（p，s）。

NB：由于现代CPU不支持128位整数本地，因此模仿了Decimal128的操作。由于该十分位数为十分位128，其工作速度明显慢于Decimal32/Decimal64。

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.

回复收藏 0 原文

~没有更多了~