Room SQLITE query subtracting long timestamps

Question

I have a table which has two columns: 'TimeStart' and 'TimeFinish' These columns store a long value which gets converted from the Date format using a typeclassconverter.

I need to write a query to return all the rows in the table but I need to calculate to Total Time. So I would need to substract the long values in the Timestart and TimeFinish to receive the individual time spent for each row then total them. I want to return a value in hours for the user to se ehow many hours they have spent doing activities. I am not sure what would be the best way to go about doing this. Could someone please help me write a query that has the above functionality?

TABLE

@Entity(tableName = "tableCheckIn")
public class TableCheckIn {

@PrimaryKey(autoGenerate = true)
private long pkCheckInId;

@ColumnInfo(name = "userId")
private long fkUserId;

@ColumnInfo(name = "moodStart")
private long moodStart;

@ColumnInfo(name = "checkInChallenge")
private String checkInChallenge;

@ColumnInfo(name = "checkInGratitude")
private String checkInGratitude;

@ColumnInfo(name = "workoutStart")
@TypeConverters({TimeTypeConverter.class})
private Date workoutStart;

@ColumnInfo(name = "workoutEnd")
@TypeConverters({TimeTypeConverter.class})
private Date workoutEnd;

DATE CONVERTER

public class TimeTypeConverter {

@TypeConverter
public static Long toDate(Date date){
    return date == null ? null : date.getTime();
}

@TypeConverter
public static Date toLong(Long timestamp){
    return timestamp == null ? null : new Date(timestamp);
}

DAO

 @Query("SELECT (SUM(workoutEnd - workoutStart) / 3600) as totalWorkouts FROM tableCheckIn WHERE userId = :pkUserId")
LiveData<List<ModelStatsTotalWorkoutTime>> totalWorkoutTime(long pkUserId);

REPO

public LiveData<List<ModelStatsTotalWorkoutTime>> totalWorkoutTime(long userId) {
    return feelingFitDatabase.getUsersDao().totalWorkoutTime(userId);
}

VIEWMODEL

  public LiveData<List<ModelStatsTotalWorkoutTime>> totalWorkoutTime(long userId) {
    return repoUsers.totalWorkoutTime(userId);
}

FRAGMENT

  mViewModelUsers.totalWorkoutTime(pkUserId).observe(getViewLifecycleOwner(), new Observer<List<ModelStatsTotalWorkoutTime>>() {
        @Override
        public void onChanged(List<ModelStatsTotalWorkoutTime> modelStatsTotalWorkouts) {

            for(ModelStatsTotalWorkoutTime list: modelStatsTotalWorkouts){
                totalWorkoutTimeSeconds += list.getTotalWorkouts();
            }

            totalWorkoutTimeMinutes = totalWorkoutTimeSeconds / 60;
            totalWorkoutTimeHours = totalWorkoutTimeMinutes / 60;

            if (totalWorkoutTimeMinutes <= 59){
                tvTotalTimeWorkingOut.setText(""+totalWorkoutTimeMinutes);
                tvTotalTimeWorkingOutDescription.setText(getString(R.string.workout_minutes));
            } else {
                tvTotalTimeWorkingOut.setText(""+totalWorkoutTimeHours);
                tvTotalTimeWorkingOutDescription.setText(getString(R.string.workout_hours));
            }
        }
    });
}

Answer 1

The columns TimeStart and TimeFinish contain unix epoch timestamps that contain milliseconds.
In SQLite you can get the dates in a readable format with:

select 
  datetime(1596107885085 / 1000, 'unixepoch'),
  datetime(1596108413648 / 1000, 'unixepoch')

which returns:

2020-07-30 11:18:05     2020-07-30 11:26:53

To get their difference in minutes you must divide by 1000 to strip off the milliseconds and then divide by 60:

(TimeFinish - TimeStart) / 1000 / 60

So you can get the total difference in minutes with:

select sum(TimeFinish - TimeStart) / 1000 / 60 from tablename