strftim() in sqlite gives wrong results

Question

I store date field in the database as number of seconds since epoch:

For the timestamp in the image (1550591783—representing 2019-02-19 19:26:23), sqlite should return 50 as day of the year but it returns 40.

This is the query in PurchaseDao:

@Query("SELECT strftime('%j', date, 'unixepoch', 'localtime') AS day " +
       "FROM purchase " +
       "WHERE ...")
abstract List<Cost> getCosts();

and this is the date converter:

public class DateConverter {

    @TypeConverter
    public static Date fromTimestamp(Long value) {
        return value == null ? null : new Date(value * 1_000); // multiply by 1000 to make it milliseconds
    }

    @TypeConverter
    public static Long toTimestamp(Date date) {
        return date == null ? null : date.getTime() / 1_000; // divide by 1000 to store as seconds
    }
}

Even if I pass now as the parameter to query (I've actually even applied it to a fresh method without any other distractions) I get the same wrong result:

@Query("SELECT strftime('%j', 'now', 'localtime')")

I've tried removing 'localtime' argument, changing date converters to store date as string (for example in the format 2019-02-19) and running the app in an AVD but I get the same wrong result in all cases.

On the other hand when I get day of the year with Calendar (Calendar.getInstance().get(Calendar.DAY_OF_YEAR);) or run the queries in my PC with stetho, the results are correct.

Any help appreciated.

Answer 1

as I've already explained it here, the conversion of UNIX time works alike this:

SELECT DATE(dateColumn, 'unixepoch') AS isodate FROM tableName

or when storing milliseconds since the epoch began, as it is common for Android Java:

SELECT DATE(ROUND(dateColumn / 1000), 'unixepoch') AS isodate FROM tableName

this doesn't require any multiplication or division in the TypeConverter. while this multiplication/division operand 1_000 in the TypeConverter looks strange to me.

the issue here might be exactly the same as with pure SQL ... that date.getTime() / 1000 in 999/1000 of cases could only be represented as a float value and not a straight long integer value - unless rounding that value to a long integer value. ROUND(dateColumn / 1000) would prevent this by SQL. somehow this question lacks the context; please comment below which particular value you'd need to obtain - and why you'd need to obtain it; then I might be able to extend my answer - because I don't really understand the purpose of getting the day of the year for a purchase. I'd rather would expect it to be days elapsed since a purchase.