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 |
+-----+----------+-----------+
*/
Select hour( timediff( out_time, in_time)-if( date( in_time)+interval 12 hour between in_time and out_time), interval 1 hour,0) ) AS onhour from your_table ....
먼저 생각해봐야할것은
시간의 누적이 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