Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 9D7B217E4E for ; Sat, 14 Feb 2015 23:06:22 +0000 (UTC) Received: (qmail 49954 invoked by uid 500); 14 Feb 2015 23:06:16 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 49910 invoked by uid 500); 14 Feb 2015 23:06:16 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 49900 invoked by uid 99); 14 Feb 2015 23:06:16 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 14 Feb 2015 23:06:16 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of raj.cassandra@gmail.com designates 74.125.82.51 as permitted sender) Received: from [74.125.82.51] (HELO mail-wg0-f51.google.com) (74.125.82.51) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 14 Feb 2015 23:06:11 +0000 Received: by mail-wg0-f51.google.com with SMTP id y19so23015452wgg.10 for ; Sat, 14 Feb 2015 15:05:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=XmHiOvT9bwd2X6XRp06qe/92/M6QSO2uZkJmPLieHK4=; b=g29CL19fkg1UvqtlmkkwILMDwSkzwyfEq8mY9t67RNso+fWbQhcQF5Df9Q1vWCDbpI fJRcr1E2mmYAHkfry1V40DfP61SwSqnNHHENHEsHtcSb4Afv7y71H/EybwMz/uhVv/Us DCtF7zuEu/vIFEPQk5sXfZgHzGnmMS21WAmCalCBZT1XcE4qXx/k62dbeMV30kD8m9bH ekUo0s7rv9UTfNh5/hYghz8z45arY7TFDtr6Fp3aSIA3sC5+SFgQ5yaN9w1tD05Dn3Rw 85iAe635+ddreygH3UWflVE+SncRDEDikt2KZ0qxHgHgS8DlWvOabxWjH9d+WvIIYPbO r73w== MIME-Version: 1.0 X-Received: by 10.180.188.41 with SMTP id fx9mr31029668wic.93.1423955105151; Sat, 14 Feb 2015 15:05:05 -0800 (PST) Received: by 10.194.106.3 with HTTP; Sat, 14 Feb 2015 15:05:05 -0800 (PST) In-Reply-To: References: Date: Sat, 14 Feb 2015 18:05:05 -0500 Message-ID: Subject: Re: Storing bi-temporal data in Cassandra From: Raj N To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=001a11c263143803f0050f1462de X-Virus-Checked: Checked by ClamAV on apache.org --001a11c263143803f0050f1462de Content-Type: text/plain; charset=UTF-8 I don't think thats solves my problem. The question really is why can't we use ranges for both time columns when they are part of the primary key. They are on 1 row after all. Is this just a CQL limitation? -Raj On Sat, Feb 14, 2015 at 3:35 AM, DuyHai Doan wrote: > "I am trying to get the state as of a particular transaction_time" > > --> In that case you should probably define your primary key in another > order for clustering columns > > PRIMARY KEY (weatherstation_id,transaction_time,event_time) > > Then, select * from temperatures where weatherstation_id = 'foo' and > event_time >= '2015-01-01 00:00:00' and event_time < '2015-01-02 > 00:00:00' and transaction_time = 'xxxx' > > > > On Sat, Feb 14, 2015 at 3:06 AM, Raj N wrote: > >> Has anyone designed a bi-temporal table in Cassandra? Doesn't look like I >> can do this using CQL for now. Taking the time series example from well >> known modeling tutorials in Cassandra - >> >> CREATE TABLE temperatures ( >> weatherstation_id text, >> event_time timestamp, >> temperature text, >> PRIMARY KEY (weatherstation_id,event_time), >> ) WITH CLUSTERING ORDER BY (event_time DESC); >> >> If I add another column transaction_time >> >> CREATE TABLE temperatures ( >> weatherstation_id text, >> event_time timestamp, >> transaction_time timestamp, >> temperature text, >> PRIMARY KEY (weatherstation_id,event_time,transaction_time), >> ) WITH CLUSTERING ORDER BY (event_time DESC, transaction_time DESC); >> >> If I try to run a query using the following CQL, it throws an error - >> >> select * from temperatures where weatherstation_id = 'foo' and event_time >> >= '2015-01-01 00:00:00' and event_time < '2015-01-02 00:00:00' and >> transaction_time < '2015-01-02 00:00:00' >> >> It works if I use an equals clause for the event_time. I am trying to get >> the state as of a particular transaction_time >> >> -Raj >> > > --001a11c263143803f0050f1462de Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I don't think thats solves my problem. The question re= ally is why can't we use ranges for both time columns when they are par= t of the primary key. They are on 1 row after all. Is this just a CQL limit= ation?

-Raj

On Sat, Feb 14, 2015 at 3:35 AM, DuyHai Doan <= doanduyhai@gmail.com> wrote:
"I am trying t= o get the state as of a particular transaction_time"=C2=A0
<= /div>

=C2=A0--> In that case you should probab= ly define your primary key in another order for clustering columns

PRIMARY KEY (weatherstation_id,transaction_time,event_time)

Then,=C2=A0select * from temperatures where=C2=A0we= atherstation_id =3D 'foo' and event_time >=3D '2015-01-01 00= :00:00' and event_time < '2015-01-02 00:00:00'=C2=A0and=C2= =A0transaction_time =3D 'xxxx'

<= /div>


On Sat, Feb 14, 2015= at 3:06 AM, Raj N <raj.cassandra@gmail.com> wrote:
Has anyone designed a bi-temporal table = in Cassandra? Doesn't look like I can do this using CQL for now. Taking= the time series example from well known modeling tutorials in Cassandra -<= div>
CREATE TABLE temperatures (
weatherstation_id text,
event_time timestamp,
temperature tex= t,
PRIMARY KEY (weatherstation_id,event_t= ime),
) WITH CLUSTERING ORDER BY (event_t= ime DESC);

If I add another column=C2=A0tra= nsaction_time
<= span style=3D"font-size:15px;line-height:18px">
CREATE TABLE temperatures (
weatherstation_id text,
event_time ti= mestamp,
transaction_time timestamp,=
temperature text,
PRIMARY KEY (weatherstation_id,event_time,tran= saction_time),
) WITH CLUSTERING ORDER BY= (event_time DESC, transaction_time DESC);

If I try to run a query using= the following CQL, it=C2=A0throws an error -

select * from tempe= ratures where=C2=A0weatherstation_id =3D 'foo' and event_time >= =3D '2015-01-01 00:00:00' and event_time < '2015-01-02 00:00= :00'=C2=A0and transaction_time <=C2=A0&= #39;2015-01-02 00:00:00'

<= /font>
It works if I use an equals clause for=C2= =A0the event_time. I am trying to get the state as of a particular transact= ion_time

-Raj
=


--001a11c263143803f0050f1462de--