From 6bea6dcda7c4525fa1004f6c7885beb3f8a4b426 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 18 Jan 2024 22:56:20 +0000 Subject: [PATCH] Cleanup data required to tidy data types, fixes problem #10 --- conversion/make_tidy.sh | 1 + conversion/tidy_cleanups.sql | 89 ++++++++++++++++++++++++++++++++++++ 2 files changed, 90 insertions(+) create mode 100644 conversion/tidy_cleanups.sql diff --git a/conversion/make_tidy.sh b/conversion/make_tidy.sh index 797f7e9..f334973 100755 --- a/conversion/make_tidy.sh +++ b/conversion/make_tidy.sh @@ -34,6 +34,7 @@ set -o pipefail { eval "${PSQL_TIDY_SETUP}" ; ./speedups.sh ; + cat tidy_cleanups.sql ; cat tidy.sql ; } | psql ${PSQL_ARGS} ${PSQL_SINGLE_TRANS} --set=ON_ERROR_STOP=y \ | { ! grep -v '^[[:space:]]*$' ; } diff --git a/conversion/tidy_cleanups.sql b/conversion/tidy_cleanups.sql new file mode 100644 index 0000000..4740e4d --- /dev/null +++ b/conversion/tidy_cleanups.sql @@ -0,0 +1,89 @@ +-- Copyright (C) 2024 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 . +-- +-- Change the data in the tidy schema in preparation for the change +-- of column datatypes. +-- +-- Karl O. Pinc + +-- Fix: +-- (#10) BRECORD_NOTES contains rows where BREC_time has values that are +-- not just a time +update "BRECORD_NOTES" + set "BREC_time" = ('1899-12-30 ' || "BREC_time"::TIME)::TIMESTAMP + where "BREC_time"::DATE <> '1899-12-30' + and "BREC_FOL_B_focal_AnimID" + || '*' + || ("BREC_FOL_date"::DATE)::TEXT + || '*' + || ("BREC_time"::TIME)::TEXT + in ( + 'FO*2014-01-11*09:00:00', + 'EOW*2014-01-26*15:00:00', + 'SW*2012-08-18*14:00:00', + 'FD*1994-01-04*06:00:00', + 'FD*1994-07-01*16:50:00', + 'FE*2011-11-18*09:00:00', + 'FE*2011-11-18*08:00:00', + 'SDB*2011-11-17*12:00:00', + 'FO*2012-04-09*20:00:00', + 'GLD*2012-04-16*17:00:00', + 'GLD*2012-04-16*13:00:00', + 'SA*2013-01-26*12:00:00', + 'SIF*2013-04-26*17:00:00', + 'SAM*2013-02-08*17:00:00', + 'SAM*2013-02-08*15:00:00', + 'KON*2012-09-10*00:00:00', + 'RUD*2012-07-30*12:00:00', + 'FLI*2012-06-24*21:00:00', + 'LON*2013-02-26*13:00:00', + 'KON*2013-02-06*14:00:00', + 'FAN*2013-09-27*19:00:00', + 'SL*2013-12-10*04:00:00', + 'TN*2013-12-26*16:00:00', + 'KOC*2014-02-18*15:00:00', + 'GLI*2013-12-03*15:00:00', + 'BIM*2014-02-13*17:00:00', + 'EDG*2014-09-05*09:00:00', + 'KOC*2014-06-21*14:00:00', + 'LAM*2014-01-30*00:00:00', + 'MGA*2014-04-09*14:00:00', + 'FLI*2015-09-30*15:00:00', + 'TN*2014-04-10*21:00:00', + 'FO*2014-03-09*00:00:00', + 'AL*1993-09-03*14:00:00', + 'FD*1993-08-18*20:00:00', + 'GM*2014-09-03*21:00:00', + 'NUR*2017-09-08*00:00:00', + 'SL*2017-09-02*21:00:00', + 'RUM*2017-10-15*12:00:00', + 'GLI*2017-12-07*21:00:00', + 'TOM*2017-12-09*14:00:00', + 'IMA*2017-12-22*21:00:00', + 'SL*2018-01-05*21:00:00', + 'SL*2018-01-05*09:00:00', + 'GM*2018-02-09*21:00:00', + 'Group search*2018-02-28*21:00:00', + 'GIM*2018-03-02*15:00:00', + 'SN*2018-06-18*12:00:00', + 'GIM*2017-01-13*12:00:00', + 'DL*2018-08-08*21:00:00', + 'LOS*2016-08-28*12:00:00', + 'GM*2016-08-08*21:00:00', + 'LON*2015-12-01*17:00:00', + 'LAM*2016-03-22*15:00:00', + 'KON*2016-03-30*12:00:00', + 'LON*2016-05-13*00:00:00' +); -- 2.34.1