Room Sqlite Query to return unix time stamp from date saved as String

Question

Hey guys I will keep this simple and show the code of the table/model and query. Trying to get the date to be returned as a unix time stamp. Currently the query isn't returning anything from the database.

Please see code snippets below

TABLE CLASS

@Entity(tableName = "moodBeforeTable")
public class MoodBeforeTable {

@PrimaryKey(autoGenerate = true)
private long moodBeforePK;

@NonNull
@ColumnInfo(name = "userId")
private int userId;

@NonNull
@ColumnInfo(name = "moodBefore")
private int moodBefore;

@NonNull
@ColumnInfo(name = "workoutDate")
private String workoutDate;

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

---

POJO CLASS used for JOINS

public class MoodStatsLineGraphModel {

private long day, dayAfter;
private int moodBefore;
private long userId;
private long cbtId;
private int moodAfter;
private String workoutDate;

**Constructors getters/setters not shown to save space**
}

---

DAO with the QUERY

@Dao
public interface MoodStatsLineGraphDao {

@Query("SELECT strftime ('%s',moodBeforeTable.workoutDate)AS 'day', strftime ('%s',moodAfterTable.workoutDate) AS 'dayAfter', moodBeforeTable.moodBefore, moodBeforeTable.userId, moodBeforeTable.workoutDate, moodBeforeTable.cbtId, moodAfterTable.moodAfter, moodAfterTable.workoutDate " +
        "FROM moodBeforeTable " +
        "LEFT JOIN moodAfterTable ON moodBeforeTable.cbtId = moodAfterTable.cbtId " +
        "WHERE DATE(moodBeforeTable.workoutDate) >= datetime('now', '-1 month') " +
        //"AND moodBeforeTable.userId = '$getId' " +
        "OR moodBeforeTable.cbtId = NULL " +
        "ORDER BY moodBeforeTable.workoutDate ASC")
LiveData<List<MoodStatsLineGraphModel>> getMoodStatsMonthly();

Answer 1

You need a type converter. from the docs https://developer.android.com/training/data-storage/room/referencing-data#java:

public class Converters {
    @TypeConverter
    public static Date fromTimestamp(Long value) {
        return value == null ? null : new Date(value);
    }

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

}