Page 1 of 1
Get an hourly sample.
Posted: Tue Apr 18, 2023 10:24 am
by 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'
Re: Get an hourly sample.
Posted: Tue Apr 18, 2023 1:08 pm
by 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.
Re: Get an hourly sample.
Posted: Wed Apr 19, 2023 3:20 pm
by kknm
Found a much easier way -SELECT EXTRACT (HOUR FROM field_datetime)
Re: Get an hourly sample.
Posted: Wed Apr 19, 2023 7:37 pm
by kev1n
Sure, if you don't want to show all hours, even those without records, you can use the HOUR() function.