strftim () в sqlite дает неправильные результаты

Я храню поле date в базе данных как количество секунд с эпохи:

введите здесь описание изображения

Для отметки времени на изображении (1550591783 - представляет 2019-02-19 19:26:23) sqlite должен возвращать 50 как день года, но возвращает 40.

Это запрос в PurchaseDao:

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

а это конвертер дат:

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
    }
}

Даже если я передаю now в качестве параметра запроса (я даже применил его к новому методу без каких-либо других отвлекающих факторов), я получаю тот же неправильный результат:

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

Я попытался удалить аргумент 'localtime', изменить преобразователи даты для хранения даты в виде строки (например, в формате 2019-02-19) и запустить приложение в AVD, но во всех случаях получаю один и тот же неправильный результат.

С другой стороны, когда я получаю день года с помощью Календаря (Calendar.getInstance().get(Calendar.DAY_OF_YEAR);) или запускаю запросы на моем ПК с помощью стето, результаты верны.

Любая помощь приветствуется.


person Mahozad    schedule 19.02.2019    source источник
comment
Вы уверены, что оценивается метка времени? Получите ли вы тот же результат, если жестко закодируете метку времени в запросе?   -  person Gabriel Negut    schedule 19.02.2019
comment
@GabrielNegut Да, я получаю тот же результат с жестко запрограммированным 1550591783. И дело в том, что когда я запускаю запрос к базе данных приложения на моем компьютере (со стето), результат правильный.   -  person Mahozad    schedule 19.02.2019


Ответы (2)


как я уже объяснил здесь, преобразование времени UNIX работает примерно так:

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

или при сохранении миллисекунд с начала эпохи, как это обычно бывает для Android Java:

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

это не требует умножения или деления в TypeConverter. в то время как этот операнд умножения / деления 1_000 в TypeConverter кажется мне странным.

проблема здесь может быть точно такой же, как и с чистым SQL ... что date.getTime() / 1000 в 999/1000 случаях может быть представлено только как значение float, а не как прямое целочисленное значение long - если это значение не округляется до целочисленного значения long. ROUND(dateColumn / 1000) предотвратит это с помощью SQL. почему-то этому вопросу не хватает контекста; пожалуйста, прокомментируйте ниже, какое именно значение вам нужно получить - и почему вам нужно его получить; тогда я, возможно, смогу расширить свой ответ - потому что я действительно не понимаю, для чего нужен день года для покупки. Я бы предпочел ожидать, что с момента покупки прошло несколько дней.

person Martin Zeitler    schedule 11.03.2019
comment
Я хочу показать общие расходы в день, например, с 90 дней назад до сегодняшнего дня. Таким образом, кажется, что единственные средства форматирования, которые могут давать уникальные результаты (уникальные номера дней), - это %j и %J. Думаю, вы не читали вопрос. Даже если я запустил запрос с аргументом now, результат будет неправильным: @Query("SELECT strftime('%j', 'now', 'localtime')") В любом случае я попробовал ваше решение и получил другой неверный результат. - person Mahozad; 11.03.2019
comment
@Mahozad, вам лучше использовать для этого агрегатную функцию домена SUM() в сочетании с WHERE dateColumn IN BETWEEN и GROUP BY isodate. в этом прелесть SQL, и это намного проще, чем делать это вручную. noSQL не поддерживает это. - person Martin Zeitler; 11.03.2019
comment
Да, я уже использовал group by day, но не упомянул об этом в своем запросе в вопросе. Проблема по-прежнему день в году! - person Mahozad; 11.03.2019
comment
@Mahozad это не требуется при преобразовании в isodate ... потому что тогда может применяться GROUP BY, что не может применяться, пока это числовые значения (которые представляют секунды или миллисекунды, но не представляют отдельные дни, по которым можно было бы GROUP BY). - person Martin Zeitler; 11.03.2019
comment
Обратите внимание еще раз, что group by и sum работают должным образом. Проблема в том, что он дает неправильные числа дней - person Mahozad; 11.03.2019
comment
@Mahozad, вам просто нужно использовать DATE() вместо DATETIME() ... тогда размер GROUP BY будет соответствовать требованиям; иначе это было бы еще GROUP BY секунды. localtime также может вызвать неожиданное смещение ... так что люди из разных мест получат разные результаты (на что могут намекнуть приведенные выше комментарии). смещение до UTC все еще необходимо учитывать, добавляя или вычитая его из времени начала / окончания. - person Martin Zeitler; 11.03.2019
comment
чтобы объяснить основную проблему, которая приводит к этим неожиданным значениям: живя в Германии (как и я), обычно не нужно учитывать смещение, потому что оно всегда будет в пределах обычных рабочих часов, со смещением только +1:00h (если это не интернет-магазин, который работает круглосуточно). просто возьмите лист бумаги и выполните вычисления ... это смещение в часах можно перевести в секунды с помощью *3600, где время начала и окончания нужно сдвинуть вперед или назад, в зависимости от префикса +/- смещения. - person Martin Zeitler; 11.03.2019

Возможно, это ошибка в реализации strftime дня года; Я получаю правильный результат. Если обновление SQLite или библиотек невозможно, возможно, вы могли бы обойти проблему, вычтя юлианский день первого января года date из юлианского дня date (упрощено и исправлено после комментариев):

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

Это, конечно, предполагает, что функция julianday работает нормально.

person J.R.    schedule 13.03.2019
comment
Ваш запрос можно упростить: SELECT round(julianday(date, 'unixepoch', 'localtime') - julianday('now', 'localtime', 'start of year')) FROM Purchase. round() следует использовать для преобразования числа с плавающей запятой в int и чтобы результат был правильным. - person Mahozad; 13.03.2019
comment
Хммм ... хороший момент по поводу упрощения, но в этом случае я думаю, вам нужно привести к int и добавить один (что я забыл сделать); Отредактирую ответ. Работает ли этот подход на вашей целевой системе? - person J.R.; 14.03.2019
comment
@Mahozad; PS: выше предполагается, что ваши временные метки хранятся в UTC. - person J.R.; 14.03.2019
comment
Спасибо. Обходной путь, который вы придумали, работает должным образом, но я полностью изменил способ запроса данных и проблему решил. - person Mahozad; 14.03.2019