Najlepszy sposób pracy z datami w Androidzie SQLite [zamknięty]

Najlepszym sposobem jest zapisanie dat jako liczby, otrzymanej za pomocą polecenia Kalendarz.

//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()); 

Warto wspomnieć, że wartości, które z tego wynikają, powinny być długie, a nie int. liczba całkowita w sqlite może oznaczać wiele rzeczy, wszystko od 1-8 bajtów, ale dla prawie wszystkich dat 64 bity, lub długi, jest to działa.

EDIT: jak wspomniano w komentarzach, musisz użyć cursor.getLong(), aby poprawnie uzyskać znacznik czasu, jeśli to zrobisz.

1. jak zakładano w tym komentarzu, zawsze używałem liczb całkowitych do przechowywania dat.

2. Do przechowywania można użyć metody użytkowej

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

   Tak:

   ContentValues values = new ContentValues();
   values.put(COLUMN_NAME, persistDate(entity.getDate()));
   long id = db.insertOrThrow(TABLE_NAME, null, values);
   

3. Inna metoda użytkowa zajmuje się ładowaniem

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

   Może być użyty w następujący sposób:

   entity.setDate(loadDate(cursor, INDEX));
   

4. Kolejność według daty jest prosta klauzula SQL ORDER (ponieważ mamy kolumnę liczbową). Następująca kolejność będzie malejąca (czyli najnowsza data idzie "pierwszy": {]}

   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
       }
   

Zawsze upewnij się, że przechowujesz UTC/GMT, szczególnie podczas pracy z java.util.Calendar i java.text.SimpleDateFormat, które używają domyślnej strefy czasowej (tj. Twojego urządzenia). java.util.Date.Date() jest bezpieczny w użyciu, ponieważ tworzy wartość UTC.

SQLite może używać typów danych tekstowych, rzeczywistych lub całkowitych do przechowywania dat. Co więcej, za każdym razem, gdy wykonujesz zapytanie, wyniki są wyświetlane za pomocą formatu %Y-%m-%d %H:%M:%S.

Teraz, jeśli wstawisz / zaktualizujesz wartości daty / czasu używając funkcji daty/czasu SQLite, możesz również przechowywać milisekundy. W takim przypadku wyniki są wyświetlane przy użyciu formatu %Y-%m-%d %H:%M:%f. Na przykład:

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

Teraz wykonujemy kilka zapytań, aby sprawdzić, czy rzeczywiście jesteśmy w stanie porównać czasy:

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

Możesz sprawdzić ten sam SELECT za pomocą col2 i col3 i otrzymasz takie same wyniki. Jak widać, drugi wiersz (126 milisekund) nie jest zwracany.

Zauważ, że BETWEEN jest zatem inkluzywna...

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');

... zwróci ten sam zestaw.

Spróbuj bawić się z różnymi zakresami daty/czasu, a wszystko będzie zachowywać się zgodnie z oczekiwaniami.

A co bez strftime funkcji?

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

A co bez strftime funkcji i nie milisekundy?

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

A co z 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

Wreszcie, gdy mamy do czynienia z rzeczywistymi operacjami w programie (bez użycia programu wykonywalnego SQLite...)

BTW: używam JDBC(nie jestem pewien innych języków)... sterownik SQLite-jdbc v3.7. 2 z xerial - być może nowsze wersje zmienią zachowanie opisane poniżej... Jeśli rozwijasz się w systemie Android, nie potrzebujesz sterownika jdbc. Wszystkie operacje SQL można składać za pomocą SQLiteOpenHelper.

JDBC ma różne metody, aby uzyskać rzeczywiste wartości daty/czasu z bazy danych: java.sql.Date, java.sql.Time, i java.sql.Timestamp.

Pokrewnymi metodami w java.sql.ResultSet są (oczywiście) getDate(..), getTime(..), i getTimestamp() odpowiednio.

Na przykład:

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.

Ponieważ SQLite nie ma rzeczywistego typu danych DATE/TIME / TIMESTAMP, wszystkie te 3 metody zwracają wartości tak, jakby obiekty zostały zainicjalizowane przez 0:

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

Więc pytanie brzmi: jak możemy właściwie wybrać, wstawić lub zaktualizować obiekty daty/czasu/znacznika czasu?Nie ma łatwej odpowiedzi. Możesz wypróbować różne kombinacje, ale zmuszą cię do osadzenia funkcji SQLite we wszystkich instrukcjach SQL. O wiele łatwiej jest zdefiniować klasę narzędzi do przekształcania tekstu na daty obiektów wewnątrz programu Java. Ale zawsze pamiętaj, że SQLite przekształca dowolną wartość daty NA UTC + 0000.

Podsumowując, pomimo ogólnej zasady, aby zawsze używać poprawnego typu danych, lub nawet liczb całkowitych oznaczających czas Unix (milisekundy od epoki), uważam o wiele łatwiej przy użyciu domyślnego formatu SQLite ('%Y-%m-%d %H:%M:%f' lub w Javie 'yyyy-MM-dd HH:mm:ss.SSS') zamiast komplikować wszystkie instrukcje SQL z funkcjami SQLite. Pierwsze podejście jest znacznie łatwiejsze do utrzymania.

TODO: sprawdzę wyniki podczas korzystania z getDate / getTime / getTimestamp wewnątrz Androida(API15 lub lepszy)... być może wewnętrzny sterownik różni się od sqlite-jdbc...

Zazwyczaj (tak samo jak w mysql/postgres) przechowuję daty w int(mysql/post) lub text(SQLite), aby zapisać je w formacie znacznika czasu.

Następnie przekonwertuję je na obiekty daty i wykonam działania w oparciu o strefę czasową użytkownika

1-dokładnie tak jak powiedział StErMi.

2 - Proszę przeczytaj to: 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);

Zobacz tutaj:

Query() in sqlitedatabase

4 - Zobacz odpowiedź 3

Wolę to. To nie jest najlepszy sposób, ale szybkie rozwiązanie.

//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);
}

"SELECT  "+_ID+" ,  "+_DESCRIPTION +","+_CREATED_DATE +","+_DATE_TIME+" FROM "+TBL_NOTIFICATION+" ORDER BY "+"strftime(%s,"+_DATE_TIME+") DESC";

Najlepszy sposób przechowywania datew SQLite DB jest do przechowywania bieżącego DateTimeMilliseconds. Poniżej znajduje się fragment kodu do zrobienia so_

1. Pobierz 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. Wstaw dane do swojego DB

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 .

Następnym krokiem jest, gdy chcesz odzyskać dane z DB, musisz przekonwertować odpowiednią datę milisekund czasu na odpowiednią datę. Poniżej znajduje się przykładowy fragment kodu, aby zrobić same_

1. Konwertuj milisekundy daty na łańcuch daty.

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. teraz w końcu Pobierz Dane i zobacz, jak działają...

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;

}

Mam nadzieję, że to pomoże wszystkim! :)