Im working on automatic silent app that has start and end time

Question

and the data is store in room database but i want solution what if start and end time is same

is there solution that database reject the same start and end time

Answer 1

what if start and end time is same

Using BETWEEN would return a row where both the start and end are the same for both the stored data and the query.

Consider the following demo (where it is the last query aka result 2) that shows this. The rest just creates the data in the database (result 1).

/* Just in case Environment is dirty */
DROP TABLE IF EXISTS example;
/* Create the table */
CREATE TABLE IF NOT EXISTS example (startdatetime INTEGER, enddatetime INTEGER);

/* Load the table with some data */
WITH cte(onedatetime) AS (
        SELECT datetime('now') UNION ALL SELECT datetime(onedatetime,'+1 Hour') FROM cte LIMIT 10
    )
INSERT INTO example SELECT onedatetime,onedatetime FROM cte;

/* The data loaded */
SELECT * FROM example;

/* The demonstration */
SELECT * FROM example WHERE startdatetime BETWEEN (SELECT startdatetime FROM example ORDER BY startdatetime ASC LIMIT 1) AND (SELECT startdatetime FROM example ORDER BY startdatetime ASC LIMIT 1);
/* Clean Up Test Environment */
DROP TABLE IF EXISTS example;

Result 1 The data that has been loaded (note that it changes for each run as the datetimes are based upon the current datetime):-

• Note that the highlighted row will be the one used for both values for the BETWEEN .... AND .... clause.

Result 2

• the subquery (SELECT startdatetime FROM example ORDER BY startdatetime ASC LIMIT 1) ensures that same date (that as per the first row in the example table)

  • obviously this would be of little use but serves to demonstrate when the start and end dates are the same.

i.e. even though both values of the BETWEEN (start and end) are the same the row still matches.

Assuming that the example table were defined as an @Entity in Room and that the start and end datetime's were to be passed then the function in the @Dao annotated interface/abstract class could be like/based upon :-

@Query("SELECT * FROM example WHERE startdatetime BETWEEN :startDateTime AND :endDateTime AND enddatetime BETWEEN :startDateTime AND :endDateTime;")
fun getExamplesBetweenDateTimes(startDateTime: String, endDateTime: String): List<Example>

Room Demo

using the @Entity:-

@Entity
data class Example(
    @PrimaryKey
    val exampleId: Long?=null,
    val startdatetime: String,
    val enddatetime: String
)

Along with the getExamplesBetweenDateTimes function above along with the other necessary code (@Database annotated abstract class, and fun to insert Examples) then using:-

    dbx = SO75530351DB.getInstance(this)
    daox = dbx.getDao()

    val dt01 = "2023-01-01 20:00"
    val dt02 = "2023-01-01 21:00"
    val dt03 = "2023-01-01 19:00"

    daox.insert(Example(startdatetime =  dt01, enddatetime =  dt01))
    daox.insert(Example(startdatetime =  dt01, enddatetime =  dt02))
    daox.insert(Example(startdatetime =  dt02, enddatetime =  dt02))
    daox.insert(Example(startdatetime =  dt02, enddatetime =  dt03))
    daox.insert(Example(startdatetime =  dt03, enddatetime =  dt03))

    for (e in daox.getExamplesBetweenDateTimes(dt01,dt01)) {
        Log.d(
            "DBINFO",
            "Example extracted where startdatetime is ${e.startdatetime} and enddatetime is ${e.enddatetime} ID is ${e.exampleId}"
        )
    }

Results in:-

D/DBINFO: Example extracted where startdatetime is 2023-01-01 20:00 and enddatetime is 2023-01-01 20:00 ID is 1

• i.e. just the single rows matches the query that the start is between 2023-01-01 20:00 AND the end is also between 2023-01-01 20:00 (not that one would typically expect anything to have the same start and end unless milliseconds were taken into consideration).

---

The Solution (a Solution)

---

is there solution that database reject the same start and end time

If it were native SQLite then a CHECK constraint, along with INSERT OR IGNORE could be used. However, although Room supports INSERT OR IGNORE e.g. @Insert(onConflict = OnConflictStrategy.IGNORE). Room does not offer support for CHECK via annotation (not impossible to implement (I believe)).

However, the following could be used to effectively mimic the CHECK :-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(example: Example): Long
@Query("")
fun insertRestrictedExample(example: Example): Long {
    if (example.startdatetime != example.enddatetime) return insert(example)
    return -1
}

• noting that the insert function would never be called directly (unless there were a need to insert with the same start and end)

Expanding the Demo accordingly

e.g. If the following were used:-

    val dt01 = "2023-01-01 20:00"
    val dt02 = "2023-01-01 21:00"
    val dt03 = "2023-01-01 19:00"

    daox.insertRestrictedExample(Example(startdatetime =  dt01, enddatetime =  dt01))
    daox.insertRestrictedExample(Example(startdatetime =  dt01, enddatetime =  dt02))
    daox.insertRestrictedExample(Example(startdatetime =  dt02, enddatetime =  dt02))
    daox.insertRestrictedExample(Example(startdatetime =  dt02, enddatetime =  dt03))
    daox.insertRestrictedExample(Example(startdatetime =  dt03, enddatetime =  dt03))

    for (e in daox.getExamplesBetweenDateTimesButNotIfSameStartAndEndPassed(dt01,dt01)) {
        Log.d(
            "DBINFO1",
            "Example extracted where startdatetime is ${e.startdatetime} and enddatetime is ${e.enddatetime} ID is ${e.exampleId}"
        )
    }
    for (e in daox.getAllFromExample()) {
        Log.d(
            "DBINFO2",
            "Example extracted where startdatetime is ${e.startdatetime} and enddatetime is ${e.enddatetime} ID is ${e.exampleId}"
        )
    }

• i.e. the new insertRestrcitedExample and a query to extract ALL rows, then the result would be

:-

D/DBINFO2: Example extracted where startdatetime is 2023-01-01 20:00 and enddatetime is 2023-01-01 21:00 ID is 1
D/DBINFO2: Example extracted where startdatetime is 2023-01-01 21:00 and enddatetime is 2023-01-01 19:00 ID is 2

i.e. only the 2 rows where the start and end differ are now inserted into the database.

You could also protect against extracting any with the same end and start by using AND :startDateTime <> :endDateTime

e.g.

@Query("SELECT * FROM example WHERE startdatetime BETWEEN :startDateTime AND :endDateTime AND enddatetime BETWEEN :startDateTime AND :endDateTime AND :startDateTime <> :endDateTime;")
fun getExamplesBetweenDateTimesButNotIfSameStartAndEndPassed(startDateTime: String, endDateTime: String): List<Example>