1 -- @tag: time_recordings_date_duration
2 -- @description: Erweiterung Zeiterfassung um Datum und Dauer
3 -- @depends: time_recordings2
5 ALTER TABLE time_recordings ADD COLUMN date DATE;
6 ALTER TABLE time_recordings ADD COLUMN duration INTEGER;
8 UPDATE time_recordings SET date = start_time::DATE;
9 ALTER TABLE time_recordings ALTER COLUMN start_time DROP NOT NULL;
10 ALTER TABLE time_recordings ALTER COLUMN date SET NOT NULL;
12 UPDATE time_recordings SET duration = EXTRACT(EPOCH FROM (end_time - start_time))/60;
14 -- trigger to set date from start_time
15 CREATE OR REPLACE FUNCTION time_recordings_set_date_trigger()
18 IF NEW.start_time IS NOT NULL THEN
19 NEW.date = NEW.start_time::DATE;
25 CREATE TRIGGER time_recordings_set_date BEFORE INSERT OR UPDATE ON time_recordings FOR EACH ROW EXECUTE PROCEDURE time_recordings_set_date_trigger();
27 -- trigger to set duration from start_time and end_time
28 CREATE OR REPLACE FUNCTION time_recordings_set_duration_trigger()
31 IF NEW.start_time IS NOT NULL AND NEW.end_time IS NOT NULL THEN
32 NEW.duration = EXTRACT(EPOCH FROM (NEW.end_time - NEW.start_time))/60;
38 CREATE TRIGGER time_recordings_set_duration BEFORE INSERT OR UPDATE ON time_recordings FOR EACH ROW EXECUTE PROCEDURE time_recordings_set_duration_trigger();