MySQL Daily Totals: Date Keys Are Dates, Not Timestamps

After the fix, each counter produces one daily value from two consecutive date-keyed snapshots, while the running total remains untouched. When the daily table stays empty even though the current totals appear in log_counter, trace the failure through the scheduled group, the stored record_date, and the self-join.

What is the empty daily table telling you?

The screen may show valid totalizer values and a populated source table while daily_counter remains empty. That combination points away from the live counter binding: the acquisition path has already written data. The failure lies in the rows selected by the calculation query or in execution of the calculation group.

Observed condition	Likely cause	Check
No rows in log_counter	The logging group did not execute, its source binding failed, or the insert failed	Check group status and confirm one committed row per counter
Only one date exists for a counter	The self-join has no preceding snapshot	Wait for or insert a valid second daily snapshot before calculating
Current and previous rows exist, but the daily table is empty	record_date contains time-of-day values that fail equality tests	Inspect the complete stored values, not only the formatted date shown on screen
Some counters calculate and others do not	The tag strings differ between days or a daily snapshot is missing	Compare the exact string and date pair for each counter
Daily values appear more than once	The plain INSERT ran repeatedly for the same date and tag	Check the schedule, execution history, and uniqueness policy
A daily value is negative	The ongoing counter decreased, reset, rolled over, or was replaced	Compare the two stored totals before accepting the result

The calculation requires two rows for the same tag: one current snapshot and one snapshot exactly one calendar day earlier. An INNER JOIN returns nothing when either side is missing or either join key differs.

Which date-matching approach should you use?

Two configurations can calculate the difference. Storing a canonical date is the recommended configuration because it makes the data model match the daily reporting interval.

Approach	Source setting	Calculation condition	Effect
Store a date-only key	Write SELECT DATE(NOW()) into record_date	Compare record_date values directly	Simple joins, clear daily keys, and fewer time-boundary failures
Normalize timestamps in the query	Keep the existing time-of-day values	Apply DATE() to both sides of the comparison	Can recover existing data, but functions on indexed columns may reduce index use and multiple same-day snapshots can create ambiguous matches

Use the date-only design for new records. Configure the logging group with a Triggered Expression Item named RecordDate, run SELECT DATE(NOW()), and map its Target Name to record_date. A date target stores the calendar date; a datetime target may display the same value at midnight. Either representation works with direct equality when every writer uses the same canonical value.

Query-side normalization is useful when historical records already contain times. It works only when each tag has one intended snapshot per calendar day. If several readings exist for one tag and day, joining by date alone can match several row combinations. Select the intended row by an explicit rule before calculating rather than treating duplicates as interchangeable.

Why does a timestamp stop the self-join?

The failing data contains values such as 2014-10-17 00:01:02. The filter compares that complete value with DATE(NOW()), which represents the current calendar date at midnight when compared as a datetime. Therefore this condition is false:

WHERE today.record_date = DATE(NOW())

The join also compares complete values after subtracting one day:

DATE_SUB(today.record_date, INTERVAL 1 DAY) = yesterday.record_date

Subtracting one day preserves the time component. For example, subtracting a day from 2014-10-17 00:01:02 produces a value with 00:01:02; it does not equal 2014-10-16 00:00:02. The date may look right in a formatted display while the stored keys remain unequal.

The other join predicate is equally significant:

today.tag = yesterday.tag

The tag column is a string label used to identify the counter. It does not have to be an actual live tag path, but it must remain identical across daily rows. Whitespace, spelling changes, or assigning a new label breaks the pair.

How should the two transaction groups be configured?

Separate snapshot acquisition from daily calculation. The first group writes the current ongoing totals; the second runs later and calculates differences from committed source rows.

1. Create log_counter with the supplied fields id, record_date, tag, counter_value, and t_stamp.
2. Create daily_counter with id, record_date, tag, daily_counter_value, and t_stamp.
3. Create the Block Transaction Group LogCounters.
4. Add a Triggered Expression Item named RecordDate. Use SELECT DATE(NOW()) and set its Target Name to record_date.
5. Add Block Items for the counter label and total. Map their Target Names to tag and counter_value, then add each counter label and value as a paired entry.
6. Schedule LogCounters once per day at a consistent point in the reporting cycle. One cited configuration logs at 12:01am; another uses 7:00.
7. Create LogDailyCounters and schedule it after the snapshot group has finished. The cited separations are 12:01am followed by 12:15am, or 7:00 followed by 7:15.
8. Add the daily calculation as the triggered expression for the second group.

The deciding requirement is ordering, not a universally correct clock time. The calculation must start after all current rows are committed. Use one time basis for the scheduled groups and NOW(); a date boundary interpreted differently by the scheduler and database can select the wrong reporting day.

What SQL calculates the daily total?

With canonical date values in record_date, use the direct self-join:

INSERT INTO daily_counter
    (record_date, tag, daily_counter_value, t_stamp)
SELECT
    yesterday.record_date,
    today.tag,
    today.counter_value - yesterday.counter_value,
    NOW()
FROM log_counter today
INNER JOIN log_counter yesterday
    ON DATE_SUB(today.record_date, INTERVAL 1 DAY) = yesterday.record_date
   AND today.tag = yesterday.tag
WHERE today.record_date = DATE(NOW());

The aliases today and yesterday represent two logical copies of log_counter. The WHERE clause selects current-day snapshots. The ON clause pairs each selected row with the same label one day earlier. The subtraction is current total minus previous total:

daily_counter_value = today.counter_value - yesterday.counter_value

The inserted record_date comes from yesterday.record_date. A calculation run from the October 17 snapshot and the October 16 snapshot is therefore labeled October 16, the day represented by the interval between those readings. t_stamp records when the derived row was written and is not the date-matching key.

For timestamped historical data, the equivalent calendar-date matching form is:

ON DATE_SUB(DATE(today.record_date), INTERVAL 1 DAY)
       = DATE(yesterday.record_date)
AND today.tag = yesterday.tag
WHERE DATE(today.record_date) = DATE(NOW())

Use that form only after confirming one intended row per tag per day. The preferred long-term correction remains writing record_date with SELECT DATE(NOW()).

How do you diagnose a calculation that inserts no rows?

1. Confirm that LogDailyCounters actually executed after LogCounters. A valid query cannot calculate from an uncommitted or missing current snapshot.
2. Filter log_counter for the current calendar day and inspect the full record_date. A value such as 00:01:02 will not equal the midnight value produced by DATE(NOW()) in a direct datetime comparison.
3. For each current row, locate the preceding calendar-day row with the exact same tag. One source row is insufficient; an inner join needs both sides.
4. Run the SELECT portion without the INSERT. This separates matching and arithmetic problems from permissions or destination-table failures.
5. Check the selected columns. The preview must show the prior date, current tag, current-minus-prior difference, and current write time.
6. Only after the preview is correct, run the full insert and confirm the new destination rows.

Do not troubleshoot the live tag first when the expected total already appears in log_counter. The tag is right; the binding between stored dates is wrong. Conversely, if the source table lacks the counter row, repair the acquisition group before changing the SQL.

How do you verify values and prevent misleading totals?

Verify row count and arithmetic independently. With three configured meters and complete current/previous pairs, one execution should select three rows. From the recorded October 16 and October 17 totals, the expected differences are:

Tag	Previous total	Current total	Expected daily value
AHT1	248983089	249391246	408157
AHT2	151008430	151265661	257231
AHT3	205081471	205398003	316532

The design assumes ongoing, non-resetting counters. A reset, rollover, replacement, or manual correction can make current-minus-previous negative or otherwise invalid. Flag such results for review instead of silently treating them as consumption.

The supplied statement is a plain INSERT. Re-running it for the same reporting interval can create duplicate daily rows unless the destination applies a uniqueness rule or the execution checks for an existing date-and-tag result. Decide the retry policy before enabling automatic recovery. Also confirm that each source date and tag identifies one snapshot; otherwise a self-join can multiply rows.

To calculate another interval, change the time relationship in the join and align the snapshot schedule with that interval. Keep the same core rule: identify two unambiguous snapshots for the same counter, subtract the older total from the newer total, and label the derived period consistently.

FAQ