MySQL query or procedure to return table from values computed over multiple rows

I have a network created MYSQL table with following fields:

IP_SRC, IP_DST, BYTES_IN, BYTES_OUT, START_TIME, STOP_TIME
1.1.1.1 8.8.8.8 1080      540        1580684018  1580684100
8.8.4.4 1.1.1.1 2000      4000       1580597618  1580597800

The TIME values are epoch time ticks and each record is basically one TCP session.

I would like formulate a query (or procedure) to return a table with the following fields:

DayOfMonth, TotalOutBytes
1            12345
2            83747
3            2389

where DayOfMonth is the last 14 days or a range of last “n” days (or to keep the focus on the main problem assume the values are 1, 2, 3 of Feb 2020). The challenge is to grab all rows from the network table where STOP_TIME falls within the timeticks for DayOfMonth value for that row and sum up the BYTES_OUT to report as TotalOutBytes for that date.

I’m afraid I’m somewhat new to MYSQL and hopelessly lost.

Answer

Consider:

select
    day(from_unixtime(stop_time)) dayOfMonth,
    sum(bytes_out) TotalOutBytes
from mytable
where stop_time >= unix_timestamp(current_date - interval 14 day)
group by day(from_unixtime(stop_time)) 

Rationale:

• the where clause uses unix_timestamp() to generate the unix timstamp corresponding to 14 days before the current date, which we can use to filter the table
• from_unixtime() turns an epoch timestam pto a datetime value, then day() gives you the day number for that point in time
• you can then aggregate with that value and sum bytes_out per group