如何在Room数据库中按年月查询指定行（时间存为Long类型）

在Android Java项目中使用Room数据库时，通过TypeConverter将Date类型转为Long（毫秒级时间戳）存储，现在需要实现按年月查询指定数据的功能。已知针对字符串类型时间字段的查询语句如下：

SELECT id FROM things 
   WHERE MONTH(happened_at) = 1 AND YEAR(happened_at) = 2009;

示例表数据：

适配Long类型时间字段的查询实现 #

由于存储的happened_at是毫秒级时间戳（Long类型），需要先将其转换为SQLite可识别的日期格式，再使用日期函数提取年月。可以通过datetime()函数将毫秒时间戳转为日期字符串（注意需除以1000转为秒级时间戳，适配SQLite的unixepoch格式），之后用日期函数筛选年月。

修改后的查询语句 #

SELECT id FROM things 
WHERE MONTH(datetime(happened_at / 1000, 'unixepoch')) = 1 
  AND YEAR(datetime(happened_at / 1000, 'unixepoch')) = 2009;

或者使用strftime()函数（更符合SQLite的使用习惯）：

SELECT id FROM things 
WHERE strftime('%m', datetime(happened_at / 1000, 'unixepoch')) = '01' 
  AND strftime('%Y', datetime(happened_at / 1000, 'unixepoch')) = '2009';

在Room DAO中的使用示例 #

@Dao
public interface ThingDao {
    @Query("SELECT id FROM things WHERE MONTH(datetime(happened_at / 1000, 'unixepoch')) = :month AND YEAR(datetime(happened_at / 1000, 'unixepoch')) = :year")
    List<Integer> getIdsByMonthAndYear(int month, int year);
}

现有TypeConverter代码 #

import androidx.room.TypeConverter;
import java.util.Date;  

public class DateConverter {     
    @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();
    } 
}

内容的提问来源于stack exchange，提问作者Sri