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.

Are you certain that is the timestamp being evaluated? Do you get the same result if you hardcode the timestamp in the query? — Gabriel Negut, Feb 19 '19 at 17:07
@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. — Mahozad, Feb 19 '19 at 18:14

Martin Zeitler · Answer 1 · 2019-03-11T17:39:33.567

5

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.

edited Mar 11 '19 at 17:39

answered Mar 11 '19 at 16:01

Martin Zeitler

1
19
155
216

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. – Mahozad Mar 11 '19 at 16:54
@Mahozad 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. – Martin Zeitler Mar 11 '19 at 16:59
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! – Mahozad Mar 11 '19 at 17:02
@Mahozad 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`). – Martin Zeitler Mar 11 '19 at 17:03
Please note again that the `group by` and `sum` work as expected. The problem is that it gives results in wrong day numbers – Mahozad Mar 11 '19 at 17:05
@Mahozad 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. – Martin Zeitler Mar 11 '19 at 17:12
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. – Martin Zeitler Mar 11 '19 at 18:06

J.R. · Answer 2 · 2019-03-13T21:25:21.373

3

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.

edited Mar 13 '19 at 21:25

answered Mar 13 '19 at 06:30

J.R.

1,880
8
16

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. – Mahozad Mar 13 '19 at 17:02
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? – J.R. Mar 13 '19 at 21:23
@Mahozad; PS: the above assumes that your timestamps are stored in UTC. – J.R. Mar 14 '19 at 01:31
Thanks. The workaround you came up with works as expected but I totally changed the way I query data and the problem solved. – Mahozad Mar 14 '19 at 10:21

strftim() in sqlite gives wrong results

2 Answers2