BAG: Tijdzone voor BAG is onjuist

[sebastic]

In commit b2a2038 zijn de timestamp kolommen door @borrob voorzien van een timezone, hierdoor klopt de data in de postgis database niet meer met de XML die is ingelezen.

Voorbeeld:

        <bag_LVC:Woonplaats>
          <bag_LVC:identificatie>3242</bag_LVC:identificatie>
[...]
          <bag_LVC:tijdvakgeldigheid>
            <bagtype:begindatumTijdvakGeldigheid>2010060600000000</bagtype:begindatumTijdvakGeldigheid>
            <bagtype:einddatumTijdvakGeldigheid>2017091200000000</bagtype:einddatumTijdvakGeldigheid>
          </bag_LVC:tijdvakgeldigheid>
[...]
        </bag_LVC:Woonplaats>

bag=# SELECT identificatie, begindatumtijdvakgeldigheid, einddatumtijdvakgeldigheid, woonplaatsnaam, woonplaatsstatus FROM woonplaats WHERE woonplaatsnaam = 'Termunten' AND einddatumtijdvakgeldigheid IS NOT NUll;
-[ RECORD 1 ]---------------+-----------------------
identificatie               | 3242
begindatumtijdvakgeldigheid | 2010-06-05 22:00:00+00
einddatumtijdvakgeldigheid  | 2017-09-11 22:00:00+00
woonplaatsnaam              | Termunten
woonplaatsstatus            | Woonplaats aangewezen

[justb4]

Timestamp met timezone is wel het meest gebruikelijk om te doen in Postgres. Bij de BAG gaat het om de datum, maar goed die verspringt dus blijkbaar. Het is ook weer typisch BAG om niet een standaard formaat voor datum/tijd te gebruiken als ISO 8601. We mogen aannemen dat de nullen een lokale NL (Europe/Amsterdam) tijd zijn. Bij inlezen wordt er naar ISO 8601 geformatteerd. Kunnen we dat niet uitbreiden met een tijdzone, desnoods converteren naar UTC (met pytz)? Of komen er dan weer nieuwe problemen, als iemand zijn DB in UTC heeft staan? Lastig, ik hoor graag suggesties.

[sebastic]

In de huidige opzet moet elke postgresql connectie de timezone expliciet instellen, dit is een bijzonder irritante regression.

De default configuratie van postgresql op Debian gebruikt de TZ waarde van het OS, wat voor systemen buiten Nederland (cloud instances e.d.) niet Europe/Amsterdam hoeft te zijn.

TIMESTAMP WITHOUT TIME ZONE zoals voorheen werdt gebruikt heeft het voordeel dat de data in de database gelijk is aan de data in de XML ongeacht de timezone van de betrokken systemen. Indien time zone normalisatie gewenst is, is dit de taak van de software die gebruik maakt van de database.

[justb4]

Ben geen time expert: maar begreep dat door gebruik van TIMESTAMP WITH TIME ZONE je juist kan aangeven dat de date/time die je insert voorzien is van een timezone en dat je die er daardoor altijd weer uit kan halen in lokale tijdzone. Dacht dat met TIMESTAMP WITHOUT TIME ZONEje eigenlijk 'niets' weet tenzij je ervan uitgaat dat je UTC opslaat.

Maar goed, het blijft interessant, want gaat over BAG datums, tijd is onbelangrijk. Kan DATE type ook? Laten we gezamenlijk naar de best werkbare versie sturen. Als een client ingewikkelde dingen moet doen als een tijdzone op een postgres connectie is dat niet handig...

[sebastic]

DATE type is ook geen goed die omdat dan het tijd component weg valt wat gebruikt wordt als een volgnummer:

bag=# SELECT begindatumtijdvakgeldigheid::time, COUNT(begindatumtijdvakgeldigheid::time) AS count FROM woonplaats GROUP BY begindatumtijdvakgeldigheid::time ORDER BY begindatumtijdvakgeldigheid::time;
 begindatumtijdvakgeldigheid | count 
-----------------------------+-------
 00:00:00                    |  1260
 00:00:00.01                 |   642
 00:00:00.02                 |   133
 00:00:00.03                 |    41
 00:00:00.04                 |     9
 00:00:00.05                 |     3
 00:00:00.06                 |     2
 00:00:00.08                 |     2
 00:00:00.09                 |     1
 00:00:00.1                  |    42
 00:00:00.17                 |     1
 00:00:00.22                 |     1
 00:00:01                    |   249
 00:00:02                    |   709
 00:00:03                    |   179
 00:00:04                    |    18
 00:00:05                    |    16
 00:00:06                    |    10
 00:00:06.61                 |     1
 00:00:07                    |    13
 00:00:08                    |    17
 00:00:09                    |     2
 00:00:10                    |     9
 00:00:12                    |     2
 00:00:13                    |     1
 00:00:14                    |     7
 00:00:15                    |     1
 00:00:16                    |     2
 00:00:17                    |     4
 00:00:19                    |     1
 00:00:22                    |     1
 00:00:27                    |     1
 00:00:30                    |     1
 00:00:34                    |     1
 00:01:00                    |     1
 00:10:00.03                 |     2
 10:00:00.02                 |    19
 10:00:00.03                 |     4
 10:00:00.04                 |     2
(39 rows)

TIMESTAMP WITHOUT TIME ZONE blijft de beste optie omdat het de data as-is in de database opslaat.

[borrob]

Het lijkt me juist ongewenst om geen tijdzone te gebruiken: dat laat de tijdstempel ongedefinieerd. Zeker in het geval je een willekeurige machine met willekeurige tijdzone gebruikt en daarop webservices bouwt die de data publiceert. Dan kun je nergens meer van op aan.

Misschien is het beter om toch de tijdzone van de database in te stellen op Europe/Amsterdam tijdens het opzetten van de tabellen. Zie de discussie bij het Pull request.

[sebastic]

TIMESTAMP WITHOUT TIME ZONE heeft juist al voordeel dat de data altijd hetzelfde is ongeacht de timezone van de database server en diens clients. De timestamp is dus hetzelfde als de clients zelf de XML zouden gebruiken.

Het instellen van de tijdzone van de database kan alleen door gebruikers die admin rechten op hun postgresql server hebben, dat zijn ze niet allemaal.

Als men graag een timezone aan de datums in de BAG knoopt kan met dat eenvoudig zelf:

bag=# SELECT begindatumtijdvakgeldigheid, begindatumtijdvakgeldigheid AT TIME ZONE 'Europe/Amsterdam' AS begindatumtijdvakgeldigheid_tz FROM woonplaats WHERE woonplaatsnaam = 'Termunten';
 begindatumtijdvakgeldigheid | begindatumtijdvakgeldigheid_tz 
-----------------------------+--------------------------------
 2017-09-12 00:00:00         | 2017-09-11 22:00:00+00
 2010-06-06 00:00:00         | 2010-06-05 22:00:00+00
(2 rows)

Ik zie geen reden om TIMESTAMP WITH TIME ZONE te gebruiken in het standaard BAG schema, de waardes in de BAG zijn geen volwaardige timestamp, het is een datum met tijd component gebruikt als volgnummer. Dit volgnummer moet je dus niet interpreteren, dit is gewoon een nummer, geen tijd in een time zone.

[borrob]

Erop vertrouwen dat servers en clients het vanzelf maar goed doen als ze allemaal in een andere timezone staan lijkt me juist gevaarlijk. Ik gok dat daarom issue #223 is geopend om timezones in te stellen.
Als ik het goed heb, moet je owner of superuser zijn om de tijdzone in te kunnen stellen. Mijn verwachting is dat je met een schone install van de bag-database wel over die rechten beschikt, al hoeft dat natuurlijk niet.
Uiteraard kun je eenzelfde soort query of view schrijven om de tijdzone weer te verwijderen,
De tijd in de tijdstempel wordt door de bag misbruikt om de volgorde te geven, maar de tijdzone is voor de datum ook nog steeds van belang.

[sebastic]

Er is geen enkel risico voor clients en servers die "allemaal in een andere timezone staan", dat is juist het mooie van de TIMESTAMP WITHOUT TIME ZONE data, die is voor elke client en server ongeacht hun time zone hetzelfde.

Omdat de datums in de BAG geen volwaardige datum met tijd zijn, moeten deze waardes niet als zodanig behandeld worden. Alle applicaties die van de BAG database gebruik maken moeten rekening houden dat het tijd component een volg nummer is, en dat de datums van toepassing zijn op Nederland.

Waarom is de timezone voor de datum volgens jou van belang?

Het lijkt er op dat de je geen concrete use-case hebt voor de timezone change, waarom heb je die change dan door gevoerd?

Je heb daarmee een regression geintroduceerd waardoor de BAG database niet meer te gebruiken is zoals het de voorgaande jaren wel was.

Zolang er geen concrete use-case voor timezones in de BAG database zijn moeten de betreffende changes gerevert worden (#239).

Wanneer er wel een concrete use-case is kan daarvoor een betere oplossing ontwikkeld worden. Bijvoorbeeld een optioneel post processing SQL script om een extra kolom met timezone toe te voegen naast de TIMESTAMP WITHOUT TIME ZONE kolommen die de data as-is bevatten.

[justb4]

Heren. Ik breek mij er ook het hoofd over (en hopelijk @fsteggink en @arjennienhuis nu ook). We willen voor iedereen de correcte/meest bruikbare BAG DB. Mijn gevoel voor correctheid zou zeggen om altijd WITH TIMEZONE te gebruiken, doe ik bijv met sensor-data. Maar met de datum-velden in BAG is m.i. wat bijzonders aan de hand: Ik zie de *datumtijdvakgeldigheid velden bijna als sorteerbare strings bijv YYYYMMDD. Dan weet je voor je query exact met welke datums je van doen hebt. Is dat niet het effect van WITHOUT TIMEZONE? Voor bijv bouwjaar wordt zelfs geen DATETIME gebruikt.

Lijkt mij goed use-cases te maken waarin de oorspronkelijke (of nieuwe) situatie verkeerde resultaten zou opleveren. Bijv: queries:

• aantal geldige panden tussen datum X en datum Y (kan in toekomst zijn)
• aantal geldige panden op datum X
• aantal geldige panden vandaag

Of ik deze queries nu in Amsterdam of op Hawaii uitvoer, ik moet hetzelfde aantal krijgen. Niet op Hawaii iets anders omdat het daar 9 of 10 uur vroeger is en/of ik als client "ingewikkelde" dingen moet doen.

[sebastic]

In het geval van queries in een andere timezone dan Europe/Amsterdam moet de applicatie/query zijn lokale tijd omzetten naar Europe/Amsterdam, dit kan bijvoorbeeld door het gebruik van AT TIME ZONE 'Europe/Amsterdam' in de query.

Mijn use-case is het vergelijken van de begindatumtijdvakgeldigheid waardes in de BAG en OpenStreetMap, alle woonplaatsen waarvan de waarde niet overeenkomt moeten in OSM geupdate worden. De exacte waarde zoals deze in de BAG geregistreerd is, is hierbij van belang, niet een interpretatie daarvan in een bepaalde time zone.

M.i moet NLExtract de data die het importeerd nagenoeg as-is importeren, daarnaast kan het de data verrijken met optionele script die bijvoorbeeld het gebruik in de SQL context verbeteren. Hoe de data geinterpreteerd wordt is het domein van de applicaties die gebruik maken van de NLExtract databases.

[fsteggink]

Ik heb deze discussie niet gevolgd, aangezien ik mijn hoofd over dit issue aan het breken ben. Ik ben het op zich met Bas eens dat NLExtract de data zoveel mogelijk "as is" moet importeren, maar wel "interpretatie" moet doen voor zover dat uit de BAG (of welke andere basisregistratie dan ook) voortvloeit. Als de BAG voorschrijft dat een datum aan een tijdzone gebonden is, dan moet NLExtract dit ook zo importeren. Als dit niet helder is uit de BAG documentatie, dan moeten we dit ergens in de docs van NLExtract melden en pas dan is het aan de applicatie die de dump gebruikt om te interpreteren.

Let op, het kan ook zo zijn dat Geonovum ervan uitgaat dat datums en tijdstippen altijd voor Nederland gelden. Dit zou bij hen gecheckt moeten worden. Een discussie hierover kan het beste op het PDOK-forum worden gevoerd. Dan kunnen ook makkelijk mensen van Geonovum of de LV's erbij betrokken worden. Verder gelden dit soort vragen niet alleen voor NLExtract, maar voor alle gebruikers van de basisregistraties.

[borrob]

De reden dat ik de wijziging heb gedaan is vanwege issue #223: dat stond al een tijd open en de algemene stemming was dat het een gewenste aanvulling was. Het zou mooier zijn geweest als de BAG-export een tijdzone zou specificeren (er wordt wel een SRID meergegeven voor de geom), maar helaas. Voor wat het waard is: volgens mij gebruikt het kadaster zelf wel de Europe/Amsterdam timezone, zie als voorbeeld de tijdstempel met +2 tijdzone onder voorkomen en begin in het voorbeeld hier

Op het moment gebruiken de 'actueel' en 'actueel-bestaand' views de timestamp van de server. Als de server in Hawaii staat, dan kan dat dus wel een verkeerde selectie geven.

Over een andere boeg: wellicht moeten we de begindatumTijdvakGeldigheid en einddatumTijdvakGeldigheid splitsen naar enkel een datum en een tweede, losse kolom volgnummer (lees: tijd). De 'actueel'-views kunnen dan kijken naar het hoogste volgnummer. Het hoogste volgnummer is volgens mij het geldige record voor die hele dag, ook al staat op 23:59 (dat is een aanname van mijn kant). Een groot nadeel is dat we hiermee geen 1-op-1-mapping hebben van database naar originele XML, maar dat kunnen we misschien rechttrekken door het via de views te spelen.

[sebastic]

De tabellen moet m.i. de data as-is opslaan, views e.d. toevoegingen kunnen daar verrijkingen op doen zoals het toevoegen van een time zone, het splitsen van de datum & tijd/volgnummer, etc.

[justb4]

Ik lees in laatste opmerkingen aantal heldere zaken waar ik mij in kan vinden:

1. BAG inlezen in tabellen as-is staat in NLExtract voorop
2. VIEWs/afgeleide tabellen doen hierop interpretaties/verrijkingen/conversies

Ad 1) daarom ook identifiers als strings (#108). Volgens de BAG XSD, zijn de *datumTijdvakGeldigheid velden van type bag:DatumTijd, uiteindelijk strings:

<xs:simpleType name="DatumTijd">
	<xs:annotation>
		<xs:documentation>formaat JJJJMMDDUUMMSSmm</xs:documentation>
	</xs:annotation>
	<xs:restriction base="xs:token">
		<xs:minLength value="8"/>
		<xs:maxLength value="16"/>
		<xs:pattern value="[0-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-2][0-9][0-5][0-9][0-5][0-9][0-9][0-9]"/>
	</xs:restriction>
</xs:simpleType>
    
met xs:token direct uit XMLSchema XSD:

  <xs:simpleType name="token" id="token">
    <xs:annotation>
      <xs:documentation
        source="http://www.w3.org/TR/xmlschema-2/#token"/>
    </xs:annotation>
    <xs:restriction base="xs:normalizedString">
      <xs:whiteSpace value="collapse" id="token.whiteSpace"/>
    </xs:restriction>
  </xs:simpleType>

Dit volgend zouden *datumTijdvakGeldigheid type character(16) moeten worden.
Dit heeft wel behoorlijke implicaties, maar zou meest "zuiver" zijn.

Ad 2) inderdaad denk ik dat met of zonder gebruik TZ in de VIEWs een probleem op kan treden bij gebruik dumps op andere server/tijdzone met LOCALTIMESTAMP. Met TZ zou now() gebruikt kunnen worden. Het is overigens zo dat Postgres bij TIMESTAMP WITH TIMEZONE geen tijdzone opslaat, maar naar UTC converteert en dat opslaat. Timezone is dan vervolgens een presentatie/client optie. Er zijn use cases voor TIMESTAMP WITHOUT TIMEZONE, bijv bij scheduling (factory shifts), of bijv feestdagen (Nieuwjaar valt eerder in Hawaii dan hier). Zie ook deze discussie.

De hamvraag voor BAG blijft: wel geen TZ? Ik probeer weer een interpretatie:

Met gebruik TZ en tijd bijna altijd 00:00:00, zal PG de UTC tijd opslaan. Dit (UTC) zal 1 of 2 uur eerder zijn (DST) dus op vorige dag. Maar sec beschouwd: als ik nu in Londen zit, zal bijvoorbeeld een einddatumTijdvakGeldigheid wel degelijk om 23:00 in Londen (is nu wintertijd, dan 00:00 volgende dag in A'dam) ingaan en niet als bijv Nieuwjaar in Hawaii. Denk zolang je BAG inleest in eigen tijdzone en daar gebruikt, het zonder TZ werkt. Alleen als een BAG dump zonder TZ download en een andere server tijd bevraagt, dat het misgaat.
Tenzij we afspreken alles in UTC (of deed in verleden zelfs UnixTime) op te slaan (maar dat is effectief wat .. WITH TIMEZONE doet).

Ik zou graag eens zien wat andere BAG tools doen: BEK (Geon) of PDOK (FME?). Vraag op PDOK Forum lijkt mij ook goed idee, want treft ook andere Basis Regs.

Mijn (voorzichtige) conclusie hier zou zijn: 1) lees in als char(16) in tabel, 2) converteer naar WITH TIMEZONE in VIEWs. Impact (performance, storage) echter onduidelijk.

[borrob]

Het blijkt inderdaad lastiger dan dat je in eerste instantie denkt. Ik heb wel in de BAG catalogus gevonden dat alles in de Nederlandse tijdzone wordt gedefinieerd. Dat staat expliciet beschreven in paragraaf 4.7.3 van dit document.

...
Als tijdstip (datum en tijd) voor ontstaan, wijzigen en vervallen van objecten geldt het uitgangspunt dat hierbij de tijdzone voor Nederland van kracht is: in de winter wordt de wintertijd aangehouden oftewel Midden-Europese Tijd (MET) en in de zomer wordt de zomertijd aangehouden oftewel Midden-Europese Zomertijd (MEZT).
...

Als je naar Hawaii verhuist en je wilt 'actueel' weten dan zul je dus alsnog moeten omrekenen, omdat alle data in Nederlandse tijdzone zijn uitgewerkt (ongeacht of je het opslaat met of zonder tijdzone).

Het lijkt me inderdaad zuiver om dan de character(16) aan te houden en via de views de interpretatie toe te voegen.

[sebastic]

varchar(16) is wel heel basic, daarmee moet je meer conversie doen om er weer een bruikbare datum van te maken.

[fsteggink]

@borrob: bedankt voor dit document. Wat mij betreft is het helder, dus tijdstippen volgens de tijdzone Europe/Amsterdam interpreteren en opslaan. Alsl je de data zonder tijdzone opslaat, is het m.i. juist een verlies aan informatie. Als er clients die op een andere tijdzone staan ingesteld met de BAG-data aan de slag willen, moeten ze maar hun instellingen veranderen. Het gaat om Nederlandse data.

Het is wel een slechte zaak dat de BAG tijdstippen gebruikt als een soort volgnummer. Dan kunnen ze beter een apart attribuut hiervoor opnemen wat duidelijk maakt dat het echt om een volgnummer gaat.

[sebastic]

De timezone is m.i. impliciet en hoeft niet als zodanig opgeslagen te worden, want de tijden in de datums zijn geen echte tijden en wordt de data niet meer as-is opgeslagen.

Ik zal alle timezone changes in mijn fork reverten omdat ik niet al mijn scripts en database servers ga aanpassen om de Europe/Amsterdam timezone expliciet te gebruiken. Dit issue blijft voor mij een onaanvaardbare regression.

[fsteggink]

Dat is natuurlijk prima en het fijne van hoe Github werkt :) Soms moet je keuzes maken.

[sebastic]

Een fork moeten bijhouden is nooit fijn, ongeacht dat git dit eenvoudiger maakt.

M.i moeten de timezone waardes als extra kolommen toegevoegd worden zodat de regression is opgelost door het blijven opslaan as-is van de waarde in de XML en dat voor de gebruikers die waarde hechten aan timezones dat ook beschikbaar is.