꿈꾸는 개발자, DBA 커뮤니티 구루비

DATETIME 의 지정한 값 가져오기

by 도리비 [SQL Query] [2020.03.13 16:21:19]

2020-03-09 08:00:00 100

2020-03-09 09:00:00 100...

2020-03-10 08:00:00 150 ...

2020-03-11 08:00:00 120 ..

이런식으로 들어오는 값이 있습니다... 최근 5일의 DATE마다 합계를 가져오게 하고 싶습니다.

예를들어 03월 09일의 값은 09일 08시 부터 10일 08시 까지의 합산한 값입니다.

3월10일의 값은 3월 10일 08시~3월11일 08시 까지의 값입니다.

by 마농 [2020.03.13 16:37:15]

DBMS 종류와 컬럼 Data Type 에 따라 구문이 달라집니다.
구현 방식은 날짜에서 8시간을 차감한뒤 일자만 추출하여 그룹핑하면 됩니다.
최근 5일이면? 어제까지 인가요? 오늘도 포함인가요?

by 도리비 [2020.03.13 17:24:14]

MariaDB이며 DateTime , Date 모두 있습니다.

오늘도 포함합니다.

by 도리비 [2020.03.13 17:51:18]

SELECT
REPLACE('PL_AU_Water_Con*300','PL_AU_Water_Con*300','Sample') tagname,tagtype,identifier, DAYOFWEEK,logdatetime,logdate, SUM(logvalue)
FROM tbl_history_hour
WHERE tagname= 'Sample AND logdatetime >= '2020-03-09 08:00:00' AND logdatetime <= '2020-03-10 08:00:00' AND tagtype = 'H'

이렇게 했을 때 나온 결과값으로 아래처럼 나오듯이 최근 5일 (오늘 포함)하여 나와야합니다...

"Sample" "H" "2020030908" "월요일" "2020-03-09 08:00:00" "2020-03-09" "498"

by 도리비 [2020.03.13 17:46:46]

SELECT tagname,tagtype,identifier,DAYOFWEEK,logdatetime,logdate,logvalue
FROM hour
WHERE tagname= 'Sample' AND logdatetime BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 DAY),'%Y-%m-%d 08:00') AND DATE_FORMAT(CURDATE(),'%Y-%m-%d 08:00') AND tagtype='H'

이렇게 해서 나온 결과 입니다.

Tag , 시간식별 , 날짜+시간, 요일, DateTime, Date, Value

"Sample"   "H"   "2020030908"   "월요일"   "2020-03-09 08:00:00"   "2020-03-09"   "5"
"Sample"   "H"   "2020030909"   "월요일"   "2020-03-09 09:00:00"   "2020-03-09"   "14"
"Sample"   "H"   "2020030910"   "월요일"   "2020-03-09 10:00:00"   "2020-03-09"   "32"
"Sample"   "H"   "2020030911"   "월요일"   "2020-03-09 11:00:00"   "2020-03-09"   "26"
"Sample"   "H"   "2020030912"   "월요일"   "2020-03-09 12:00:00"   "2020-03-09"   "33"
"Sample"   "H"   "2020030913"   "월요일"   "2020-03-09 13:00:00"   "2020-03-09"   "23"
"Sample"   "H"   "2020030914"   "월요일"   "2020-03-09 14:00:00"   "2020-03-09"   "24"
"Sample"   "H"   "2020030915"   "월요일"   "2020-03-09 15:00:00"   "2020-03-09"   "24"
"Sample"   "H"   "2020030916"   "월요일"   "2020-03-09 16:00:00"   "2020-03-09"   "28"
"Sample"   "H"   "2020030917"   "월요일"   "2020-03-09 17:00:00"   "2020-03-09"   "20"
"Sample"   "H"   "2020030918"   "월요일"   "2020-03-09 18:00:00"   "2020-03-09"   "27"
"Sample"   "H"   "2020030919"   "월요일"   "2020-03-09 19:00:00"   "2020-03-09"   "15"
"Sample"   "H"   "2020030920"   "월요일"   "2020-03-09 20:00:00"   "2020-03-09"   "18"
"Sample"   "H"   "2020030921"   "월요일"   "2020-03-09 21:00:00"   "2020-03-09"   "27"
"Sample"   "H"   "2020030922"   "월요일"   "2020-03-09 22:00:00"   "2020-03-09"   "19"
"Sample"   "H"   "2020030923"   "월요일"   "2020-03-09 23:00:00"   "2020-03-09"   "10"
"Sample"   "H"   "2020030924"   "월요일"   "2020-03-10 00:00:00"   "2020-03-09"   "22"
"Sample"   "H"   "2020031001"   "화요일"   "2020-03-10 01:00:00"   "2020-03-10"   "21"
"Sample"   "H"   "2020031002"   "화요일"   "2020-03-10 02:00:00"   "2020-03-10"   "20"
"Sample"   "H"   "2020031003"   "화요일"   "2020-03-10 03:00:00"   "2020-03-10"   "22"
"Sample"   "H"   "2020031004"   "화요일"   "2020-03-10 04:00:00"   "2020-03-10"   "15"
"Sample"   "H"   "2020031005"   "화요일"   "2020-03-10 05:00:00"   "2020-03-10"   "12"
"Sample"   "H"   "2020031006"   "화요일"   "2020-03-10 06:00:00"   "2020-03-10"   "12"
"Sample"   "H"   "2020031007"   "화요일"   "2020-03-10 07:00:00"   "2020-03-10"   "6"
"Sample"   "H"   "2020031008"   "화요일"   "2020-03-10 08:00:00"   "2020-03-10"   "23"
"Sample"   "H"   "2020031009"   "화요일"   "2020-03-10 09:00:00"   "2020-03-10"   "27"
"Sample"   "H"   "2020031010"   "화요일"   "2020-03-10 10:00:00"   "2020-03-10"   "27"
"Sample"   "H"   "2020031011"   "화요일"   "2020-03-10 11:00:00"   "2020-03-10"   "22"
"Sample"   "H"   "2020031012"   "화요일"   "2020-03-10 12:00:00"   "2020-03-10"   "27"
"Sample"   "H"   "2020031013"   "화요일"   "2020-03-10 13:00:00"   "2020-03-10"   "16"
"Sample"   "H"   "2020031014"   "화요일"   "2020-03-10 14:00:00"   "2020-03-10"   "21"
"Sample"   "H"   "2020031015"   "화요일"   "2020-03-10 15:00:00"   "2020-03-10"   "29"
"Sample"   "H"   "2020031016"   "화요일"   "2020-03-10 16:00:00"   "2020-03-10"   "24"
"Sample"   "H"   "2020031017"   "화요일"   "2020-03-10 17:00:00"   "2020-03-10"   "36"
"Sample"   "H"   "2020031018"   "화요일"   "2020-03-10 18:00:00"   "2020-03-10"   "32"
"Sample"   "H"   "2020031019"   "화요일"   "2020-03-10 19:00:00"   "2020-03-10"   "27"
"Sample"   "H"   "2020031020"   "화요일"   "2020-03-10 20:00:00"   "2020-03-10"   "25"
"Sample"   "H"   "2020031021"   "화요일"   "2020-03-10 21:00:00"   "2020-03-10"   "34"
"Sample"   "H"   "2020031022"   "화요일"   "2020-03-10 22:00:00"   "2020-03-10"   "20"
"Sample"   "H"   "2020031023"   "화요일"   "2020-03-10 23:00:00"   "2020-03-10"   "11"
"Sample"   "H"   "2020031024"   "화요일"   "2020-03-11 00:00:00"   "2020-03-10"   "19"
"Sample"   "H"   "2020031101"   "수요일"   "2020-03-11 01:00:00"   "2020-03-11"   "15"
"Sample"   "H"   "2020031102"   "수요일"   "2020-03-11 02:00:00"   "2020-03-11"   "18"
"Sample"   "H"   "2020031103"   "수요일"   "2020-03-11 03:00:00"   "2020-03-11"   "15"
"Sample"   "H"   "2020031104"   "수요일"   "2020-03-11 04:00:00"   "2020-03-11"   "13"
"Sample"   "H"   "2020031105"   "수요일"   "2020-03-11 05:00:00"   "2020-03-11"   "23"
"Sample"   "H"   "2020031106"   "수요일"   "2020-03-11 06:00:00"   "2020-03-11"   "9"
"Sample"   "H"   "2020031107"   "수요일"   "2020-03-11 07:00:00"   "2020-03-11"   "16"
"Sample"   "H"   "2020031108"   "수요일"   "2020-03-11 08:00:00"   "2020-03-11"   "25"
"Sample"   "H"   "2020031109"   "수요일"   "2020-03-11 09:00:00"   "2020-03-11"   "27"
"Sample"   "H"   "2020031110"   "수요일"   "2020-03-11 10:00:00"   "2020-03-11"   "25"
"Sample"   "H"   "2020031111"   "수요일"   "2020-03-11 11:00:00"   "2020-03-11"   "22"
"Sample"   "H"   "2020031112"   "수요일"   "2020-03-11 12:00:00"   "2020-03-11"   "21"
"Sample"   "H"   "2020031113"   "수요일"   "2020-03-11 13:00:00"   "2020-03-11"   "16"
"Sample"   "H"   "2020031114"   "수요일"   "2020-03-11 14:00:00"   "2020-03-11"   "26"
"Sample"   "H"   "2020031115"   "수요일"   "2020-03-11 15:00:00"   "2020-03-11"   "18"
"Sample"   "H"   "2020031116"   "수요일"   "2020-03-11 16:00:00"   "2020-03-11"   "22"
"Sample"   "H"   "2020031117"   "수요일"   "2020-03-11 17:00:00"   "2020-03-11"   "33"
"Sample"   "H"   "2020031118"   "수요일"   "2020-03-11 18:00:00"   "2020-03-11"   "24"
"Sample"   "H"   "2020031119"   "수요일"   "2020-03-11 19:00:00"   "2020-03-11"   "19"
"Sample"   "H"   "2020031120"   "수요일"   "2020-03-11 20:00:00"   "2020-03-11"   "23"
"Sample"   "H"   "2020031121"   "수요일"   "2020-03-11 21:00:00"   "2020-03-11"   "20"
"Sample"   "H"   "2020031122"   "수요일"   "2020-03-11 22:00:00"   "2020-03-11"   "18"
"Sample"   "H"   "2020031123"   "수요일"   "2020-03-11 23:00:00"   "2020-03-11"   "20"
"Sample"   "H"   "2020031124"   "수요일"   "2020-03-12 00:00:00"   "2020-03-11"   "16"
"Sample"   "H"   "2020031201"   "목요일"   "2020-03-12 01:00:00"   "2020-03-12"   "14"
"Sample"   "H"   "2020031202"   "목요일"   "2020-03-12 02:00:00"   "2020-03-12"   "17"
"Sample"   "H"   "2020031203"   "목요일"   "2020-03-12 03:00:00"   "2020-03-12"   "15"
"Sample"   "H"   "2020031204"   "목요일"   "2020-03-12 04:00:00"   "2020-03-12"   "20"
"Sample"   "H"   "2020031205"   "목요일"   "2020-03-12 05:00:00"   "2020-03-12"   "13"
"Sample"   "H"   "2020031206"   "목요일"   "2020-03-12 06:00:00"   "2020-03-12"   "15"
"Sample"   "H"   "2020031207"   "목요일"   "2020-03-12 07:00:00"   "2020-03-12"   "18"
"Sample"   "H"   "2020031208"   "목요일"   "2020-03-12 08:00:00"   "2020-03-12"   "19"
"Sample"   "H"   "2020031209"   "목요일"   "2020-03-12 09:00:00"   "2020-03-12"   "20"
"Sample"   "H"   "2020031210"   "목요일"   "2020-03-12 10:00:00"   "2020-03-12"   "15"
"Sample"   "H"   "2020031211"   "목요일"   "2020-03-12 11:00:00"   "2020-03-12"   "28"
"Sample"   "H"   "2020031212"   "목요일"   "2020-03-12 12:00:00"   "2020-03-12"   "33"
"Sample"   "H"   "2020031213"   "목요일"   "2020-03-12 13:00:00"   "2020-03-12"   "23"
"Sample"   "H"   "2020031214"   "목요일"   "2020-03-12 14:00:00"   "2020-03-12"   "39"
"Sample"   "H"   "2020031215"   "목요일"   "2020-03-12 15:00:00"   "2020-03-12"   "25"
"Sample"   "H"   "2020031216"   "목요일"   "2020-03-12 16:00:00"   "2020-03-12"   "26"
"Sample"   "H"   "2020031217"   "목요일"   "2020-03-12 17:00:00"   "2020-03-12"   "25"
"Sample"   "H"   "2020031218"   "목요일"   "2020-03-12 18:00:00"   "2020-03-12"   "30"
"Sample"   "H"   "2020031219"   "목요일"   "2020-03-12 19:00:00"   "2020-03-12"   "19"
"Sample"   "H"   "2020031220"   "목요일"   "2020-03-12 20:00:00"   "2020-03-12"   "20"
"Sample"   "H"   "2020031221"   "목요일"   "2020-03-12 21:00:00"   "2020-03-12"   "22"
"Sample"   "H"   "2020031222"   "목요일"   "2020-03-12 22:00:00"   "2020-03-12"   "30"
"Sample"   "H"   "2020031223"   "목요일"   "2020-03-12 23:00:00"   "2020-03-12"   "19"
"Sample"   "H"   "2020031224"   "목요일"   "2020-03-13 00:00:00"   "2020-03-12"   "17"
"Sample"   "H"   "2020031301"   "금요일"   "2020-03-13 01:00:00"   "2020-03-13"   "18"
"Sample"   "H"   "2020031302"   "금요일"   "2020-03-13 02:00:00"   "2020-03-13"   "19"
"Sample"   "H"   "2020031303"   "금요일"   "2020-03-13 03:00:00"   "2020-03-13"   "13"
"Sample"   "H"   "2020031304"   "금요일"   "2020-03-13 04:00:00"   "2020-03-13"   "20"
"Sample"   "H"   "2020031305"   "금요일"   "2020-03-13 05:00:00"   "2020-03-13"   "10"
"Sample"   "H"   "2020031306"   "금요일"   "2020-03-13 06:00:00"   "2020-03-13"   "8"
"Sample"   "H"   "2020031307"   "금요일"   "2020-03-13 07:00:00"   "2020-03-13"   "12"
"Sample"   "H"   "2020031308"   "금요일"   "2020-03-13 08:00:00"   "2020-03-13"   "21"

by 마농 [2020.03.16 14:09:13]

WITH tbl_history_hour AS
(
SELECT 'Sample' tagname, 'H' tagtype, '2020-03-14 07:00:00' logdatetime, 100 logvalue
UNION ALL SELECT 'Sample', 'H', '2020-03-14 08:00:00', 100
UNION ALL SELECT 'Sample', 'H', '2020-03-14 09:00:00', 100
UNION ALL SELECT 'Sample', 'H', '2020-03-15 08:00:00', 150
UNION ALL SELECT 'Sample', 'H', '2020-03-16 08:00:00', 120
)
SELECT tagname
     , tagtype
     , DATE(logdatetime - INTERVAL 8 HOUR) dt
     , SUM(logvalue) logvalue
  FROM tbl_history_hour
 WHERE tagname = 'Sample'
   AND tagtype = 'H'
   AND logdatetime >= CURDATE() + INTERVAL 8 HOUR - INTERVAL 4 DAY
   AND logdatetime <  CURDATE() + INTERVAL 8 HOUR
 GROUP BY tagname, tagtype
     , DATE(logdatetime - INTERVAL 8 HOUR)
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변