0

I have a table structure that looks like this:

id:Bigint(11)
channel_id:Bigint(11)
server_id:Bigint(11)
created:timestamp

and i want to get a table that shows the number of entries in a given time period (each hour, so from 00:00-00:59 and 01:00-01:59 etc...) for each channel_id Where the server_id is X (and if possible also the total number over all channel_ids). And i need it to also show time periods where there is no entry as well as the channel_ids where there is no message for that period (all channel_ids are in the data set at least once)

the best thing i could come up with is

SELECT 
    DATE_FORMAT(
    MIN(created),
    '%d/%m/%Y %H:00:00'
  ) AS tmstamp,
  COUNT(*) AS cnt 
FROM
  messages
GROUP BY ROUND(UNIX_TIMESTAMP(created) / 3600)

I could not find anything regarding the variable number of columns (since i dont know how many distinct channel_ids there are for a given server_id)

Thanks for any help in advance.

EDIT: removed the timeperiod part of the question, my main question remains however. How do i get the channel_ids as columns even if i dont know them beforhand.

Littellittel
  • 13
  • 1
  • 4
  • 1
    If certain periods of time be completely missing in your table, then you may have to join with a calendar table to cover all periods. – Tim Biegeleisen Dec 13 '18 at 15:11
  • You have to use a so called "calender table" [How to populate a table with a range of dates?](https://stackoverflow.com/questions/10132024/how-to-populate-a-table-with-a-range-of-dates) also marked this one as duplication – Raymond Nijland Dec 13 '18 at 15:12

0 Answers0