处理Cassandra时间戳 [英] Dealing with Cassandra Timestamp
问题描述
最近,我开始研究cassandra,在使用cql和Java处理cassandra时间戳时遇到一些问题。
下面是我的示例cassandra表架构。
创建表emp (
empid int,
create_date时间戳,
deptid int,
PRIMARY KEY(empid,create_date)
)
以下是我的问题:
1)实际上我需要仅日期(我不担心时间),在我的表模式中,我使用了timestamp数据类型,是否有日期之类的数据类型只存储日期而不是时间戳。
2)如果下面没有替代时间戳数据类型的方法,那么我将如何手动将记录插入表中
插入emp(empId,deptId,create_date)值(1,2,'2016-03-15 00:00:00 + 0000');
当我尝试使用cql查询检索记录时,如下所示
从emp中选择*,其中create_date ='2016-03-15'和empid = 1;
它不返回任何记录,所以我在查询下面使用
从emp中选择*,其中create_date ='2016-03-15 00:00:00 + 0000'和empid = 1;
上面的查询返回了记录,因此如何查询以便只获取带有日期的记录。 / p>
3)我正在使用datastax 2.1 JAVA API与cassandra通信,下面是我从Java检索时间戳列的代码。
row.getDate( create_date)
getDate方法返回java.util.date(更高版本返回 com.datastax.driver.core.LocalDate )
当我尝试从代码中获取日期时,如果表中的记录为 2016-03-15 00:00:00 + 0000 ,则返回的日期减少了一天,但是从我的代码中显示 2016-03-14 EDT 之类的东西(由于时区问题,这少了一天),如果它的时区问题应该在检索时在Java代码中设置时区
4)如果我想查询我的基于empId和create_date的Java代码并获取记录,如何制作精确的模式就像Java代码中的 2016-03-15 00:00:00 + 0000
一样,因为如果仅在条件中提供日期或是否有更好的方法,则无法检索
回到您的示例:
插入emp(empId,deptId,create_date)值(1,2,'2016-03-15 00:00:00 + 0000');
上面的语句将为2016-03-15 00:00 UTC创建一个时间戳值。
select * from emp其中create_date ='2016-03-15'和empid = 1;
选择将匹配根据您当地时区的给定日期中创建的时间戳记。您要做的是使用 create_date ='2016-03-15Z'
通过UTC进行专门查询。
由于Java中还存在与时区相关的问题,最简单的选择就是简单地使用日期的字符串表示形式。
Recently I have started working on cassandra and I have some issues dealing with cassandra timestamp using cql and Java.
Below is my sample cassandra table schema.
CREATE TABLE emp (
empid int,
create_date timestamp,
deptid int,
PRIMARY KEY (empid, create_date)
)
Here are my questions below:
1) Actually I need only date(I am not worried about time), In my table schema I have used timestamp datatype, Is there any datatype like date to store only date instead timestamp.
2) If there is no alternative to timestamp datatype below is how I am inserting record into table manually
Insert into emp(empId, deptId, create_date) values(1,2,'2016-03-15 00:00:00+0000');
When I am trying to query to retrieve record using cql as below
select * from emp where create_date='2016-03-15' and empid=1;
It's not returning any record so I have use below query
select * from emp where create_date='2016-03-15 00:00:00+0000' and empid=1;
above query returned record, So how to query so that I need to get records only with date.
3) I am using datastax 2.1 JAVA API to communicate to cassandra, Below is my code how I am retrieving timestamp column from Java.
row.getDate("create_date")
getDate method is returning java.util.date (later versions returning com.datastax.driver.core.LocalDate)
When I try to get date from code its returning one day less suppose if record in my table is 2016-03-15 00:00:00+0000, but from my code it shows something like 2016-03-14 EDT (one day less is this because of timezone issue), If its timezone issue should I set timezone in my Java code while retrieving
4) If I want to query and get record from my Java code based on empId and create_date how to make exact pattern like 2016-03-15 00:00:00+0000
in Java code, As it is not retrieving if I provide only date in where condition or is there any better way in retrieving using Java code.
Getting back to your example:
Insert into emp(empId, deptId, create_date) values(1,2,'2016-03-15 00:00:00+0000');
The statement above will create a timestamp value for 2016-03-15 00:00 UTC.
select * from emp where create_date='2016-03-15' and empid=1;
The select will match against a timestamp created from the given date in your local timezone. What you have to do here is to query specifically by UTC, using create_date='2016-03-15Z'
.
As you're also having timezone related issues in Java the easiest option would be to simply use a string representation of the date.
这篇关于处理Cassandra时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!