Root.cz  »  Blogy  »  Pavel Tavoda  »  Pouzitie LocalDateTime a ZonedDateTime v Hibernate + PostgreSQL

Pouzitie LocalDateTime a ZonedDateTime v Hibernate + PostgreSQL

18. 8. 2020 12:43 (aktualizováno) Pavel Tavoda

Kedze som potreboval zistit ako funguje v novej verzii Hibernate ukladanie LocalDateTime a aky vplyv maju jednotlive nastavanie v pripade ze potrebujete bezat dislokovany cluster v roznych casovych zonach proti spolocnej DB urobil som nasledovny test. Urobil som si na to malicku RESTovu aplikaciu (majte zlutovanie s nazvami a mapovanim parametrov, povodne to mal byt iba maly test a teraz to koli vam prepisovat nebudem :-P).

Zdrojove kody

Takze Entita (vygenerovane nastrojom Sculptor http://sculptorge­nerator.org podobne ako zvysny boilerplate kod), ked uz som test robil tak som ho urobil aj s triedou ZonedDateTime, v DB su pouzite datove typy timestamp a timestamptz:
@Entity
@Table(name = "TESTTIME")
public class TestTime extends AbstractDomainObject implements Identifiable {

   private static final long serialVersionUID = 1L;

   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "ID")
   private Long id;

   @Column(name = "LOCALTIMENOW", nullable = false)
   @Type(type = "org.hibernate.type.LocalDateTimeType")
   @NotNull
   private LocalDateTime localTimeNow;

   @Column(name = "LOCALTIMEUTC", nullable = false)
   @Type(type = "org.hibernate.type.LocalDateTimeType")
   @NotNull
   private LocalDateTime localTimeUtc;

   @Column(name = "ZONEDTIMENOWDEF", nullable = false)
   @Type(type = "org.hibernate.type.ZonedDateTimeType")
   @NotNull
   private ZonedDateTime zonedTimeNowDef;

   @Column(name = "ZONEDTIMEUTCDEF", nullable = false)
   @Type(type = "org.hibernate.type.ZonedDateTimeType")
   @NotNull
   private ZonedDateTime zonedTimeUtcDef;

   @Column(name = "ZONEDTIMENOWUTC", nullable = false)
   @Type(type = "org.hibernate.type.ZonedDateTimeType")
   @NotNull
   private ZonedDateTime zonedTimeNowUtc;

   @Column(name = "ZONEDTIMEUTCUTC", nullable = false)
   @Type(type = "org.hibernate.type.ZonedDateTimeType")
   @NotNull
   private ZonedDateTime zonedTimeUtcUtc;

   @Column(name = "DEFAULTTZ", nullable = false, length = 100)
   @NotNull
   private String defaultTz;

   @Column(name = "STRINGLOCALTIME", nullable = false, length = 100)
   @NotNull
   private String stringLocalTime;

   ... Contstructor + getters + setters vyhodene koli prehladnosti ...
}

A REST client, ostatny boilerplate nie je nijako zaujimavy, metoda save(TestTime testTime) a findById(Long id):

@RequestMapping(value = "/test", method = RequestMethod.GET)
public String testTimestamp(HttpServletRequest request) {
   String tz = request.getParameter("tz");
   if (tz == null) {
      return "ERROR";
   }
   TimeZone.setDefault(TimeZone.getTimeZone(tz == null ? "UTC" : tz));

   TestTime tt = new TestTime();
   tt.setStringLocalTime("" + new Date());
   tt.setDefaultTz(tz);

   LocalDateTime now = LocalDateTime.now();
   LocalDateTime utc = LocalDateTime.now(ZoneOffset.UTC);
   ZoneId zoneUtc = ZoneId.of("UTC");
   tt.setLocalTimeNow(now);
   tt.setLocalTimeUtc(utc);
   tt.setZonedTimeNowDef(now.atZone(ZoneId.systemDefault()));
   tt.setZonedTimeUtcDef(utc.atZone(ZoneId.systemDefault()));
   tt.setZonedTimeNowUtc(now.atZone(zoneUtc));
   tt.setZonedTimeUtcUtc(utc.atZone(zoneUtc));
   tt = testTimeService.save(tt);
   return "javaDefaultTz = " + TimeZone.getDefault().getDisplayName()
         + " :: ID = " + tt.getId()
         + " :: createdTz = " + tt.getDefaultTz()
         + " :: createdStringTime = " + tt.getStringLocalTime()
         + " :: localTimeNow = " + tt.getLocalTimeNow()
         + " :: localTimeUtc = " + tt.getLocalTimeUtc()
         + " :: zonedTimeNowDef = " + tt.getZonedTimeNowDef()
         + " :: zonedTimeUtcDef = " + tt.getZonedTimeUtcDef()
         + " :: zonedTimeNowUtc = " + tt.getZonedTimeNowUtc()
         + " :: zonedTimeUtcUtc = " + tt.getZonedTimeUtcUtc()
   ;
}

@RequestMapping(value = "/testGet", method = RequestMethod.GET)
public String testTimestampGet(HttpServletRequest request) throws TestTimeNotFoundException {
   String tz = request.getParameter("tz");
   if (tz == null) {
      return "ERROR";
   }
   TimeZone.setDefault(TimeZone.getTimeZone(tz == null ? "UTC" : tz));

   String id = request.getParameter("id");
   TestTime tt = testTimeService.findById(Long.parseLong(id));
   return "javaDefaultTz = " + TimeZone.getDefault().getDisplayName()
         + " :: ID = " + tt.getId()
         + " :: createdTz = " + tt.getDefaultTz()
         + " :: createdStringTime = " + tt.getStringLocalTime()
         + " :: localTimeNow = " + tt.getLocalTimeNow()
         + " :: localTimeUtc = " + tt.getLocalTimeUtc()
         + " :: zonedTimeNowDef = " + tt.getZonedTimeNowDef()
         + " :: zonedTimeUtcDef = " + tt.getZonedTimeUtcDef()
         + " :: zonedTimeNowUtc = " + tt.getZonedTimeNowUtc()
         + " :: zonedTimeUtcUtc = " + tt.getZonedTimeUtcUtc()
   ;
}

DDL script:

CREATE TABLE TESTTIME (
   ID BIGINT NOT NULL,
   LOCALTIMENOW TIMESTAMP NOT NULL,
   LOCALTIMEUTC TIMESTAMP NOT NULL,
   ZONEDTIMENOWDEF TIMESTAMPTZ NOT NULL,
   ZONEDTIMEUTCDEF TIMESTAMPTZ NOT NULL,
   ZONEDTIMENOWUTC TIMESTAMPTZ NOT NULL,
   ZONEDTIMEUTCUTC TIMESTAMPTZ NOT NULL,
   DEFAULTTZ VARCHAR(100) NOT NULL,
   STRINGLOCALTIME VARCHAR(100) NOT NULL
);

Metodika

Teraz samotny test, vytvoril som jeden objekt v casovej zone Europe/Bratislava (GMT+2) a druhy objekt v zone America/Dominica (GMT-4). Potom som obidva zaznamy precital v obidvoch casovych zonach a naviac este aj v casovej zone Asia/Dubai (GMT+4). Cely tento test som spustil 2×, raz bez nastavenia premennej hibernate.jdbc.time_zone a druhy krat s hibernate.jdbc.time_zone=UTC. Databaza bezala v casovej zone Europe/Bratislava (psql → show timezone).
Pouzity Hibernate 5.4.17.Final a Java 11.0.6.
Prehlad casovych zon
America/Dominica GMT-4  Atlantic Standard Time
Europe/Bratislava GMT+2  Central European Standard Time
Asia/Dubai GMT+4  Gulf Standard Time

Prvy test (bez hibernate.jdbc.time_zone)

Cas vlozenia prveho objektu v jednotlivych zonach
America/Dominica 01:46
UTC 05:46
Europe/Bratislava 07:46
Asia/Dubai 09:46

Vytvorenie

Europe/Bratislava GMT+2
javaDefaultTz = Central European Standard Time
ID = 436
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 07:46:01 CEST 2020
localTimeNow = 2020-08-17T07:46:01.943556
localTimeUtc = 2020-08-17T05:46:01.943571
zonedTimeNowDef = 2020-08-17T07:46:01.943556+02:00[Europe/Bratislava]
zonedTimeUtcDef = 2020-08-17T05:46:01.943571+02:00[Europe/Bratislava]
zonedTimeNowUtc = 2020-08-17T07:46:01.943556Z[UTC]
zonedTimeUtcUtc = 2020-08-17T05:46:01.943571Z[UTC]
America/Dominica GMT-4
javaDefaultTz = Atlantic Standard Time
ID = 437
createdTz = America/Dominica
createdStringTime = Mon Aug 17 01:46:22 AST 2020
localTimeNow = 2020-08-17T01:46:22.912326
localTimeUtc = 2020-08-17T05:46:22.912337
zonedTimeNowDef = 2020-08-17T01:46:22.912326-04:00[America/Dominica]
zonedTimeUtcDef = 2020-08-17T05:46:22.912337-04:00[America/Dominica]
zonedTimeNowUtc = 2020-08-17T01:46:22.912326Z[UTC]
zonedTimeUtcUtc = 2020-08-17T05:46:22.912337Z[UTC]

Obsah DB

id localTimeNow localTimeUtc zonedTimeNowDef zonedTimeUtcDef zonedTimeNowUtc zonedTimeUtcUtc defaultTz stringLocalTime
 436  2020–08–17 07:46:01.943556  2020–08–17 05:46:01.943571  2020–08–17 07:46:01.943556+02  2020–08–17 05:46:01.943571+02  2020–08–17 09:46:01.943556+02  2020–08–17 07:46:01.943571+02  Europe/Bratislava  Mon Aug 17 07:46:01 CEST 2020
 437  2020–08–17 01:46:22.912326  2020–08–17 05:46:22.912337  2020–08–17 07:46:22.912326+02  2020–08–17 11:46:22.912337+02 2020–08–17 03:46:22.912326+02   2020–08–17 07:46:22.912337+02  America/Dominica  Mon Aug 17 01:46:22 AST 2020

Citanie

America/Dominica GMT-4
javaDefaultTz = Atlantic Standard Time
ID = 436
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 07:46:01 CEST 2020
localTimeNow = 2020-08-17T07:46:01.943556
localTimeUtc = 2020-08-17T05:46:01.943571
zonedTimeNowDef = 2020-08-17T01:46:01.943556-04:00[America/Dominica]
zonedTimeUtcDef = 2020-08-16T23:46:01.943571-04:00[America/Dominica]
zonedTimeNowUtc = 2020-08-17T03:46:01.943556-04:00[America/Dominica]
zonedTimeUtcUtc = 2020-08-17T01:46:01.943571-04:00[America/Dominica]

javaDefaultTz = Atlantic Standard Time
ID = 437
createdTz = America/Dominica
createdStringTime = Mon Aug 17 01:46:22 AST 2020
localTimeNow = 2020-08-17T01:46:22.912326
localTimeUtc = 2020-08-17T05:46:22.912337
zonedTimeNowDef = 2020-08-17T01:46:22.912326-04:00[America/Dominica]
zonedTimeUtcDef = 2020-08-17T05:46:22.912337-04:00[America/Dominica]
zonedTimeNowUtc = 2020-08-16T21:46:22.912326-04:00[America/Dominica]
zonedTimeUtcUtc = 2020-08-17T01:46:22.912337-04:00[America/Dominica]
Europe/Bratislava GMT+2
javaDefaultTz = Central European Standard Time
ID = 436
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 07:46:01 CEST 2020
localTimeNow = 2020-08-17T07:46:01.943556
localTimeUtc = 2020-08-17T05:46:01.943571
zonedTimeNowDef = 2020-08-17T07:46:01.943556+02:00[Europe/Bratislava]
zonedTimeUtcDef = 2020-08-17T05:46:01.943571+02:00[Europe/Bratislava]
zonedTimeNowUtc = 2020-08-17T09:46:01.943556+02:00[Europe/Bratislava]
zonedTimeUtcUtc = 2020-08-17T07:46:01.943571+02:00[Europe/Bratislava]

javaDefaultTz = Central European Standard Time
ID = 437
createdTz = America/Dominica
createdStringTime = Mon Aug 17 01:46:22 AST 2020
localTimeNow = 2020-08-17T01:46:22.912326
localTimeUtc = 2020-08-17T05:46:22.912337
zonedTimeNowDef = 2020-08-17T07:46:22.912326+02:00[Europe/Bratislava]
zonedTimeUtcDef = 2020-08-17T11:46:22.912337+02:00[Europe/Bratislava]
zonedTimeNowUtc = 2020-08-17T03:46:22.912326+02:00[Europe/Bratislava]
zonedTimeUtcUtc = 2020-08-17T07:46:22.912337+02:00[Europe/Bratislava]
Asia/Dubai GMT+4
javaDefaultTz = Gulf Standard Time
ID = 436
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 07:46:01 CEST 2020
localTimeNow = 2020-08-17T07:46:01.943556
localTimeUtc = 2020-08-17T05:46:01.943571
zonedTimeNowDef = 2020-08-17T09:46:01.943556+04:00[Asia/Dubai]
zonedTimeUtcDef = 2020-08-17T07:46:01.943571+04:00[Asia/Dubai]
zonedTimeNowUtc = 2020-08-17T11:46:01.943556+04:00[Asia/Dubai]
zonedTimeUtcUtc = 2020-08-17T09:46:01.943571+04:00[Asia/Dubai]

javaDefaultTz = Gulf Standard Time
ID = 437
createdTz = America/Dominica
createdStringTime = Mon Aug 17 01:46:22 AST 2020
localTimeNow = 2020-08-17T01:46:22.912326
localTimeUtc = 2020-08-17T05:46:22.912337
zonedTimeNowDef = 2020-08-17T09:46:22.912326+04:00[Asia/Dubai]
zonedTimeUtcDef = 2020-08-17T13:46:22.912337+04:00[Asia/Dubai]
zonedTimeNowUtc = 2020-08-17T05:46:22.912326+04:00[Asia/Dubai]
zonedTimeUtcUtc = 2020-08-17T09:46:22.912337+04:00[Asia/Dubai]

Druhy test (hibernate.jdbc.time_zone=UTC)

Priblizny cas vlozenia prveho objektu v jednotlivych zonach
America/Dominica 02:02
UTC 06:02
Europe/Bratislava 08:02
Asia/Dubai 10:02

Vytvorenie

Europe/Bratislava GMT+2
javaDefaultTz = Central European Standard Time
ID = 439
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 08:02:03 CEST 2020
localTimeNow = 2020-08-17T08:02:03.888033
localTimeUtc = 2020-08-17T06:02:03.888100
zonedTimeNowDef = 2020-08-17T08:02:03.888033+02:00[Europe/Bratislava]
zonedTimeUtcDef = 2020-08-17T06:02:03.888100+02:00[Europe/Bratislava]
zonedTimeNowUtc = 2020-08-17T08:02:03.888033Z[UTC]
zonedTimeUtcUtc = 2020-08-17T06:02:03.888100Z[UTC]
America/Dominica GMT-4
javaDefaultTz = Atlantic Standard Time
ID = 440
createdTz = America/Dominica
createdStringTime = Mon Aug 17 02:02:28 AST 2020
localTimeNow = 2020-08-17T02:02:28.691584
localTimeUtc = 2020-08-17T06:02:28.691599
zonedTimeNowDef = 2020-08-17T02:02:28.691584-04:00[America/Dominica]
zonedTimeUtcDef = 2020-08-17T06:02:28.691599-04:00[America/Dominica]
zonedTimeNowUtc = 2020-08-17T02:02:28.691584Z[UTC]
zonedTimeUtcUtc = 2020-08-17T06:02:28.691599Z[UTC]

Obsah DB

id localTimeNow localTimeUtc zonedTimeNowDef zonedTimeUtcDef zonedTimeNowUtc zonedTimeUtcUtc defaultTz stringLocalTime
439 2020–08–17 06:02:03.888033 2020–08–17 04:02:03.8881 2020–08–17 08:02:03.888033+02 2020–08–17 06:02:03.8881+02 2020–08–17 10:02:03.888033+02 2020–08–17 08:02:03.8881+02 Europe/Bratislava Mon Aug 17 08:02:03 CEST 2020
440 2020–08–17 06:02:28.691584 2020–08–17 10:02:28.691599 2020–08–17 08:02:28.691584+02 2020–08–17 12:02:28.691599+02 2020–08–17 04:02:28.691584+02 2020–08–17 08:02:28.691599+02 America/Dominica Mon Aug 17 02:02:28 AST 2020

Citanie

America/Dominica GMT-4
javaDefaultTz = Atlantic Standard Time
ID = 439
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 08:02:03 CEST 2020
localTimeNow = 2020-08-17T02:02:03.888033
localTimeUtc = 2020-08-17T00:02:03.888100
zonedTimeNowDef = 2020-08-17T02:02:03.888033-04:00[America/Dominica]
zonedTimeUtcDef = 2020-08-17T00:02:03.888100-04:00[America/Dominica]
zonedTimeNowUtc = 2020-08-17T04:02:03.888033-04:00[America/Dominica]
zonedTimeUtcUtc = 2020-08-17T02:02:03.888100-04:00[America/Dominica]

javaDefaultTz = Atlantic Standard Time
ID = 440
createdTz = America/Dominica
createdStringTime = Mon Aug 17 02:02:28 AST 2020
localTimeNow = 2020-08-17T02:02:28.691584
localTimeUtc = 2020-08-17T06:02:28.691599
zonedTimeNowDef = 2020-08-17T02:02:28.691584-04:00[America/Dominica]
zonedTimeUtcDef = 2020-08-17T06:02:28.691599-04:00[America/Dominica]
zonedTimeNowUtc = 2020-08-16T22:02:28.691584-04:00[America/Dominica]
zonedTimeUtcUtc = 2020-08-17T02:02:28.691599-04:00[America/Dominica]
Europe/Bratislava GMT+2
javaDefaultTz = Central European Standard Time
ID = 439
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 08:02:03 CEST 2020
localTimeNow = 2020-08-17T08:02:03.888033
localTimeUtc = 2020-08-17T06:02:03.888100
zonedTimeNowDef = 2020-08-17T08:02:03.888033+02:00[Europe/Bratislava]
zonedTimeUtcDef = 2020-08-17T06:02:03.888100+02:00[Europe/Bratislava]
zonedTimeNowUtc = 2020-08-17T10:02:03.888033+02:00[Europe/Bratislava]
zonedTimeUtcUtc = 2020-08-17T08:02:03.888100+02:00[Europe/Bratislava]

javaDefaultTz = Central European Standard Time
ID = 440
createdTz = America/Dominica
createdStringTime = Mon Aug 17 02:02:28 AST 2020
localTimeNow = 2020-08-17T08:02:28.691584
localTimeUtc = 2020-08-17T12:02:28.691599
zonedTimeNowDef = 2020-08-17T08:02:28.691584+02:00[Europe/Bratislava]
zonedTimeUtcDef = 2020-08-17T12:02:28.691599+02:00[Europe/Bratislava]
zonedTimeNowUtc = 2020-08-17T04:02:28.691584+02:00[Europe/Bratislava]
zonedTimeUtcUtc = 2020-08-17T08:02:28.691599+02:00[Europe/Bratislava]
Asia/Dubai GMT+4
javaDefaultTz = Gulf Standard Time
ID = 439
createdTz = Europe/Bratislava
createdStringTime = Mon Aug 17 08:02:03 CEST 2020
localTimeNow = 2020-08-17T10:02:03.888033
localTimeUtc = 2020-08-17T08:02:03.888100
zonedTimeNowDef = 2020-08-17T10:02:03.888033+04:00[Asia/Dubai]
zonedTimeUtcDef = 2020-08-17T08:02:03.888100+04:00[Asia/Dubai]
zonedTimeNowUtc = 2020-08-17T12:02:03.888033+04:00[Asia/Dubai]
zonedTimeUtcUtc = 2020-08-17T10:02:03.888100+04:00[Asia/Dubai]

javaDefaultTz = Gulf Standard Time
ID = 440
createdTz = America/Dominica
createdStringTime = Mon Aug 17 02:02:28 AST 2020
localTimeNow = 2020-08-17T10:02:28.691584
localTimeUtc = 2020-08-17T14:02:28.691599
zonedTimeNowDef = 2020-08-17T10:02:28.691584+04:00[Asia/Dubai]
zonedTimeUtcDef = 2020-08-17T14:02:28.691599+04:00[Asia/Dubai]
zonedTimeNowUtc = 2020-08-17T06:02:28.691584+04:00[Asia/Dubai]
zonedTimeUtcUtc = 2020-08-17T10:02:28.691599+04:00[Asia/Dubai]

Zaver

Pre mna vzniklo niekolko zaujimavych zisteni. Co sa tyka LocalDateTime ak chceme vidiet na vsetkych serveroch rovnaky cas v UTC musime si ho zarovnat v kode a nepouzivat premennu time_zone. To znamena tak ako je v prvom teste v stlpci localTimeUtc. Vsetky localTimeUtc ukazuju 05:46 bez ohladu na tom v akej casovej zone boli vytvorene a kde su citane, v pripade ze sa nastavi premenna time_zone tak hibernate urobi ‚nadpracu‘ a shiftne udaje znovu co sposobi ich prehadzanie pre stlpec localTimeUtc tak ako je ukazane v druhom teste. Treba si vsimnut aj problem v prvom teste v DB v stlpci localTimeNow kde sa udaje ulozia pre casovu zonu kde bol udaj vytvoreny, takze prvy objekt ma cas vlozenia 07:46 a druhy aj ked bol vlozeny neskor z pohladu plynutia casu ma v DB hodnotu 01:46. To znamena ze aj keby ste si niekde pamatali timezonu kde bol udaj vytvoreny, mohlo by to sposobovat problemy pri triedeni cez SQL ORDER BY.
V druhom teste zas dopadol lepsie stlpec localTimeNow. V DB su ulozene udaje spravne v UTC (06:02) a pri vyberani udajov dojde ku spravnemu casovemu posunutiu takze sice nevidime v kazdej casovej zone rovnaky udaj ale vidime cas v ktorom bol zaznam vytvoreny akoby bol vytvoreny prave na tom prislusnom servri (02:02, 08:02, 10:02).
Co sa tyka ZonedDateTime tak jedine zaujimave zistenie je ze zonedTimeNowUtc a zonedTimeUtcUtc sa nijako nelisia, to sa sice dalo ocakavat ale ja davam prednost experimentu.
Ostatne zavery si musite urobit sami po nastudovani vysledkov a pouzit taky mod zarovnavania casu a nastavenia hibernate ktory vam vyhovuje. Neda sa urcit ktory je spravny zalezi od sposobu pozadovaneho vyuzitia. V kazdom pripade sa da aspon zistit ktore su nespravne ;-).

Sdílet

Přidat názor
Přidat názor

Pavel Tavoda

Dále u nás najdete

Jak českým firmám pomáhá digitální transformace?

Nevyžádané marketingové hovory přísnější zákon nevymýtil

Řetězce falšují slevy záměrně. Měly by dostávat vyšší sankce

Obsah vytvořený pomocí AI vidíte každý den. Jen to nevíte

Nejen Coca-Cola, ale i Lidl má červený vánoční kamion

SSD už také mají obrovskou kapacitu – až 122 TB

Čeští středoškoláci očekávají vypuštění své družice

ÚOOÚ šetří české vydavatele kvůli „pay or ok“

Lumbální punkce sice vypadá děsivě, ale nebolí

Opatrně s kofeinovým práškem, už jedna lžička je životu nebezpečná

Prokletí slavného spisovatele: duševní poruchy a sebevraždy

Novu nebude tvořit AI. A umělé moderátory v ČRo nečekejte

Stow vytváří regálové systémy pro Alzu, Lidl i Rohlík

První pacientka podstoupila genovou terapii bránící slepotě

Od pokeru do dílny: Vyrábí v ní nábytek z ohýbaného betonu

Nitroděložní tělíska jsou spolehlivá a hodí se i pro mladé ženy

Dřívější neplodnost dnes řeší miniinvazivní operace

World of Warcraft slaví 20 let a stále se hraje

Vánoční slevy operátorů? Daleko za očekáváním

Šlamastika kolem pozastavené výplaty důchodu

ŠKOLENÍ: Jak bezpečně spravovat a provozovat linuxové servery?
VÍCE INFO