Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:
- area (char)
- lognotice (char or text)
- logtime (timestamp when the event was logged).
Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated - the Query to fetch the data could typically look like this:
SELECT \* FROM log log1 WHERE logtime = ( SELECT max(logtime) FROM log log2 WHERE log2.area = log.area)
To keep things clean and tidy, I only sorte data from the past month, week or day (depending on the “log intensity”). To achieve this I usually do something like this:
DELETE FROM log WHERE logtime < ###time###