MySQL Query Group By Date (12 hours Interval)-CodePudding

I wrote a sql query for getting number of users created in a period of time for plotting graph (grafana or chart js) , and my sql query is

 SELECT
  date(user.created_date) as "time",
  count(distinct user.id) as Number Of User,
  status as status
FROM user
WHERE
  created_date >= FROM_UNIXTIME(1649635200) AND 
  created_date < FROM_UNIXTIME(1649894399)
GROUP BY user.status, date(user.created_date)
ORDER BY date(user.created_date)

Here in this query created date is passed dynamically from front-end, Now i am getting the result like,

Now whenever i select the date filter from last 24 hours/12 hours some of the result is not there,

Is there is any way to modify my sql query to group by created_date with 12 hour interval For Example, Now query result is 11/04/2022 - 5 Users(Application Created) I want query result like this 11/04/2022 00:00:00 2 - 2 users created 11/04/2022 12:00:00 - 3 users created

CodePudding user response：

In grafana there is a filed $__timeFrom() and $__timeTo() On the basis of this I rewrite my query:

SELECT
  (CASE 
    WHEN HOUR(TIMEDIFF($__timeFrom(), $__timeTo())) <= 24
    THEN user.created_date
    ELSE date(user.created_date) end) AS "time",
  count(distinct user.id) as Users,
FROM user
WHERE
  user.created_date >= $__timeFrom() AND 
  user.created_date < $__timeTo() AND
GROUP BY CASE
  when HOUR(TIMEDIFF($__timeFrom(), $__timeTo())) <= 24
  then user.created_date
  else date(created_date) end
ORDER BY CASE
  when HOUR(TIMEDIFF($__timeFrom(), $__timeTo())) <= 24
  then user.created_date
  else date(created_date) end;

CodePudding user response：

If you use this expresion in your GROUP BY, you'll get a 12-hour grouping.

DATE(created_date)   INTERVAL (HOUR(created_date) - HOUR(created_date) MOD 12) HOUR

You can, if you have the priv, declare a stored function to make this easier to read.

DELIMITER $$
DROP FUNCTION IF EXISTS TRUNC_HALFDAY$$
CREATE
  FUNCTION TRUNC_HALFDAY(datestamp DATETIME)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'truncate to 12 hour boundary. Returns the nearest
           preceding half-day (noon, or midnight)'
  RETURN DATE(datestamp)  
                INTERVAL (HOUR(datestamp) -
                          HOUR(datestamp) MOD 12) HOUR$$
DELIMITER ;

Then you can do

SELECT
  TRUNC_HALFDAY(user.created_date) as "time",
  count(distinct user.id) as Number Of User,
  status as status
FROM user
WHERE
  created_date >= whatever AND 
  created_date < whatever
GROUP BY user.status, TRUNC_HALFDAY(user.created_date)
ORDER BY TRUNC_HALFDAY(user.created_date)

Even though the function appears three times in your query, because it's declared DETERMINISTIC it only gets called once per row.

More complete writeup here.