mysql 시간 계산

mysql 시간 계산

QA

mysql 시간 계산

본문

입장 퇴장 버튼이 있어서

수시로 입장과 퇴장을 하는데 그때마다 누적시간을 한시간 단위로 나눠 평점을 주거든요

 

예를 들어 9시에 입장 하고 10시에 퇴장하면 평점 1점 이런식으로요..

 

그런데 점심 시간 12시부터 13시까지는 평점에서 제외해야하거든요.

 

이럴때 mysql에서 sql을 어떤식으로 작성해야할까요..? 

 

이게 아예 9시에 입장하고 15시에 퇴장하면 일괄적으로 한시간을 제외하면되는데..

 

입장 퇴장을 수시로 하고 할때마다 평점을 보여주거든요..

 

예를들어 9시 30분에 입장하고 12시 30분에 퇴장하면  점심시간인 30분은 제외해야 평점이 2점이되는데 이걸 어떻게 sql로 만들어야할지 모르겠어요 ㅠ ㅠ 

이 질문에 댓글 쓰기 :

답변 3

- DDL


CREATE TABLE t1(
    id varchar(50) not null,
    time_in timestamp not null default current_timestamp,
    time_out timestamp not null default current_timestamp,
    key(id)
);

 

- DML


INSERT INTO t1 VALUES 
 ('aaa', '2023-01-01 09:00:00', '2023-01-01 09:50:00')
,('aaa', '2023-01-01 10:00:00', '2023-01-01 11:00:00')
,('aaa', '2023-01-01 13:00:00', '2023-01-01 15:00:00')
,('bbb', '2023-01-01 11:00:00', '2023-01-01 12:00:00')
,('bbb', '2023-01-01 12:00:01', '2023-01-01 17:00:00')
,('ccc', '2023-01-01 11:00:00', '2023-01-01 11:30:00')
,('ccc', '2023-01-01 11:50:00', '2023-01-01 12:20:00')
,('ccc', '2023-01-01 12:48:00', '2023-01-01 12:49:00')
,('ccc', '2023-01-01 12:58:00', '2023-01-01 13:28:00')
,('ddd', '2023-01-01 09:00:00', '2023-01-01 18:00:00')
;

 

- SELECT


SELECT 
     id 
    ,SUM(t_sec) calc_sec 
    ,FLOOR(SUM(t_sec) / 3600) calc_hour 
FROM 
    (
    SELECT 
         id 
        ,time_in 
        ,time_out 
        ,(CASE 
            -- start ~ finish 내부
            --         [12:00:00] start ~ finish [13:00:00]
            WHEN 43200 <= UNIX_TIMESTAMP(time_in) % 86400 AND UNIX_TIMESTAMP(time_out) % 86400 <= 46800 THEN
                0
            -- start ~ finish 포함
            -- start ~ [12:00:00]                [13:00:00] ~ finish
            WHEN 43200 > UNIX_TIMESTAMP(time_in) % 86400 AND UNIX_TIMESTAMP(time_out) % 86400 > 46800 THEN
                (UNIX_TIMESTAMP(time_out) % 86400) - (UNIX_TIMESTAMP(time_in) % 86400) - 3600
            -- finish 걸침
            -- start ~ [12:00:00]       ~ finish [13:00:00]
            WHEN (UNIX_TIMESTAMP(time_out) % 86400 BETWEEN 43200 AND 46800) AND UNIX_TIMESTAMP(time_in) % 86400 < 43200 THEN
                43200 - (UNIX_TIMESTAMP(time_in) % 86400)
            -- start 걸침
            --         [12:00:00] start ~        [13:00:00] ~ finish
            WHEN (UNIX_TIMESTAMP(time_in) % 86400 BETWEEN 43200 AND 46800) AND UNIX_TIMESTAMP(time_out) % 86400 > 46800 THEN
                (UNIX_TIMESTAMP(time_out) % 86400) - 46800
            -- 해당없음
            ELSE 
                (UNIX_TIMESTAMP(time_out) % 86400) - (UNIX_TIMESTAMP(time_in) % 86400)
        END) t_sec
    FROM t1
    ) tmp 
GROUP BY id
;

 

- result


/*
inner set
+-----+---------------------+---------------------+-------+
| id  | time_in             | time_out            | t_sec |
+-----+---------------------+---------------------+-------+
| aaa | 2023-01-01 09:00:00 | 2023-01-01 09:50:00 |  3000 |
| aaa | 2023-01-01 10:00:00 | 2023-01-01 11:00:00 |  3600 |
| aaa | 2023-01-01 13:00:00 | 2023-01-01 15:00:00 |  7200 |
| bbb | 2023-01-01 11:00:00 | 2023-01-01 12:00:00 |  3600 |
| bbb | 2023-01-01 12:00:01 | 2023-01-01 17:00:00 | 14400 |
| ccc | 2023-01-01 11:00:00 | 2023-01-01 11:30:00 |  1800 |
| ccc | 2023-01-01 11:50:00 | 2023-01-01 12:20:00 |   600 |
| ccc | 2023-01-01 12:48:00 | 2023-01-01 12:49:00 |     0 |
| ccc | 2023-01-01 12:58:00 | 2023-01-01 13:28:00 |  1680 |
| ddd | 2023-01-01 09:00:00 | 2023-01-01 18:00:00 | 28800 |
+-----+---------------------+---------------------+-------+
 
group set
+-----+----------+-----------+
| id  | calc_sec | calc_hour |
+-----+----------+-----------+
| aaa |    13800 |         3 |
| bbb |    18000 |         5 |
| ccc |     4080 |         1 |
| ddd |    28800 |         8 |
+-----+----------+-----------+
*/

먼저 생각해봐야할것은

 

시간의 누적이 1시간단위당 1점이라고 하셨고

두번째 누적할 시간에 대해 12:00~13:00 은제외 라고 하셨는데

시간구간이 12:01~12:59 사이에 입장/퇴장이 겹칠때는 어떻게 해야 할지 고민을 해보셔야 할것같습니다.

 

그럼에도

방법을 고안해보면

두개의 쿼리를 구성합니다.

(1) A사용자의 입장~퇴장의 시간의 합 

(2) A사용자의 입장~퇴장중 12시~13시 까지의 시간의합

그후에

(1) - (2) 를 해준후 다시금 시간단위로 환산후 /10으로 나누고 int형으로 변환 하면 누적점수가됩니다.

 

위 방식은 다루기 쉬운형태로하세요

각가 쿼리로 값을 받은후 계산해도 되고

1개의 쿼리로 서브쿼리로 구성후 결과만 반환해도 되고 사용하기편한 용도대로 

구성하시면 될것같네요

 

참고자료

http://itlift.blogspot.com/2015/09/mysql.html

 

답변을 작성하시기 전에 로그인 해주세요.
전체 1,543
QA 내용 검색

회원로그인

(주)에스아이알소프트 / 대표:홍석명 / (06211) 서울특별시 강남구 역삼동 707-34 한신인터밸리24 서관 1404호 / E-Mail: admin@sir.kr
사업자등록번호: 217-81-36347 / 통신판매업신고번호:2014-서울강남-02098호 / 개인정보보호책임자:김민섭(minsup@sir.kr)
© SIRSOFT