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 D3F0217E8E for ; Sat, 14 Feb 2015 23:22:24 +0000 (UTC) Received: (qmail 65953 invoked by uid 500); 14 Feb 2015 23:21:41 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 65925 invoked by uid 500); 14 Feb 2015 23:21:40 -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 65915 invoked by uid 99); 14 Feb 2015 23:21:40 -0000 Received: from Unknown (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 14 Feb 2015 23:21:40 +0000 X-ASF-Spam-Status: No, hits=-6.5 required=5.0 tests=ENV_AND_HDR_SPF_MATCH,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS,USER_IN_DEF_SPF_WL X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of michael.laing@nytimes.com designates 209.85.192.41 as permitted sender) Received: from [209.85.192.41] (HELO mail-qg0-f41.google.com) (209.85.192.41) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 14 Feb 2015 23:21:19 +0000 Received: by mail-qg0-f41.google.com with SMTP id i50so18589743qgf.0 for ; Sat, 14 Feb 2015 15:19:20 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:content-type; bh=Rb1i3Nuwi79k4njWh5x3AlaU2X5uRBmfqdfyIrjrp3Y=; b=R9lvB758B1qB1MWIDDtQPlWWMiKZYaq+fKbaWsnice9I0y6bNBQbXpAM/8h3VyRrqJ 0BPi1GTmIlrBbifRwAVaarludLoaANv4tiFyQUfmcgIaXNHgdFH7LJ14+JFAyMWS31ye 4Ajy2+h4UzrvB1BW1oYB/TlGTWgLE0yyO3hkegQYAheq4FJRqfyLZM30TVMRMH/Q5SsP I3Cax4CUTOYMiiaclxbVVjyukGe4Z4aFc06xqAilnhsCCmq/K3bip4Odykwh/HgbpDqY LYG+zBYv2vioZ+q/3tn38Cte/U2MQ5oHvCh3AqI0b2e6v7M0Mrc6W1+wXu7U473tzzoN ALrw== X-Gm-Message-State: ALoCoQmWytP3ObTFqej5CUDxtUHhwFJA7mxWZdY9mX5fFjqFfaNDUxNxscxedoCHzzNb5Pwct5tQ MIME-Version: 1.0 X-Received: by 10.140.98.38 with SMTP id n35mr40508950qge.62.1423955960196; Sat, 14 Feb 2015 15:19:20 -0800 (PST) Received: by 10.140.92.68 with HTTP; Sat, 14 Feb 2015 15:19:20 -0800 (PST) In-Reply-To: References: Date: Sat, 14 Feb 2015 18:19:20 -0500 Message-ID: Subject: Re: Storing bi-temporal data in Cassandra From: "Laing, Michael" To: "user@cassandra.apache.org" Content-Type: multipart/alternative; boundary=001a113aaa422f0b6f050f1495b5 X-Virus-Checked: Checked by ClamAV on apache.org --001a113aaa422f0b6f050f1495b5 Content-Type: text/plain; charset=UTF-8 Perhaps you should learn more about Cassandra before you ask such questions. It's easy if you just look at the readily accessible docs. ml On Sat, Feb 14, 2015 at 6:05 PM, Raj N wrote: > 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 >>> >> >> > --001a113aaa422f0b6f050f1495b5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Perhaps you should learn more about Cassandra before you a= sk such questions.

It's easy if you just look at the= readily accessible docs.

ml

On Sat, Feb 14, 2015 at 6:0= 5 PM, Raj N <raj.cassandra@gmail.com> wrote:
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 <doanduyhai@gmail.com= > wrote:
"I am trying to get the state as of a particular = transaction_time"=C2=A0

=C2= =A0--> In that case you should probably define your primary key in anoth= er order for clustering columns

PRI= MARY KEY (weatherstation_id,transaction_time,event_time)

Then,=C2=A0= select * from temperatures where=C2=A0weatherstation_id =3D 'foo' a= nd event_time >=3D '2015-01-01 00:00:00' and event_time < = 9;2015-01-02 00:00:00'=C2=A0and=C2=A0transaction_= time =3D 'xxxx'



On Sat, Feb 14, 2015 at 3:06 AM, Raj N <raj.cassandra@gmail.c= om> wrote:
Has anyone de= signed a bi-temporal table in Cassandra? Doesn't look like I can do thi= s using CQL for now. Taking the time series example from well known modelin= g tutorials in Cassandra -

CREATE TABLE t= emperatures (
weatherstation_id text,
event_time timestamp,
temperature text,
PRIMARY KEY= (weatherstation_id,event_time),
) WITH C= LUSTERING ORDER BY (event_time DESC);

=
If I add another column=C2=A0transaction_time
<= br>
CREATE TABLE temperatures (
weatherstation_id text,
event_time timestamp,
transact= ion_time timestamp,
temperature text,
PRIMARY KEY (weathe= rstation_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=C2=A0throws an error -

select * from temperatures where=C2=A0weatherstation_id =3D 'fo= o' 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'2015-01-02 00:00:00'

It works if I use= an equals clause for=C2=A0the event_time. I am trying to get the state as = of a particular transaction_time

-Raj



--001a113aaa422f0b6f050f1495b5--