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.

Prestissimo

Are you certain that is the timestamp being evaluated? Do you get the same result if you hardcode the timestamp in the query? – Lune

@GabrielNegut Yes I get the same result with the hardcoded 1550591783. And the thing is that when I run the query on the app database in my computer (with stetho) the result is correct. – Prestissimo

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.

Wrapping

I want to show the total costs per day from for example 90 days ago until today. So it seems that the only formatters that can give unique results (unique day numbers) are %j and %J. I think you didn't read the question. Even if I run a query with now argument, the result is wrong: @Query("SELECT strftime('%j', 'now', 'localtime')") Anyway I tried your solution and got another wrong result. – Prestissimo

@Prestissimo you should rather use domain aggregate function SUM() for this, combined with WHERE dateColumn IN BETWEEN and GROUP BY isodate. that's the beauty of SQL and much easier than doing this "manually". noSQL does not support this. – Wrapping

Yes I've used group by day already but did not mention it in my query in the question. The problem is still day of the year! – Prestissimo

@Prestissimo this is not required, when converting to isodate... because then GROUP BY can be applied - which cannot be applied as long as it is numeric values (which do represent seconds or milliseconds, but not represent single days, by which one could GROUP BY). – Wrapping

Please note again that the group by and sum work as expected. The problem is that it gives results in wrong day numbers – Prestissimo

@Prestissimo you'd just need to use DATE() instead of DATETIME() ...then the dimension to GROUP BY would meet the requirements; else it would still GROUP BY seconds. localtime might also cause an unexpected offset... so that people from different places will get different results (as the comments above might hint for). the offset to UTC still needs to be considered, by adding or subtracting it from start/end time. – Wrapping

to explain the underlying problem, which leads to these unexpected values: when living in Germany (as I do), one usually does not have to consider the offset, because it will always be within the common business hours, with an offset of only +1:00h (unless it is a web-store, which is open 24/7). just take a piece of paper and do the math... this offset in hours can be translated to seconds by *3600, where the start and end time have to be shifted forwards or backwards, depending on the +/- prefix of the offset. – Wrapping

Answer 2

Maybe its a bug in the strftime day-of-the-year implementation somewhere; I'm getting the correct result. If updating SQLite or libraries is not an option then maybe you could work around the issue by subtracting the Julian day of the first of January of the year of date from the Julian day of date (simplified and corrected after comments):

SELECT cast (
       julianday(date, 'unixepoch', 'localtime') 
     - julianday(date, 'unixepoch', 'localtime', 'start of year')
     as int
     )
     + 1
from Purchase

That of course assumes that the julianday function works OK.

Rife

Your query can be simplified: SELECT round(julianday(date, 'unixepoch', 'localtime') - julianday('now', 'localtime', 'start of year')) FROM Purchase. The round() should be used to convert the floating point into an int and for the result to be correct. – Prestissimo

Hmmh... good point about the simplification but in that case I think you'd need to cast to int and add one (which I forgot to do); I'll edit the answer. Does that approach work on your target system? – Rife

@Mahozad; PS: the above assumes that your timestamps are stored in UTC. – Rife

Thanks. The workaround you came up with works as expected but I totally changed the way I query data and the problem solved. – Prestissimo