I have this table:
--------------------------------------------------------
|Date |type| |Value|
--------------------------------------------------------
|09/05/2022 00:03:04| |Water| |8.3|
--------------------------------------------------------
|09/05/2022 01:02:04| |Water| |3.3|
--------------------------------------------------------
|09/05/2022 02:13:04| |Water| |1.3|
--------------------------------------------------------
|09/05/2022 03:33:04| |Water| |3.3|
--------------------------------------------------------
|09/05/2022 04:43:04| |Water| |5.3|
--------------------------------------------------------
|09/05/2022 05:53:04| |Water| |6.3|
--------------------------------------------------------
|09/05/2022 06:22:04| |Water| |47.3|
--------------------------------------------------------
|09/05/2022 07:55:04| |Water| |48.3|
--------------------------------------------------------
|09/05/2022 08:03:14| |Water| |8.3|
--------------------------------------------------------
|09/05/2022 09:33:04| |Water| |9.3|
--------------------------------------------------------
I need to have:
--------------------------------------------------------
|Date |type| |Value|
--------------------------------------------------------
|09/05/2022 00:00:00| |Water| |8.3|
--------------------------------------------------------
|09/05/2022 01:00:00| |Water| |3.3|
--------------------------------------------------------
|09/05/2022 02:00:00| |Water| |1.3|
--------------------------------------------------------
|09/05/2022 03:00:00| |Water| |3.3|
--------------------------------------------------------
|09/05/2022 04:00:00| |Water| |5.3|
--------------------------------------------------------
|09/05/2022 05:00:00| |Water| |6.3|
--------------------------------------------------------
|09/05/2022 06:00:00| |Water| |47.3|
--------------------------------------------------------
|09/05/2022 07:00:00| |Water| |48.3|
--------------------------------------------------------
|09/05/2022 08:00:00| |Water| |8.3|
--------------------------------------------------------
|09/05/2022 09:00:00| |Water| |9.3|
--------------------------------------------------------
|09/05/2022 10:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 11:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 12:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 13:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 14:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 15:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 16:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 17:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 18:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 19:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 20:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 21:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 22:00:00| |Water| | |
--------------------------------------------------------
|09/05/2022 23:00:00| |Water| | |
--------------------------------------------------------
Is it possible?
I have some data in a day... So i Need to know from 0 am to 23 pm which data i got. And if a data Is between 2 hours i Need to have First data at beginning. For example, if i got data at 00:05:00 am i Need that data is stored at 00:00:00. So if i have many data from 00:00:01 to 00:59:59 , i Need only last One and show It at 00:00:00. So i Need last data for each hour in a day. How can i do with mysql SQL? Tks
CodePudding user response：
You can use date format :
SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H') as datee, type, value
from TABLE
group by datee, type, value
CodePudding user response：
Brief Explanation:
Recursive hours, this gets 0 to 23 hour rangeyourdatetime, this gets all unique dates fromyourtableincluding hours from 0 to 23output, basicallyyourtablewith a column added (row_number) used later in the where clause to include only last data appear in the same hour in a day
See db<>fiddle
WITH RECURSIVE hours AS (
SELECT 0 AS level
UNION ALL
SELECT level 1 AS value
FROM hours
WHERE hours.level <= 22
),
yourdatetime AS (
SELECT date, level
FROM (SELECT DISTINCT LEFT(date,10) AS date FROM yourtable) tmp
CROSS JOIN hours
),
output AS (
SELECT
type, value,
DATE_FORMAT(STR_TO_DATE(date, '%m/%d/%Y %H:%i:%s'), '%m/%d/%Y %H:00:00') AS date,
ROW_NUMBER() OVER(PARTITION BY DATE_FORMAT(STR_TO_DATE(date, '%m/%d/%Y %H:%i:%s'), '%m/%d/%Y %H:00:00') ORDER BY date DESC) AS rn
FROM yourtable
)
SELECT
DATE_FORMAT(STR_TO_DATE(CONCAT(t1.date, level), '%m/%d/%Y%k'), '%m/%d/%Y %H:00:00') AS date,
COALESCE(t2.type, 'Water') AS type,
t2.value
FROM yourdatetime t1
LEFT JOIN output t2 ON DATE_FORMAT(STR_TO_DATE(CONCAT(t1.date, level), '%m/%d/%Y%k'), '%m/%d/%Y %H:00:00') = t2.date
WHERE rn = 1 OR rn IS NULL
ORDER BY DATE_FORMAT(STR_TO_DATE(CONCAT(t1.date, level), '%m/%d/%Y%k'), '%m/%d/%Y %H:00:00')