TIMESTAMP: PAIEŠKOS ATRIBUTAS SU POVANDENINIAIS AKMENIMIS

Noriu pasidalinti savo patirtimi su tokiu, atrodytų, primityviu daiktu kaip timestamp. Teorija visiems turbūt puikiai pažįstama – tai sekundžių skaičius nuo 1970 metų sausio pirmos dienos 00:00:00. Puikus kandidatas paieškai duomenų bazėse, deja ne viskas taip paprasta.

Nematoma reikšmė

Bet pradėkime nuo pradžių. Naudojame Postgre DB ir kreipiamės į ją iš Java programos. Mūsų projekte yra lentelė su timestamp tipo lauku – calldatetime, sistema viduje įrašus saugo GMT laiku, ir generuojant raportus konvertuoja įrašų laiką pagal vietos laiko zoną. Gana dažnai naudojamas modelis.

Gavau kartą užklausimą, su prašymu patikslinti kodėl vieno įrašo duomenys yra būtent tokie ir to įrašo timestamp’ą:1438157014. Viskas atrodytų paprasta – iš IDE prisijungiau prie DB, suvedžiau užklausą:

‘SELECT * FROM that_table WHERE calldatetime=to_timestamp(1438157014)’
0 row(s) found.

Prisijungiau prie serverio per SSH, psql pakartoju tą patį sql sakinį – irgi nieko nerasta. O va per programos admin GUI pagal nurodytą timestamp‘ą – sėkmingai randa.

Praleidžiant tolimesnius problemos paieškos veiksmus reziumė:
Jei duomenų bazėje timestamp laukas yra nurodytas be laiko zonos (laukas timestamp without time zone), tai per JDBC draiverį darant užklausas, serveris atlieka transformacijas panaudodamas kliento Java Virtual Machine (JVM) laiko zoną. Todėl taip ir išėjo, kad iš mano kompiuterio (kur timezone +3) ir iš serverio konsolės (timezone +2) nieko nebuvo surasta, o va iš programos JVM (kur įjungta timezone +0) – sėkmingai atrasta.

Atlikau testuką. Iš savo lokalaus kompiuterio prisijungiau prie DB ir:

CREATE TABLE learning( ts_su timestamp with time zone, ts_be timestamp without time zone, tm_su time with time zone, tm_be time without time zone);
INSERT INTO learning (ts_su, ts_be, tm_su, tm_be ) VALUES (now(), now(), now(), now());

Ir tada paleidau tokią užklausą – ‘SELECT ts_su::varchar, ts_be::varchar, tm_su::varchar, tm_be::varchar FROM learning ‘ – iš lokalios mašinos, iš serverio ir iš java app, kur įjungta UTC timezone.

Rezultatai (paryškinau skirtumus):

Taip buvo su 1.6, su 1.7 ir su 1.8 Java distribucijomis. Senesnių jau nebebandžiau, bet spėju būtų lygiai tas pats.

Rekomendacijos

Surinkau tokius pasiūlymus kaip išvengti šios povandeninės duobės dirbant su JDBC driveriais:

• Pats geriausias ir užtikrinčiausias būdas – lentelėse visi timestamp laukai turi būti su laiko zona (laukas timestamp with time zone). Tokiu atveju visada bus identiška reikšmė nepriklausomai nuo kliento JVM laiko zonos.
• Nurodyti pageidaujamą laiko zoną per JDBC URL, pvz. jdbc:dbtype://localhost/dbname?tz=utc
• Šio sprendimo minusas tai kad ne visos duomenų bazės palaiko tokį mechanizmą, ir gali iškilti problemų, jei norėsite vieną duomenų bazę pakeisti kita.
• PreparedStatement’ui nustatyti priverstinę zoną su setDate(int, Date) metodu.
• Minusai: hibernate’ui, JPA tai ignoruoja, o dar jei tai yra legacy kodas su paprastais Statement, tai laukia didelis refactorinimas.
• Naudoti modifikuotą prisijungimo draiverį (biblioteką), kuri priverstinai uždeda fiksuotą laiko juostą.
• Šio sprendimo minusas, kad reikia nepamiršti modifikuoti naują biblioteką (kai bus atnaujinama versija). Taip pat jei bus keičiama viena DB į kitą.
• Java kode priverstinai uždėti pageidaujamą laiko zoną: TimeZone.setDefault(TimeZone.getTimeZone(‘UTC’));
• Vėlgi nepatogu, nes tai darys įtaką ne tik DB prisijungimams, bet ir visoms kitoms datetime operacijoms. Nors tam tikrais atvejais tai gali būti ir pageidaujamas efektas.