Mercurial > gemma
diff wamos.sql @ 59:37faa7359ea6
Interfaces may provide timestamps with time zone.
Storing the time zone seems easier than normalising
everything to UTC at application level.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 24 May 2018 14:41:41 +0200 |
parents | 30cb2f87c268 |
children | f0ca64da9446 |
line wrap: on
line diff
--- 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) );