Get an hourly sample.

[kknm]

There is a table in which cars pass through the scales. Help me make a request, where there will be 24 fields by hours, which will reflect the number of weighed cars per hour.
Fields in the table:
Number of the car,
Weight,
Time '%d.%m.%Y %H:%i'

[kev1n]

Are you looking for an sql statement for a report?
Use a procedure that creates a temporary table (search for examples in the forum) and use an sql like this:

Code: Select all

SELECT
  all_hours.`Hour`,
  COUNT(`Number_of_the_car`) AS `Number_of_weighed_cars`
  -- Add additional fields for car number, weight, etc. as needed
FROM
  (
    SELECT
      DATE_FORMAT(
        DATE_ADD(NOW(), INTERVAL -1 DAY),
        '%d.%m.%Y %H:00'
      ) AS `Hour`
    UNION ALL
    SELECT
      DATE_FORMAT(
        DATE_ADD(NOW(), INTERVAL -1 DAY + INTERVAL 1 HOUR),
        '%d.%m.%Y %H:00'
      ) AS `Hour`
    UNION ALL
    SELECT
      DATE_FORMAT(
        DATE_ADD(NOW(), INTERVAL -1 DAY + INTERVAL 2 HOUR),
        '%d.%m.%Y %H:00'
      ) AS `Hour`
    -- Add more UNION ALL blocks to generate hours for 24-hour range
  ) AS all_hours
LEFT JOIN
  `your_table_name` -- Replace with the actual name of your MySQL table
ON
  DATE_FORMAT(`Time`, '%d.%m.%Y %H:00') = all_hours.`Hour`
WHERE
  `Time` >= DATE_SUB(NOW(), INTERVAL 1 DAY) -- Only consider records from the past 24 hours
GROUP BY
  all_hours.`Hour`
ORDER BY
  all_hours.`Hour` ASC

;

A derived table called all_hours is used to generate a list of all hours within the desired time range (in this case, the past 24 hours). The LEFT JOIN with the actual table is used to include all hours, even if there are no records in the table for a specific hour. The GROUP BY clause groups the result by hour, including the hours with no data, and the ORDER BY clause orders the result by hour in ascending order. You can modify the all_hours derived table to generate more hours as needed to cover the desired time range.

[kknm]

Found a much easier way -SELECT EXTRACT (HOUR FROM field_datetime)

[kev1n]

Sure, if you don't want to show all hours, even those without records, you can use the HOUR() function.