Apache Cassandra 1.2 Documentation

Determining time-to-live and write time

The Cassandra 1.2 documentation is transitioning to a new format!
Please use the new Cassandra 1.2 documentation instead.		 Back to Table of Contents
All Documents List     

You can use these functions in a SELECT statement to determine:

  • TTL: How much longer an expiring column has to live
  • WRITETIME: The date/time that a write to a column occurred

Determining how long a column has to live

This procedure creates a table, inserts data into two columns, and calls the TTL function to retrieve the date/time of the writes to the columns.

  1. Create a users table named clicks in the excelsior keyspace.

    CREATE TABLE excelsior.clicks (
  userid uuid,
  url text,
  date timestamp,  //unrelated to WRITETIME discussed in the next section
  name text,
  PRIMARY KEY (userid, url)
);

  2. Insert data into the table, including a date in yyyy-mm-dd format, and set that data to expire in a day (86400 seconds).

    INSERT INTO excelsior.clicks (
  userid, url, date, name)
  VALUES (
    3715e600-2eb0-11e2-81c1-0800200c9a66,
    'http://apache.org',
    '2013-10-09', 'Mary')
    USING TTL 86400;

    To set time-to-live on a column, insert the new data, the primary key, and selected columns followed by the USING TTL clause.

  3. Wait for a while and then issue a SELECT statement to determine how much longer the data entered in step 2 has to live.

    SELECT TTL (name) from excelsior.clicks
  WHERE url = 'http://apache.org';

    Output is, for example, 85908 seconds:

     ttl(name)
-----------
 85908

Determining the date/time that a write occurred

Using the WRITETIME function in a SELECT statement returns the date/time in microseconds that the column was written to the database. This procedure continues the example from the previous procedure and calls the WRITETIME function to retrieve the date/time of the writes to the columns.

  1. Insert more data into the table.

    INSERT INTO excelsior.clicks (
  userid, url, date, name)
  VALUES (
    cfd66ccc-d857-4e90-b1e5-df98a3d40cd6,
    'http://google.com',
    '2013-10-11', 'Bob'
);

  2. Retrieve the date/time that the value Mary was written to the name column. Use the WRITELINE function in a SELECT statement, followed by the name of a column in parentheses:

    SELECT WRITETIME (name) FROM excelsior.clicks
  WHERE url = 'http://apache.org';

     writetime(name)
------------------
 1353010594789000

    The writetime output in microseconds converts to November 15, 2012 at 12:16:34 GMT-8.

  3. Retrieve the date/time that the timestamp value 2013-10-09 was written to the date column.

    SELECT WRITETIME (date) FROM excelsior.clicks
  WHERE url = 'http://google.org';

     writetime(date)
------------------
 1353010564029000

    The writetime output in microseconds converts to November 15, 2012 at 12:16:04 GMT-8.