Search code examples
mysqldatetimetransfer

MYSQL: Transfer values from one table to another based on date/hour

i have the following MYSQL tables:

Part of the input table 'input':

Input Table

and part of the Output Table 'output':

Output table

both date/time columns are pre given and cannot be changed. they are the basis of the MYSQL code I want to use to do the following: I want to sum all values from 'total' column of the input table from the same date and same hour and put them into 'total' column of the output table where the 'time' column has the same date and same hour as in the input table.

Would this be possible with MYSQL only? Or do I need PHP as well? The input table has about 400,000 values.

I started with:

SELECT SUM(total) FROM input
WHERE date BETWEEN '2019-06-06 00:00:00' AND '2019-06-06 23:59:59'

But I dont know how to continue.

Any help would be appreciated. Thanks in advance :)

Solution

  • Get the sums in the input table and join to the output table in the UPDATE statement:

    update output o 
inner join (
  select date_format(date, '%Y%m%d%H') hour, sum(total) total
  from input
  group by date_format(date, '%Y%m%d%H')
) i on i.hour = date_format(o.date, '%Y%m%d%H')
set o.total = i.total