How to correctly use strftime and datetime using Room library?

Question

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

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

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();
    }
}

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 methods:

    @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();

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

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]

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

How to solve this problem?

Answer 1

You miss 2 things here :

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

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

And same for month:

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