Лучший способ работы с датами в Android SQLite

212

У меня возникли проблемы с работой с датами в приложении для Android, использующем SQLite. У меня есть пара вопросов:

  • Какой тип я должен использовать для хранения дат в SQLite (текст, целое число,...)?
  • Учитывая лучший способ хранения дат, как его правильно сохранить с помощью ContentValues?
  • Какой лучший способ получить дату из базы данных SQLite?
  • Как сделать SQL-запрос на SQLite, заказывая результаты по дате?
  • 2
    Просто используйте класс Calendar и его время члена (которое представляет количество миллисекунд, прошедших с 01.01.1970). Существуют функции-члены для преобразования значения времени в читаемые пользователем строки.
Теги:
database
date

9 ответов

44
Лучший ответ

Вы можете использовать текстовое поле для хранения дат в SQLite.

Сохранение дат в формате UTC, по умолчанию, если вы используете datetime('now') (yyyy-MM-dd HH:mm:ss), тогда разрешите сортировку по столбцу даты.

Получение дат в виде строк из SQLite позволяет затем форматировать или преобразовывать их по мере необходимости в локальные регионизированные форматы с использованием метода Календаря или android.text.format.DateUtils.formatDateTime.

Здесь используется региональный метод форматирования,

public static String formatDateTime(Context context, String timeToFormat) {

    String finalDateTime = "";          

    SimpleDateFormat iso8601Format = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");

    Date date = null;
    if (timeToFormat != null) {
        try {
            date = iso8601Format.parse(timeToFormat);
        } catch (ParseException e) {
            date = null;
        }

        if (date != null) {
            long when = date.getTime();
            int flags = 0;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
            flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
            flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

            finalDateTime = android.text.format.DateUtils.formatDateTime(context,
            when + TimeZone.getDefault().getOffset(when), flags);               
        }
    }
    return finalDateTime;
}
  • 61
    Как бы вы справились с запросами диапазонов дат?
  • 49
    «Рекомендуемая практика»? Не правильно звучит.
Показать ещё 13 комментариев
200

Лучший способ - сохранить даты как число, полученное с помощью команды Calendar.

//Building the table includes:
StringBuilder query=new StringBuilder();
query.append("CREATE TABLE "+TABLE_NAME+ " (");
query.append(COLUMN_ID+"int primary key autoincrement,");
query.append(COLUMN_DATETIME+" int)");

//And inserting the data includes this:
values.put(COLUMN_DATETIME, System.currentTimeMillis()); 

Зачем? Прежде всего, получение значений из диапазона дат легко. Просто конвертируйте свою дату в миллисекунды, а затем запросите ее соответствующим образом. Сортировка по дате также проста. Вызов конвертировать между различными форматами также также легко, как я и включил. Нижняя линия, с помощью этого метода, вы можете делать все, что вам нужно, никаких проблем. Немного сложно читать необработанное значение, но это больше, чем тот недостаток, который легко читается и используется машинами. И на самом деле, относительно легко построить читателя (и я знаю, что там есть), который автоматически преобразует тег времени на сегодняшний день как таковой для удобства чтения.

Стоит отметить, что значения, которые выходят из этого, должны быть длинными, а не int. Целое число в sqlite может означать много вещей, что-то от 1 до 8 байт, но почти для всех дат работает 64 бит или длинный.

EDIT: Как было указано в комментариях, вы должны использовать cursor.getLong(), чтобы правильно получить метку времени, если вы это сделаете.

  • 0
    Я применил ваше решение для сохранения даты, но когда я получил значение из db, это отрицательное число. Так что не может восстановить объект календаря. У вас есть идея с этим? P / S: я определил столбец как BIGINTEGER.
  • 1
    Дата никогда не должна отображаться как отрицательное число ... Я предполагаю, что где-то происходит неправильная типизация, попробуйте убедиться, что то, что входит в базу данных, это то, что вы получаете из нее. Если вы действительно не можете найти решение, попробуйте задать новый вопрос здесь.
Показать ещё 8 комментариев
29
  • Как предполагается в этом комментарии, я всегда буду использовать целые числа для хранения дат.
  • Для хранения можно использовать служебный метод

    public static Long persistDate(Date date) {
        if (date != null) {
            return date.getTime();
        }
        return null;
    }
    

    так:

    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME, persistDate(entity.getDate()));
    long id = db.insertOrThrow(TABLE_NAME, null, values);
    
  • Другой метод утилиты заботится о загрузке

    public static Date loadDate(Cursor cursor, int index) {
        if (cursor.isNull(index)) {
            return null;
        }
        return new Date(cursor.getLong(index));
    }
    

    можно использовать следующим образом:

    entity.setDate(loadDate(cursor, INDEX));
    
  • Заказ по дате простой SQL предложение ORDER (потому что у нас есть числовой столбец). Ниже будет указано, что происходит спуск (это самая новая дата):

    public static final String QUERY = "SELECT table._id, table.dateCol FROM table ORDER BY table.dateCol DESC";
    
    //...
    
        Cursor cursor = rawQuery(QUERY, null);
        cursor.moveToFirst();
    
        while (!cursor.isAfterLast()) {
            // Process results
        }
    

Обязательно сохраняйте время UTC/GMT, особенно при работе с java.util.Calendar и java.text.SimpleDateFormat, которые используют часовой пояс по умолчанию (т.е. ваш аппарат). java.util.Date.Date() безопасен в использовании, так как он создает значение UTC.

  • 0
    Мне нравится это решение! Спасибо! Я работаю с чем-то, что нуждается только в точности до сегодняшнего дня. Я хочу иметь возможность запрашивать строки в базе данных, которые имеют даты в определенном диапазоне от текущей даты. Например, я получаю текущее время из календаря и ищу все строки с датой в течение одного месяца. Но что, если сейчас 2:30, а месяц назад я сохранил дату в 2:29? Он будет остановлен, потому что у него меньше секунд, чем месяц назад. Есть ли способ вернуться на месяц назад, но на следующий день?
  • 0
    Было бы плохим решением узнать текущее время, вызвав calendar.set (Calendar.HOUR_OF_DAY, 0); calendar.set (Calendar.MINUTE, 0); на все даты перед сохранением и перед запросом?
Показать ещё 1 комментарий
7

SQLite может использовать текстовые, реальные или целые типы данных для хранения дат. Более того, всякий раз, когда вы выполняете запрос, результаты отображаются с использованием формата %Y-%m-%d %H:%M:%S.

Теперь, если вы вставляете/обновляете значения даты и времени с помощью функций даты и времени SQLite, вы также можете хранить миллисекунды. В этом случае результаты отображаются с использованием формата %Y-%m-%d %H:%M:%f. Например:

sqlite> create table test_table(col1 text, col2 real, col3 integer);
sqlite> insert into test_table values (
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123')
        );
sqlite> insert into test_table values (
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
            strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126')
        );
sqlite> select * from test_table;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

Теперь, выполняя некоторые запросы, чтобы проверить, действительно ли мы можем сравнивать время:

sqlite> select * from test_table /* using col1 */
           where col1 between 
               strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.121') and
               strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123

Вы можете проверить тот же SELECT с помощью col2 и col3, и вы получите те же результаты. Как вы можете видеть, вторая строка (126 миллисекунд) не возвращается.

Обратите внимание, что BETWEEN является включительным, поэтому...

sqlite> select * from test_table 
            where col1 between 
                 /* Note that we are using 123 milliseconds down _here_ */
                strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123') and
                strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');

... вернет тот же набор.

Попробуйте поиграть с разными диапазонами дат/времени, и все будет вести себя так, как ожидалось.

Как насчет без strftime функции?

sqlite> select * from test_table /* using col1 */
           where col1 between 
               '2014-03-01 13:01:01.121' and
               '2014-03-01 13:01:01.125';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123

Как насчет без strftime функции и миллисекунды?

sqlite> select * from test_table /* using col1 */
           where col1 between 
               '2014-03-01 13:01:01' and
               '2014-03-01 13:01:02';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

Как насчет ORDER BY?

sqlite> select * from test_table order by 1 desc;
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
sqlite> select * from test_table order by 1 asc;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126

Хорошо работает.

Наконец, при работе с фактическими операциями внутри программы (без использования исполняемого файла sqlite...)

BTW: Я использую JDBC (не уверен в других языках)... драйвер sqlite-jdbc v3.7.2 от xerial - возможно, новые версии измените поведение, описанное ниже... Если вы разрабатываете Android, вам не нужен драйвер jdbc. Все операции SQL могут быть представлены с помощью SQLiteOpenHelper.

JDBC имеет разные методы для получения фактических значений даты/времени из базы данных: java.sql.Date, java.sql.Time и java.sql.Timestamp.

Связанные методы в java.sql.ResultSet (очевидно) getDate(..), getTime(..) и getTimestamp() соответственно.

Например:

Statement stmt = ... // Get statement from connection
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_TABLE");
while (rs.next()) {
    System.out.println("COL1 : "+rs.getDate("COL1"));
    System.out.println("COL1 : "+rs.getTime("COL1"));
    System.out.println("COL1 : "+rs.getTimestamp("COL1"));
    System.out.println("COL2 : "+rs.getDate("COL2"));
    System.out.println("COL2 : "+rs.getTime("COL2"));
    System.out.println("COL2 : "+rs.getTimestamp("COL2"));
    System.out.println("COL3 : "+rs.getDate("COL3"));
    System.out.println("COL3 : "+rs.getTime("COL3"));
    System.out.println("COL3 : "+rs.getTimestamp("COL3"));
}
// close rs and stmt.

Так как SQLite не имеет фактического типа данных DATE/TIME/TIMESTAMP, все эти 3 метода возвращают значения, как если бы объекты были инициализированы с помощью 0:

new java.sql.Date(0)
new java.sql.Time(0)
new java.sql.Timestamp(0)

Итак, вопрос в том, как мы можем на самом деле выбирать, вставлять или обновлять объекты Date/Time/Timestamp? Нет простого ответа. Вы можете попробовать разные комбинации, но они заставят вас встроить функции SQLite во все SQL-запросы. Гораздо проще определить класс утилиты для преобразования текста в объекты Date внутри вашей Java-программы. Но всегда помните, что SQLite преобразует любое значение даты в UTC + 0000.

В целом, несмотря на общее правило всегда использовать правильный тип данных или даже целые числа, обозначающие время Unix (миллисекунды с эпохи), я нахожу намного проще с использованием стандартного SQLite-формата ('%Y-%m-%d %H:%M:%f' или в Java 'yyyy-MM-dd HH:mm:ss.SSS'), чтобы усложнить все ваши SQL-запросы функциями SQLite. Первый подход намного проще поддерживать.

TODO: Я проверю результаты при использовании getDate/getTime/getTimestamp внутри Android (API15 или лучше)... возможно, внутренний драйвер отличается от sqlite-jdbc...

  • 0
    Учитывая механизм внутреннего хранения SQLite, я не уверен, что ваши примеры имеют эффект, который вы подразумеваете: похоже, что механизм «позволяет хранить любые типизированные для хранения значения в любом столбце независимо от объявленного типа SQL» ( books.google. де /… ). Мне кажется, что в вашем примере Real против Integer против Text происходит следующее: SQLite просто сохраняет текст в виде текста во всех столбцах дерева. Так что, естественно, все результаты хорошие, память все равно потрачена впустую. Если использовалось только целое число, вы должны потерять миллисекунды. Просто говорю...
  • 0
    Фактически, вы можете подтвердить то, что я только что сказал, выполнив SELECT datetime (col3, 'unixepoch') FROM test_table. Это покажет пустые строки для ваших примеров ... если только для теста вы не вставите фактическое целое число. Например, если вы добавите строку со значением col3 37, приведенная выше инструкция SELECT отобразит: 1970-01-01 00:00:37. Так что, если вы на самом деле не можете хранить все даты довольно неэффективно в виде текстовой строки, не делайте так, как вы предлагаете.
Показать ещё 1 комментарий
3

Обычно (так же, как в mysql/postgres) я храню даты в int (mysql/post) или text (sqlite), чтобы сохранить их в формате timestamp.

Затем я преобразую их в объекты Date и выполняю действия на основе пользователя TimeZone

1

Я предпочитаю это. Это не лучший способ, но быстрое решение.

//Building the table includes:
StringBuilder query= new StringBuilder();
query.append("CREATE TABLE "+TABLE_NAME+ " (");
query.append(COLUMN_ID+"int primary key autoincrement,");
query.append(COLUMN_CREATION_DATE+" DATE)");

//Inserting the data includes this:
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
values.put(COLUMN_CREATION_DATE,dateFormat.format(reactionGame.getCreationDate())); 

// Fetching the data includes this:
try {
   java.util.Date creationDate = dateFormat.parse(cursor.getString(0);
   YourObject.setCreationDate(creationDate));
} catch (Exception e) {
   YourObject.setCreationDate(null);
}
1

1 - Точно так же, как сказано в StErMi.

2 - Пожалуйста, прочтите следующее: http://www.vogella.de/articles/AndroidSQLite/article.html

3 -

Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "title", "title_raw", "timestamp"}, 
                "//** YOUR REQUEST**//", null, null, "timestamp", null);

см. здесь:

Запрос() в SQLiteDatabase

4 - см. ответ 3

0

Лучший способ сохранить date в SQlite DB - сохранить текущие DateTimeMilliseconds. Ниже приведен фрагмент кода, чтобы сделать это.

  1. Получить DateTimeMilliseconds
public static long getTimeMillis(String dateString, String dateFormat) throws ParseException {
    /*Use date format as according to your need! Ex. - yyyy/MM/dd HH:mm:ss */
    String myDate = dateString;//"2017/12/20 18:10:45";
    SimpleDateFormat sdf = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);
    Date date = sdf.parse(myDate);
    long millis = date.getTime();

    return millis;
}
  1. Вставьте данные в свою БД
public void insert(Context mContext, long dateTimeMillis, String msg) {
    //Your DB Helper
    MyDatabaseHelper dbHelper = new MyDatabaseHelper(mContext);
    database = dbHelper.getWritableDatabase();

    ContentValues contentValue = new ContentValues();
    contentValue.put(MyDatabaseHelper.DATE_MILLIS, dateTimeMillis);
    contentValue.put(MyDatabaseHelper.MESSAGE, msg);

    //insert data in DB
    database.insert("your_table_name", null, contentValue);

   //Close the DB connection.
   dbHelper.close(); 

}

Now, your data (date is in currentTimeMilliseconds) is get inserted in DB.

Следующим шагом, когда вы хотите получить данные из БД, необходимо преобразовать соответствующие миллисекунды времени в соответствующую дату. Ниже приведен пример фрагмента кода, чтобы сделать то же самое

  1. Преобразование даты в миллисекундах в строку даты.
public static String getDate(long milliSeconds, String dateFormat)
{
    // Create a DateFormatter object for displaying date in specified format.
    SimpleDateFormat formatter = new SimpleDateFormat(dateFormat/*"yyyy/MM/dd HH:mm:ss"*/);

    // Create a calendar object that will convert the date and time value in milliseconds to date.
    Calendar calendar = Calendar.getInstance();
    calendar.setTimeInMillis(milliSeconds);
    return formatter.format(calendar.getTime());
}
  1. Теперь, наконец, получить данные и увидеть его работу...
public ArrayList<String> fetchData() {

    ArrayList<String> listOfAllDates = new ArrayList<String>();
    String cDate = null;

    MyDatabaseHelper dbHelper = new MyDatabaseHelper("your_app_context");
    database = dbHelper.getWritableDatabase();

    String[] columns = new String[] {MyDatabaseHelper.DATE_MILLIS, MyDatabaseHelper.MESSAGE};
    Cursor cursor = database.query("your_table_name", columns, null, null, null, null, null);

    if (cursor != null) {

        if (cursor.moveToFirst()){
            do{
                //iterate the cursor to get data.
                cDate = getDate(cursor.getLong(cursor.getColumnIndex(MyDatabaseHelper.DATE_MILLIS)), "yyyy/MM/dd HH:mm:ss");

                listOfAllDates.add(cDate);

            }while(cursor.moveToNext());
        }
        cursor.close();

    //Close the DB connection.
    dbHelper.close(); 

    return listOfAllDates;

}

Надеюсь, это поможет всем! :)

  • 0
    SQLite не поддерживает длинный тип данных. РЕДАКТИРОВАТЬ: Моя ошибка, INTEGER имеет длину 8 байт, поэтому он должен поддерживать этот тип данных.
0
"SELECT  "+_ID+" ,  "+_DESCRIPTION +","+_CREATED_DATE +","+_DATE_TIME+" FROM "+TBL_NOTIFICATION+" ORDER BY "+"strftime(%s,"+_DATE_TIME+") DESC";

Ещё вопросы

Сообщество Overcoder
Наверх
Меню