迄今为止，如何将CQL BIGINT类型转换为？

发布于 2025-02-05 04:08:20 字数 672 浏览 4 评论 0原文

我使用的是Cassandra，并且有一列数据，该数据代表以bigint类型存储的Epoch Time毫秒：

request_time bigint

我想将其转换为日期时间对象，以便与某些日子一起使用。我该如何实现这种转换？

我尝试过：

select todate(request_time)

# InvalidRequest: Error from server: code=2200 [Invalid query] message="Input type Int64 is not supported in ToDate."

还有：

select dateof(mintimeuuid(request_time))

# InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce Int64 into type CassandraTimestamp"

我的Cassandra版本是：

[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]

原文

I am using Cassandra and I have a column of data that represents epoch time milliseconds stored as a bigint type:

request_time bigint

I would like to convert this to a date time object in order to work with certain days. How can I achieve that conversion?

I have tried:

select todate(request_time)

# InvalidRequest: Error from server: code=2200 [Invalid query] message="Input type Int64 is not supported in ToDate."

and also:

select dateof(mintimeuuid(request_time))

# InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce Int64 into type CassandraTimestamp"

My Cassandra version is:

[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]

分享到QQ

分享到微博

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

发布评论

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

心的憧憬 2025-02-12 04:08:20

使用功能 -

todate()

下面的示例 -

select request_time from testkspc.test;

 request_time
---------------
 123344345
 123344687
 123456329
 223344687
 1613808000000
 323344687
 123456789
 123344329
 423344687
 523344687

select todate(request_time)  from testkspc.test ;

 system.todate(request_time)
-----------------------------
                  1970-01-02
                  1970-01-02
                  1970-01-02
                  1970-01-03
                  2021-02-20
                  1970-01-04
                  1970-01-02
                  1970-01-02
                  1970-01-05
                  1970-01-07

您可以使用Totimestamp函数。
直接在BigInt类型上使用此函数会产生错误 -

select totimestamp(request_time)  from testkspc.test ;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid call to function totimestamp, none of its type signatures match (known type signatures: system.totimestamp : (date) -> timestamp, system.totimestamp : (timeuuid) -> timestamp)"

我们可以包装todate totimestamp 。

select totimestamp(todate(request_time))  from testkspc.test ;

 system.totimestamp(system.todate(request_time))
-------------------------------------------------
                 1970-01-02 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-03 00:00:00.000000+0000
                 2021-02-20 00:00:00.000000+0000
                 1970-01-04 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-05 00:00:00.000000+0000
                 1970-01-07 00:00:00.000000+0000

Use function -

todate()

Example below -

select request_time from testkspc.test;

 request_time
---------------
 123344345
 123344687
 123456329
 223344687
 1613808000000
 323344687
 123456789
 123344329
 423344687
 523344687

select todate(request_time)  from testkspc.test ;

 system.todate(request_time)
-----------------------------
                  1970-01-02
                  1970-01-02
                  1970-01-02
                  1970-01-03
                  2021-02-20
                  1970-01-04
                  1970-01-02
                  1970-01-02
                  1970-01-05
                  1970-01-07

Further you can use totimestamp function.
Directly using this function on bigint type gives error -

select totimestamp(request_time)  from testkspc.test ;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid call to function totimestamp, none of its type signatures match (known type signatures: system.totimestamp : (date) -> timestamp, system.totimestamp : (timeuuid) -> timestamp)"

We can wrap todate inside totimestamp.

select totimestamp(todate(request_time))  from testkspc.test ;

 system.totimestamp(system.todate(request_time))
-------------------------------------------------
                 1970-01-02 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-03 00:00:00.000000+0000
                 2021-02-20 00:00:00.000000+0000
                 1970-01-04 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-02 00:00:00.000000+0000
                 1970-01-05 00:00:00.000000+0000
                 1970-01-07 00:00:00.000000+0000

回复收藏 0 原文

岁月如刀 2025-02-12 04:08:20

您通过使用内置CQL函数todate（）有正确的想法。

我已经部署了一个Cassandra 3.11.0群集（与您正在运行的版本相同），并且可以确认其有效。这是一个示例输出：

cqlsh:stackoverflow> SELECT request_time, todate(request_time) FROM testbigint ;

 request_time  | system.todate(request_time)
---------------+-----------------------------
 1654671885000 |                  2022-06-08
          1654 |                  1970-01-01

在您的情况下，我相信潜在的问题是您的表在request_time_time列中包含无效的数据。您需要检查表中的数据，然后重试。

附带说明，Apache Cassandra 3.11.0在5年前发布，您甚至不应该为测试而费力使用它。在撰写本文时，当前支持的版本为C* 3.11.13。干杯!

You had the right idea by using the built-in CQL function toDate().

I've deployed a Cassandra 3.11.0 cluster (the same as the version you're running) and can confirm that it works. Here's an example output:

cqlsh:stackoverflow> SELECT request_time, todate(request_time) FROM testbigint ;

 request_time  | system.todate(request_time)
---------------+-----------------------------
 1654671885000 |                  2022-06-08
          1654 |                  1970-01-01

In your case, I believe the underlying issue is that your table contains invalid data in the request_time column. You need to check the data in your table and try again.

As a side note, Apache Cassandra 3.11.0 was released 5 years ago and you shouldn't even bother using it for testing. At the time of writing, the current supported version is C* 3.11.13. Cheers!

回复收藏 0 原文

~没有更多了~