# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1527165701 -7200
# Node ID 37faa7359ea6dcd4efd53ea1924e98bd46d50d61
# Parent  30cb2f87c2684452ecfacd7800c83071c04fb57c
Interfaces may provide timestamps with time zone.

Storing the time zone seems easier than normalising
everything to UTC at application level.

diff -r 30cb2f87c268 -r 37faa7359ea6 wamos.sql
--- a/wamos.sql	Thu May 24 14:38:27 2018 +0200
+++ b/wamos.sql	Thu May 24 14:41:41 2018 +0200
@@ -112,7 +112,7 @@
        -- different model approach?
        depth_reference char(3) NOT NULL REFERENCES depth_references,
        -- XXX: Also an attribut of sounding result?
-       date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL
        -- additional_data xml -- Currently not relevant for WAMOS
        );
@@ -160,7 +160,7 @@
        position varchar REFERENCES spot_marks,
        -- additional_data xml -- Currently not relevant for WAMOS
        critical boolean,
-       date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL
        );
 CREATE TRIGGER fairway_availability_date_info
@@ -187,7 +187,7 @@
        profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
        profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
        PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url),
-       pdf_generation_date timestamp NOT NULL,
+       pdf_generation_date timestamp with time zone NOT NULL,
        source_organization varchar NOT NULL
        );
 
@@ -195,7 +195,7 @@
        bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
        surdat date NOT NULL,
        FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
-       measure_date timestamp NOT NULL,
+       measure_date timestamp with time zone NOT NULL,
        level_of_service smallint NOT NULL REFERENCES levels_of_service,
        PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service),
        available_depth_value smallint,
@@ -205,10 +205,10 @@
            water_level_value) IS NOT NULL),
        measure_type varchar NOT NULL REFERENCES measure_types,
        source_organization varchar NOT NULL,
-       forecast_generation_time timestamp,
+       forecast_generation_time timestamp with time zone,
        CHECK(measure_type <> 'forecasted'
              OR forecast_generation_time IS NOT NULL),
-       value_lifetime timestamp,
+       value_lifetime timestamp with time zone,
        CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
        );