如何使用Room库正确使用strftime和datetime? [英] How to correctly use strftime and datetime using Room library?

问题描述

我有一个实体 Memo 类.我使用 @TypeConverter 将 GregorianCalendar 转换为 Long .

I have an entity Memo class. I use @TypeConverter to convert GregorianCalendar to Long.

Memo.java

Memo.java

@Entity
public class Memo {
    @Ignore
    public static final int TYPE_EXPENSE = 0;
    @Ignore
    public static final int TYPE_INCOME = 1;

    @PrimaryKey(autoGenerate = true)
    public int id;

    public int type;
    @ColumnInfo(name = "item_name")
    public String itemName;
    @ColumnInfo(name = "category_name")
    public String categoryName;
    public String note;
    public double dollar;
    public GregorianCalendar date;
    public String photoPath;

    @Ignore
    public Memo(int type) {
        this.type = type;
        itemName = "";
        categoryName = "";
        note = "";
        dollar = 0d;
        date = new GregorianCalendar();
        photoPath = "";
    }

    public Memo() {
        this(TYPE_EXPENSE);
    }

    @Ignore
    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("id = " + id);
        builder.append(", itemName = " + itemName);
        builder.append(", type = " + type);
        builder.append(", categoryName = " + categoryName);
        builder.append(", note = " + note);
        builder.append(", date = " + date.getTimeInMillis());
        return builder.toString();
    }
}

Converters.java

Converters.java

public class Converters {
    @TypeConverter
    public static GregorianCalendar fromTimestamp(Long value) {
        if (value == null)
            return new GregorianCalendar();
        else {
            GregorianCalendar calendar = new GregorianCalendar();
            calendar.setTimeInMillis(value);
            return calendar;
        }
    }

    @TypeConverter
    public static Long millisToTimestamp(GregorianCalendar calendar) {
        return calendar == null ? null : calendar.getTimeInMillis();
    }
}

我想获取数据库中的第一条记录和最后一条记录，并获得 MemosWithTimestamp 对象作为结果.

I want to get the first and the last record in the database and get the MemosWithTimestamp object as a result.

public class MemoWithTimestamp {
    public int year;
    public int month;
    @Embedded
    public Memo memo;

    @Ignore
    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("year=" + year);
        builder.append(", month=" + month);
        builder.append(", memo=[ "+memo.toString()+"]");
        return builder.toString();
    }
}

查询方法:

    @Query("SELECT *, CAST(strftime('%Y', datetime(date)) AS int) AS year, CAST(strftime('%m', datetime(date)) AS int) AS month FROM memo ORDER BY date DESC LIMIT 1")
    MemoWithTimestamp getTheLastMemo();

    @Query("SELECT *, CAST(strftime('%Y', datetime(date)) AS int) AS year, CAST(strftime('%m', datetime(date)) AS int) AS month FROM memo ORDER BY date ASC LIMIT 1")
    MemoWithTimestamp getTheFirstMemo();

Logcat中数据库中的数据:

Data in the database in the Logcat:

id = 1, itemName = memo-2015-1-0, type = 0, categoryName = , note = , date = 1422634536401 <=first
....
id = 197, itemName = memo-2017-12-9, type = 0, categoryName = , note = , date = 1515428136414 <=last

在Logcat中查询后的结果:

Results after the query in the Logcat:

firstMemo= year=0, month=0, memo=[ id = 1, itemName = memo-2015-1-0, type = 0, categoryName = , note = , date = 1422634536401]

lastMemo= year=0, month=0, memo=[ id = 197, itemName = memo-2017-12-9, type = 0, categoryName = , note = , date = 1515428136414]

但是无论是否使用 CAST ，年份和月份字段始终为0.

But the year and month fields always get 0 with or without CAST.

如何解决这个问题?

推荐答案

您在这里错过了两件事:

You miss 2 things here :

1) datetime()函数获取秒数，并且您经过的毫秒数.将值除以1000.

1) datetime() function gets seconds and you are passing milliseconds. Divide value by 1000.

2)您应该将第二个参数作为'unixepoch'传递给 datetime()函数.

2) You should pass second parameter as 'unixepoch' to datetime() function.

因此，您的查询固定如下:

So, your query is fixed like this :

CAST(strftime('%Y', datetime(date/1000, 'unixepoch')) AS int) AS year

与月份相同:

CAST(strftime('%m', datetime(date/1000, 'unixepoch')) AS int) AS month 

这篇关于如何使用Room库正确使用strftime和datetime?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！