How to delete old history data automatically

[mobizt]

When we concern about the growth of data stores in the database.

We may delete the old data manually or with the help of other cloud functions.

With this Firebase library, you can use Query to select the old data at a specific node which its timestamp value exceeds the duration that we want to keep our data.

If our data structure is similar to the below picture.

Which we have the sensor data that shore periodically at some interval. The data was pushed to Firebase which Firebase server creates the random UID for our new data as in the picture.

We also push timestamp (in seconds from midnight January 1, 1970) along with our sensor values. We will use this timestamp for the data query.

Now to query the data with a timestamp, we need to set the system time of device and set the database rules for indexing the data that we want to query (the node named time for this case).

To set the system time to the device (ESP32) we use this helper function, setClock.

bool setClock(float gmtOffset, float daylightOffset)
{
  configTime((gmtOffset) * 3600, (daylightOffset) * 60, "pool.ntp.org", "time.nist.gov", NULL);

  time_t now = time(nullptr);
  int cnt = 0;
  while (now < 8 * 3600 * 2 && cnt < 20)
  {
    delay(50);
    now = time(nullptr);
    cnt++;
  }

  bool clockReady = now > 8 * 3600 * 2;
  return clockReady;
}

For setting the Firebase's database rules we need this helper function, setDatabaseRules.

void setDatabaseRules()
{
  if (Firebase.getRules(firebaseData))
  {
    FirebaseJsonData jdata;
    FirebaseJson &json = firebaseData.jsonObject();
    bool ruleExisted = false;
    //SENSOR is the path which we want to query its children nodes
    String path = "rules/SENSOR/.indexOn";
    json.get(jdata, path);
    if (jdata.success && jdata.stringValue == "time")
      ruleExisted = true;

    if (!ruleExisted)
    {
      json.set(path, "time");
      String rules = "";
      json.toString(rules, true);
      if(!Firebase.setRules(firebaseData, rules)){
         Serial.println(firebaseData.errorReason()); 
      }
    }

    json.clear();
  }
}

After we call setDatabaseRules, our database rules will be something like this

The rules SENSOR/.indexOn was added and its value is time.

Don't worry about .read and.write rules which can be the same as the above picture or be "true" in your case.

Now we are ready to query the database data with a specific time interval.

Here is the full code.

#include <time.h>
#include <FirebaseESP32.h>
#include <WiFi.h>

#define WIFI_SSID "xxxxxxxxxxxxx"
#define WIFI_PASSWORD "xxxxxxxxxx"
#define FIREBASE_HOST "xxxxxxxx.firebaseio.com"
#define FIREBASE_AUTH "xxxxxxxxxxxxxxxxxxxxxxxxxxxx"

FirebaseData firebaseData;

void clearDatabase();
void setDatabaseRules();
bool setClock(float gmtOffset, float daylightOffset);

void setup() {

  Serial.begin(115200);

  WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
  Serial.print("connecting");
  while (WiFi.status() != WL_CONNECTED) {
    Serial.print(".");
    delay(300);
  }
  Serial.println();
  Serial.print("connected with IP : ");
  Serial.println(WiFi.localIP());
  Serial.println();

  Serial.print("Setup Clock... ");

  //if Timezone offset is 3
  if (setClock(3, 0))
    Serial.println("OK");
  else
    Serial.println("FAILED");

  Firebase.begin(FIREBASE_HOST, FIREBASE_AUTH);

  Firebase.reconnectWiFi(true);

  //Set the size of HTTP response buffers in the case where we want to work with large data.
  firebaseData.setResponseSize(1024);

  Serial.println("Add index to database rules");
  setDatabaseRules();
}

void loop() {

  //Code to push new data here
  //...

  //To delete old database
  Serial.println("Delete old database");
  clearDatabase();

  delay(5000);

}

void clearDatabase() {

  String sensorPath = "/SENSOR/-88116932";

  QueryFilter query;

  //Get the current timestamp
  time_t current_ts = time(nullptr);

  //Define the total seconds of duration form now that you need to keep the data
  unsigned long dataRetentionPeriod = 30 * 60 * 60 * 24; // 30 days

  double lastTS = current_ts - dataRetentionPeriod;

  //query for all data begin with time (timestamp) 0 to the last timestamp
  //limit the return result only last 8 data.
  query.orderBy("time").startAt(0).endAt(lastTS).limitToLast(8);

  if (Firebase.getJSON(firebaseData, sensorPath, query))
  {

    if (firebaseData.dataType() == "json" && firebaseData.jsonString().length() > 4)
    {
      //parse the query result
      FirebaseJson *myJson = firebaseData.jsonObjectPtr();
      size_t len = myJson->iteratorBegin();
      String key, value;
      int otype = 0;
      for (size_t i = 0; i < len; i++)
      {
        yield();
        myJson->iteratorGet(i, otype, key, value);
        if (otype == JSON_OBJECT && key.length() > 1)
        {
          //Here is the path of obsolete data in which its timestamp exceeds the specific period.
          String path = sensorPath + "/" + key;
          //Delete that data
          Firebase.deleteNode(firebaseData, path);
        }
      }
      myJson->iteratorEnd();
      myJson->clear();
    }
  }
  else
  {
    Serial.println(firebaseData.errorReason());
  }

  query.clear();
}

void setDatabaseRules()
{
  if (Firebase.getRules(firebaseData))
  {
    FirebaseJsonData jdata;
    FirebaseJson &json = firebaseData.jsonObject();
    bool ruleExisted = false;

    //SENSOR is the path which we want to query its children nodes
    String path = "rules/SENSOR/.indexOn";
    json.get(jdata, path);
    if (jdata.success && jdata.stringValue == "time")
      ruleExisted = true;

    if (!ruleExisted)
    {
      json.set("rules/SENSOR", "time"); 
      json.set(path, "time"); 
      String rules = "";
      json.toString(rules, true);
      if (!Firebase.setRules(firebaseData, rules)) {
        Serial.println(firebaseData.errorReason());
      }
    }

    json.clear();
  }
}

bool setClock(float gmtOffset, float daylightOffset)
{
  configTime((gmtOffset) * 3600, (daylightOffset) * 60, "pool.ntp.org", "time.nist.gov", NULL);

  time_t now = time(nullptr);
  int cnt = 0;
  while (now < 8 * 3600 * 2 && cnt < 20)
  {
    delay(50);
    now = time(nullptr);
    cnt++;
  }

  bool clockReady = now > 8 * 3600 * 2;
  return clockReady;
}

If you have a lot of old data to be deleted, every time you call clearDatabase, the last 8 obsoleted data will be deleted.

This approach doesn't require any server or system to manage data retention.