How to control the frequency of sensors values being recorded in db browser SQLite?

Question

I'm recording values of multiple mobile sensors in kotlin and storing them in DB Browser SQLite database. The problem I'm facing is that I'm getting too much values getting stored in database aprox 3000+ values in 2 minutes. I want to create a dataset of sensor values and store values of 1 week of mobile sensors during normal usage. I've tried applying different logics but its not working.

I've tried applying timer logic but it's still not working. I need about 6000 to 8000 values only to be stored in database in 1 week so that i can train my ML model. I want to control the frequency of values being recorded in the database. Below is the code for recording sensors.

package com.example.sensorsapp

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.hardware.Sensor
import android.hardware.SensorEvent
import android.hardware.SensorEventListener
import android.hardware.SensorManager
import android.os.Bundle
import android.widget.Button
import android.widget.TextView
import androidx.appcompat.app.AppCompatActivity
import java.util.concurrent.Executors
import java.util.concurrent.ScheduledExecutorService
import java.util.concurrent.TimeUnit

class MainActivity : AppCompatActivity(), SensorEventListener {

    private lateinit var sensorManager: SensorManager
    private lateinit var accelerometer: Sensor
    private lateinit var gyroscope: Sensor
    private lateinit var gravity: Sensor
    private lateinit var proximity: Sensor
    private lateinit var orientation: Sensor
    private lateinit var rotationVector: Sensor
    private lateinit var linearAcceleration: Sensor
    private lateinit var dbHelper: MyDatabaseHelper
    private lateinit var db: SQLiteDatabase
    private lateinit var readingTextView: TextView
    private lateinit var gyroscopeTextView: TextView
    private lateinit var gravityTextView: TextView
    private lateinit var proximityTextView: TextView
    private lateinit var orientationTextView: TextView
    private lateinit var rotationVectorTextView: TextView
    private lateinit var linearAccelerationTextView: TextView
    private lateinit var startStopButton: Button
    private var isRecording = false

    private lateinit var executor: ScheduledExecutorService
    private val recordInterval = 300000L // Record every 5 minutes
    private var lastRecordTimestamp = 0L
    private val recordedData = mutableMapOf<Int, Triple<Float, Float, Float>>()

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        // Initialize UI elements
        readingTextView = findViewById(R.id.readingTextView)
        startStopButton = findViewById(R.id.startStopButton)
        gyroscopeTextView = findViewById(R.id.gyroscopeTextView)
        gravityTextView = findViewById(R.id.gravityTextView)
        proximityTextView = findViewById(R.id.proximityTextView)
        orientationTextView = findViewById(R.id.orientationTextView)
        rotationVectorTextView = findViewById(R.id.rotationVectorTextView)
        linearAccelerationTextView = findViewById(R.id.linearAccelerationTextView)
        startStopButton = findViewById(R.id.startStopButton)

        // Initialize the database helper
        dbHelper = MyDatabaseHelper(this)
        db = dbHelper.writableDatabase

        // Initialize the sensor manager and sensors
        sensorManager = getSystemService(Context.SENSOR_SERVICE) as SensorManager
        accelerometer = sensorManager.getDefaultSensor(Sensor.TYPE_ACCELEROMETER)
        gyroscope = sensorManager.getDefaultSensor(Sensor.TYPE_GYROSCOPE)
        gravity = sensorManager.getDefaultSensor(Sensor.TYPE_GRAVITY)
        proximity = sensorManager.getDefaultSensor(Sensor.TYPE_PROXIMITY)
        orientation = sensorManager.getDefaultSensor(Sensor.TYPE_ORIENTATION)
        rotationVector = sensorManager.getDefaultSensor(Sensor.TYPE_ROTATION_VECTOR)
        linearAcceleration = sensorManager.getDefaultSensor(Sensor.TYPE_LINEAR_ACCELERATION)

        if (accelerometer != null) {
            sensorManager.registerListener(this, accelerometer, SensorManager.SENSOR_DELAY_NORMAL)
        }
        if (gyroscope != null) {
            sensorManager.registerListener(this, gyroscope, SensorManager.SENSOR_DELAY_NORMAL)
        }
        if (gravity != null) {
            sensorManager.registerListener(this, gravity, SensorManager.SENSOR_DELAY_NORMAL)
        }
        if (proximity != null) {
            sensorManager.registerListener(this, proximity, SensorManager.SENSOR_DELAY_NORMAL)
        }
        if (orientation != null) {
            sensorManager.registerListener(this, orientation, SensorManager.SENSOR_DELAY_NORMAL)
        }
        if (rotationVector != null) {
            sensorManager.registerListener(this, rotationVector, SensorManager.SENSOR_DELAY_NORMAL)
        }
        if (linearAcceleration != null) {
            sensorManager.registerListener(this, linearAcceleration, SensorManager.SENSOR_DELAY_NORMAL)
        }

        startStopButton.setOnClickListener {
            if (isRecording) {
                stopRecording()
            } else {
                startRecording()
            }
        }
    }

    override fun onSensorChanged(event: SensorEvent?) {
        if (event == null) return

        val currentTime = System.currentTimeMillis()

        when (event.sensor.type) {
            Sensor.TYPE_ACCELEROMETER, Sensor.TYPE_GYROSCOPE, Sensor.TYPE_GRAVITY, Sensor.TYPE_LINEAR_ACCELERATION -> {
                val xValue = event.values[0]
                val yValue = event.values[1]
                val zValue = event.values[2]
                //val reading = "Accelerometer: X: $xValue, Y: $yValue, Z: $zValue"
                //readingTextView.text = reading

                if (isRecording) {
                    val sensorId = event.sensor.type
                    val previousValues = recordedData[sensorId]

                    if (previousValues == null ||
                        currentTime - lastRecordTimestamp >= recordInterval
                    ) {
                        // Record the data if it's the first reading or the specified time interval has passed
                        recordSensorData(event.sensor, xValue, yValue, zValue)
                        lastRecordTimestamp = currentTime
                    } else {
                        // Compare the current values with the previous values and record if they differ significantly
                        val deltaThreshold = 1.0f // Adjust as needed
                        if (Math.abs(xValue - previousValues.first) >= deltaThreshold ||
                            Math.abs(yValue - previousValues.second) >= deltaThreshold ||
                            Math.abs(zValue - previousValues.third) >= deltaThreshold
                        ) {
                            recordSensorData(event.sensor, xValue, yValue, zValue)
                            lastRecordTimestamp = currentTime
                        }
                    }
                }
            }
        }
    }

    override fun onAccuracyChanged(sensor: Sensor?, accuracy: Int) {
        // Handle accuracy change if needed
    }

    private fun startRecording() {
        isRecording = true
        startStopButton.text = "Stop Recording"
    }

    private fun stopRecording() {
        isRecording = false
        startStopButton.text = "Start Recording"
    }

    private fun recordSensorData(sensor: Sensor, xValue: Float, yValue: Float, zValue: Float) {
        val sensorName = sensor.getStringType()
        val values = ContentValues().apply {
            put(MyDatabaseHelper.TIMESTAMP, System.currentTimeMillis())
            put(MyDatabaseHelper.SENSOR_NAME, sensorName)
            put(MyDatabaseHelper.X_VALUE, xValue)
            put(MyDatabaseHelper.Y_VALUE, yValue)
            put(MyDatabaseHelper.Z_VALUE, zValue)
        }
        db.insert(MyDatabaseHelper.TABLE_NAME, null, values)
        recordedData[sensor.type] = Triple(xValue, yValue, zValue)
    }

    override fun onDestroy() {
        super.onDestroy()
        // Unregister the sensor listener and close the database when the activity is destroyed
        sensorManager.unregisterListener(this)
        db.close()
    }
}

Answer 1

If I understand the problem correctly, you could check the number of records from the last 7 days inside recordSensorData() and stop inserting the records when there's more than 8000.

But if these records are coming in that quickly (3000+ values in 2 minutes), you'll get data from a couple of minutes and then a week long break. Again - you could add some logic inside recordSensorData() to check for the timestamp of the last record added to a table, and wait for 5 minutes since the last insert for example.

Answer 2

Perhaps consider 2 tables and a TRIGGER.

The first table being a buffer to hold many sensor readings and the second table being the table that holds data from a number of buffered readings.

The TRIGGER could be triggered whenever a buffer row is inserted BUT basically be a NOOP until the buffer has reached a set size. When this condition happens then the aggregated values could be inserted into the main table (grouped per sensor) thus 1 row per the set size per sensor.

Here's an example of the underlying principle that could be the basis of a solution (even though it does not adhere to your thoughts on a solution):-

DROP TABLE IF EXISTS buffer;
DROP TABLE IF EXISTS aggregated;
DROP TRIGGER IF EXISTS aggregate_buffer;

/* The buffer holding all the readings for the interval */
CREATE TABLE IF NOT EXISTS buffer (id INTEGER PRIMARY KEY, timestamp DEFAULT ((strftime('%s','now')* 1000) + strftime('%f','now')),data REAL, sensor TEXT);
    /* */
/* The core table inwhich to store a subset of the collected data */
CREATE TABLE IF NOT EXISTS aggregated (id INTEGER PRIMARY KEY, timestamp INTEGER, data REAL, sensor TEXT);
/* The TRIGGER to handle extracting the subset data from the collected data */
CREATE TRIGGER IF NOT EXISTS aggragate_buffer 
    AFTER INSERT ON buffer /* WHENEVER A ROW IS INSERTED INTO THE buffer table */
    WHEN (SELECT count() FROM buffer) >= 3000 /* obviously whatever value is dtermined as suitable */
    BEGIN
        /* 1 load the subset data into the aggregate table */
        INSERT INTO aggregated (timestamp,data,sensor) 
            SELECT 
                min(timestamp), /* note may conflict with a previous timestamp, perhaps avg?, perhaps max? */
                avg(data), /* at a guess average would do */
                sensor /* as the group is according to sensor this value wil not be amiguous */
            FROM buffer 
            GROUP BY sensor;
        DELETE FROM buffer;
    END
;
/* Note SELECTS will both be empty */
SELECT * FROM buffer;
SELECT * FROM aggregated;
/* Insert some data into the buffer */
WITH counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter LIMIT 10000)
INSERT INTO buffer (data,sensor) SELECT random() / 10000.987654, CASE WHEN (random() % 10) THEN 'S1' ELSE 'S2' END FROM counter;
SELECT * FROM buffer;
SELECT * FROM aggregated;

/* Cleanup Demo environment */
DROP TABLE IF EXISTS buffer;
DROP TABLE IF EXISTS aggregated;
DROP TRIGGER IF EXISTS aggregate_buffer;

When the above is run then the 4 outputs (SELECTS):-

• both empty (i.e. showing no data existed before the mass insert)

• Note that 1000 rows remain as rows were delete each 3000 rows so rows 9001-10000 remain.

AS expected 6 rows 3 for each of the 2 sensors;