Determining time-to-live for a column

Steps for creating a table, inserting data into two columns, and calling the TTL function to retrieve the date/time of the writes to the columns.

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.

Procedure

  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). Use the USING TTL clause to set the expiration period.
    INSERT INTO excelsior.clicks (
      userid, url, date, name)
      VALUES (
        3715e600-2eb0-11e2-81c1-0800200c9a66,
        'http://apache.org',
        '2013-10-09', 'Mary')
        USING TTL 86400;
  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' ALLOW FILTERING;
    Output is, for example, 85908 seconds:
     ttl(name)
    -----------
     85908
                        
    (1 rows)