Opublikowano: 19-04-2016



Okazuje się, że sposób przechowywania czasu w bazie SQLite nie jest taki oczywisty. W ogóle przechowywanie danych odbywa się inaczej, niż w innych bazach danych. A czy wiesz, że do kolumny typu integer można wstawić tekst?

Zapis daty i godziny do bazy

SQLite posiada pięć typów danych: NULL, INTEGER, REAL, TEXT oraz BLOB. Nie ma typu odpowiadającego za czas. Jaki więc typ wykorzystać do zapisu znacznika czasu?

INTEGER - można by przechować wartość zwróconą przez time (czyli liczbę sekund od 1970r.), gdyby tylko time zwracało liczbę sekund od 1 stycznia 1970 00:00, a nie zwraca ;) Przynajmniej nie musi, bo przecież:

Although libraries may use a different representation of time: Portable programs should not use the value returned by this function directly, but always rely on calls to other elements of the standard library to translate them to portable types (such as localtime, gmtime or difftime). [źródło]

The encoding of calendar time in std::time_t is unspecified, but most systems conform to POSIX specification and return a value of integral type holding the number of seconds since the Epoch. Implementations in which std::time_t is a 32-bit signed integer (many historical implementations) fail in the year 2038. [źródło]

W związku z powyższym możliwości zapisu daty i czasu w bazie ograniczają się już chyba tylko do zapisu w postaci odpowiednio sformatowanego tekstu, bądź każdej liczby pobranej z np. gmtime (godzina, minuta, sekunda, dzień, miesiąc, rok) w osobnej kolumnie.

Zmienna tekstowa w kolumnie typu Integer

Do sformatowania tekstu SQLite dostarcza odpowiednich funkcji: date, time, datetime, julianday, strftime. Jak ich użyć znajdziesz poniżej w sekcji 'Dobre linki', a teraz do rzeczy.

Wykonywałem drobny test z ich użyciem. Przez przypadek utworzyłem tabelę z kolumną typu INTEGER, a później użyłem wcześniej wspomnianych funkcji. Zadziałało - tylko chwila, one zwracają tekst, a nie wartość liczbową.

Szybki test:

Okazuje się, że SQLite nie przechowuje informacji o typach w metadanych tabeli - chyba tylko sprawdza, czy zapytanie SQL jest prawidłowe i zapisuje informacje o nim w bazie (np. na potrzeby polecenia .schema). Informacje o typie danej wartości są przechowywane w metadanych tej wartości. W dokumentacji jest to określone jako dynamiczny system typowania. Nie działa to dla kolumny z liczbowym kluczem głównym.

In SQLite, the datatype of a value is associated with the value itself, not with its container.. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. [źródło]

Ostatecznie znacznik czasu przechowuję w tabeli jako liczby w osobnych kolumnach. Mam wrażenie, że po utworzeniu indeksów, przeszukiwanie po datach (a właśnie z tego w głównej mierze będę korzystał) będzie szybsze, niż działania na napisach, ale to należałoby przetestować.



Comments powered by Disqus