Room Sqlite查询以返回保存为字符串的日期的unix时间戳

Question

嘿，伙计们，我会保持简单，并显示表/模型和查询的代码。尝试将日期作为unix时间戳返回。当前，查询没有从数据库中返回任何内容。

请参阅下面的代码片段

表类

@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类用于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与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

您需要一个类型转换器。从文档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();
}

}