Regexp_substr() function Equivalent for RoomDatabase in Android

Question

I am developing an Android App for some friends to records personal information. I have a mock database with one table "Records". It has three main columns as: _date, hours and mins.

I have tested this query and It works perfectly on MySQL Mock database.

WITH 
  temp_dates AS (SELECT *, regexp_substr(_date, '\\.[0-9]*-[0-9]+') AS _dates FROM Records WHERE _date BETWEEN '2018-06-10' AND '2019-06-30')
SELECT
  _dates,
  SUM(hours) AS Hours, 
  SUM(mins) AS Mins
FROM 
  temp_dates
WHERE 
  temp_dates._dates LIKE regexp_substr(temp_dates._dates, '\\.[0-9]*-[0-9]+') 
  GROUP BY temp_dates._dates ORDER BY _dates ASC;

I need to implement this part:

regexp_substr(_date, '\\.[0-9]*-[0-9]+') AS _dates 

in a RoomDatabase @Query.

My Database extends RoomDatabase which doesn´t support regexp_substr() function.

Answer 1

I´ve solved using substr(x,y,z) function and some improvements as:

WITH 
  temp_dates AS (SELECT *, substr(_date, 1, 7) AS _dates FROM Records WHERE _date BETWEEN '2018-06-10' AND '2019-06-30')
SELECT
  _dates,
  SUM(hours) AS Hours, 
  SUM(mins) AS Mins
FROM 
  temp_dates

GROUP BY 

  temp_dates._dates

ORDER BY _dates ASC;