src/log/DB/doc/date_day_column.sql

   1 -- Copyright (C) 1996-2018 The Squid Software Foundation and contributors
   2 --
   3 -- Squid software is distributed under GPLv2+ license and includes
   4 -- contributions from numerous individuals and organizations.
   5 -- Please see the COPYING and CONTRIBUTORS files for details.
   6 --
   7
   8 -- we need a separate column to store the date and time of the request
   9 ALTER TABLE access_log ADD COLUMN date_day DATE;
  10 ALTER TABLE access_log ADD COLUMN date_time TIME;
  11
  12 -- let's populate the new columns, in case some rows already exist;
  13 -- the date and time values should be set by a trigger
  14 UPDATE access_log SET date_day  = DATE(FROM_UNIXTIME(time_since_epoch));
  15 UPDATE access_log SET date_time = TIME(FROM_UNIXTIME(time_since_epoch));
  16
  17 -- let's create a view that uses the date column
  18 CREATE VIEW requests_per_day_2 AS SELECT date_day, COUNT(*) AS num_of_requests FROM access_log GROUP BY 1 ORDER BY 1;
  19
  20 -- that view needs an index on the group by column
  21 CREATE INDEX date_day_idx ON access_log(date_day);
  22
  23
  24 -- a trigger that automatically extracts the date value from the time_since_epoch column
  25 -- and stores it in the date_day column
  26 DELIMITER //
  27 CREATE TRIGGER extract_date_day_bi BEFORE INSERT ON access_log FOR EACH ROW
  28 BEGIN
  29         SET NEW.date_day  = DATE(FROM_UNIXTIME(NEW.time_since_epoch));
  30     SET NEW.date_time = TIME(FROM_UNIXTIME(NEW.time_since_epoch));
  31 END //
  32
  33
  34 -- Note: after running this script against an already populated access_log,
  35 -- views have to be recreated, or the new date_day column will not show up.