Managing Time to Live Values

Time to Live (TTL) values indicate how long data can exist in a table before it expires. Expired data can no longer be returned as part of a query.

Default TTL values can be set on either a table-level or a row level when the table is first defined. Using UPDATE statements, you can change the TTL value for a single row.

You can see a row's TTL value using the remaining_hours(), remaining_days() or expiration_time() built-in functions. These TTL functions require a row as input. We accomplish this by using the $ as part of the table alias. This causes the table alias to function as a row variable.

sql-> SELECT remaining_days($j) AS Expires 
    FROM JSONPersons $j WHERE id = 6;
 +---------+
 | Expires |
 +---------+
 |      -1 |
 +---------+

1 row returned
sql-> 

The previous query returns -1. This means that the row has no expiration time. We can specify an expiration time for the row by using an UPDATE statement with a set TTL clause. This clause computes a new TTL by specifying an offset from the current expiration time. If the row never expires, then the current expiration time is 1970-01-01T00:00:00.000. The value you provide to set TTL must specify units of either HOURS or DAYS.

sql-> UPDATE JSONPersons $j
          SET TTL 1 DAYS
          WHERE id = 6
          RETURNING remaining_days($j) AS Expires;
 +---------+
 | Expires |
 +---------+
 |       1 |
 +---------+

1 row returned
sql-> 

To see the new expiration time, we can use the built-in expiration_time() function. Because we specified an expiration time based on a day boundary, the row expires at midnight of the following day (expiration rounds up):

sql-> SELECT current_time() AS Now, 
    expiration_time($j) AS Expires 
    FROM JSONPersons $j WHERE id = 6;
 +-------------------------+-------------------------+
 |           Now           |         Expires         |
 +-------------------------+-------------------------+
 | 2017-07-03T21:56:47.778 | 2017-07-05T00:00:00.000 |
 +-------------------------+-------------------------+

1 row returned
sql-> 

To turn off the TTL so that the row will never expire, specify a negative value, using either HOURS or DAYS as the unit:

sql-> UPDATE JSONPersons $j
          SET TTL -1 DAYS
          WHERE id = 6
          RETURNING remaining_days($j) AS Expires;
 +---------+
 | Expires |
 +---------+
 |       0 |
 +---------+

1 row returned
sql-> 

Notice that the RETURNING clause provides a value of 0 days. This indicates that the row will never expire. Further, if we look at the remaining_days() using a SELECT statement, we will once again see a negative value, indicating that the row never expires:

sql-> SELECT remaining_days($j) AS Expires 
          FROM JSONPersons $j WHERE id = 6;
 +---------+
 | Expires |
 +---------+
 |      -1 |
 +---------+

1 row returned
sql->