From a3714081f22821ee26b44d74553fff11f4d22d99 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Wed, 13 Sep 2023 17:18:07 -0500 Subject: [PATCH] Set default time zone in cluster setup --- .gitignore | 3 ++ db/include/macros.m4 | 6 +++ db/set_timezone.m4 | 28 ++++++++++++++ doc/src/architecture/timestamps.m4 | 59 +++++++++++++++++++++++++++++- doc/src/epilog.inc.m4 | 7 ++++ include/global_constants.m4 | 7 ++++ make_files/make_db.mk | 19 +++++++++- 7 files changed, 126 insertions(+), 3 deletions(-) create mode 100644 db/set_timezone.m4 diff --git a/.gitignore b/.gitignore index 15d3fb2..6af8431 100644 --- a/.gitignore +++ b/.gitignore @@ -2,6 +2,9 @@ db/schemas/**/*.sql db/*.sql +# Generated shell scripts +db/set_timezone.sh + # Built image files doc/images/ diff --git a/db/include/macros.m4 b/db/include/macros.m4 index 74e47e1..f732557 100644 --- a/db/include/macros.m4 +++ b/db/include/macros.m4 @@ -41,6 +41,12 @@ define(`sdb_generated_sql', -- typing "make" at the command line.) ') +define(`sdb_generated_comment', +`# DO NOT EDIT THIS FILE. It was automatically generated. Edit +# the *.m4 files instead. (Files _should_ be re-created by +# typing "make" at the command line.) +') + dnl SET clause used in all function definition statements. define(`sdb_function_search_path', `sokwedb, lookup, upload, pg_temp') define(`sdb_function_set_search_path', diff --git a/db/set_timezone.m4 b/db/set_timezone.m4 new file mode 100644 index 0000000..dc29797 --- /dev/null +++ b/db/set_timezone.m4 @@ -0,0 +1,28 @@ +#!/bin/sh +# Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. +# +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see . +# +# Set the default time zone for WITH TIMEZONE data types. +# +# Karl O. Pinc +# +dnl +dnl m4 includes +include(`macros.m4')dnl +include(`constants.m4')dnl + +sdb_generated_comment()dnl + +printf "ALTER DATABASE ${TARGET_DB} SET TimeZone TO sdb_timezone;\n" diff --git a/doc/src/architecture/timestamps.m4 b/doc/src/architecture/timestamps.m4 index 6321f7d..78547f3 100644 --- a/doc/src/architecture/timestamps.m4 +++ b/doc/src/architecture/timestamps.m4 @@ -24,7 +24,21 @@ sdb_generated_rst()dnl Dates and Times --------------- -Date values are always output in YYYY-MM-DD format.\ [#f1]_ +In SQL dates and times are written as strings, so are enclosed +in single quotes (or `dollar quotes`_). +But the system must be told that the data is a date, or a time, etc.\ [#f1]_ +The easy way to type this is to follow the string with two colons +and then the name of the appropriate `data type `_. +This is best illustrated with an example:: + + SELECT '1707-05-23'::DATE; + +.. _input_and_output_representations: + +Input and Output Representations +```````````````````````````````` + +Date values are always output in YYYY-MM-DD format.\ [#f2]_ This is unambiguous and more universal than most date representations. Date values may be input in a wide variety of formats. @@ -38,8 +52,49 @@ PostgreSQL_ documentation, either that on `dates and times the `details of date/time interpretation `_. +.. _time zones: + +Time Zones +`````````` + +SokweDB contains few, if any, time-zone aware columns. +For this reason, and reasons given below, most users will not need to +concern themselves with time zones. +Date + time combinations, called timestamps, may or may not be time zone aware. +This is also true of plain, 24-hour, time values. +Time zone aware values display differently depending on the time zone +in which they are viewed -- or at least they can display differently. +A time zone aware time value which displays as 10:00AM in the US/Eastern +time zone would display as 9:00AM in the US/Central time zone. + +Dates and times `without` a time zone, most time-related data recorded in +SokweDB, are as-of the time recorded in the field. +So in Gombe time, and the time values won't change no matter where viewed. + +Some other dates and times, perhaps those involving administrative actions +like, perhaps, the automatically recorded time of database updates, may +be time zone aware. + +By default, time-zone aware data is input and output in the ``sdb_timezone`` +time zone. +If you wish to have time-related data be input and output in a different +time zone you must tell the server which time zone you are in.\ [#f3]_ +This does not happen automatically. +Further, the change to your time zone only lasts for the duration +of your connection to the database. +Practically speaking, this usually, depending on the tool you use to access +the database, means that you must change your time zone every +time you submit SQL statements to the server. + +To sum up, most of the time-related values you work with will be +in Gombe time. The rest are in ``sdb_timezone`` unless you +put some work into changing your time zone. .. rubric: Footnotes -.. [#f1] This is the ISO 8601 format. +.. [#f1] The string is said to be `cast` to the desired data type. + +.. [#f2] This is the ISO 8601 format. + +.. [#f3] E.g. ``SET TIME ZONE 'US/Mountain';`` diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 05d8c58..15186b3 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -27,6 +27,12 @@ sdb_generated_rst()dnl .. _PostgreSQL: https://postgresql.org +.. _dollar quotes: https://www.postgresql.org/docs/current/ + sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING + +.. _date and time types: + https://www.postgresql.org/docs/current/datatype-datetime.html + .. _UUID: https://en.wikipedia.org/wiki/Universally_unique_identifier .. _technical debt: https://en.wiktionary.org/wiki/technical_debt @@ -175,4 +181,5 @@ sdb_generated_rst()dnl .. The upload schema, which is not yet documented .. |Uploads| replace:: :ref:`Uploads ` + sdb_if_htmlout(*m4[Page last updated: |today|]m4*, *m4[]m4*) diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 1a00874..3972ac0 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -40,6 +40,13 @@ define(`sdb_admin_group', `admin') define(`sdb_reader', `reader') define(`sdb_writer', `writer') +dnl +dnl Database configuration +dnl + +dnl The time zone for timestamps with time zone. +define(`sdb_timezone', `UCT') + dnl dnl Special values, table-specific -- hardcoded constants dnl diff --git a/make_files/make_db.mk b/make_files/make_db.mk index a0edf61..32969e4 100644 --- a/make_files/make_db.mk +++ b/make_files/make_db.mk @@ -487,6 +487,8 @@ $(DB_TARGETS) db/creategroups.sql: %.sql: %.m4 $(DB_DEPENDS) ## Additional databases may be created using individual logins. Creating ## them with "make" will properly initialize them for use with SokweDB. +GENERATED_SHELL := db/set_timezone.sh + # Build the statements used to setup dbs. CREATE_DB := createdb --encoding='SQL_ASCII' \ --locale='C' \ @@ -550,7 +552,7 @@ create-groups: db/creategroups.sql ## transaction. Can create the database and then fail, ## leaving the cluster in an unusual state. .PHONY: init-database -init-database: +init-database: $(GENERATED_SHELL) [ -n "$(TARGET_DB)" ] \ || { printf 'The TARGET_DB variable must be set\n' >&2 ; \ exit 1 ; } @@ -561,6 +563,7 @@ init-database: ( $(PSQL_SETUP) \ printf '$(SET_SEARCH_PATH)\n' ; \ printf "$(SET_DATESTYLE)\n" ; \ + db/set_timezone.sh ; \ ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y @@ -635,3 +638,17 @@ set-datestyle: printf "$(SET_DATESTYLE)\n" ; \ ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y + +## set-timezone Set the default TimeZone in the database named +## by the TARGET_DB variable. +.PHONY: set-timezone +set-timezone: db/set_timezone.sh + ( $(PSQL_SETUP) \ + db/set_timezone.sh ; \ + ) \ + | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y + +# The generated shell scripts +$(GENERATED_SHELL): %.sh: %.m4 + m4 $(M4_DB_INCLUDE_ARGS) $< > $@ + chmod a+x $@ -- 2.34.1