YS's develop story

MySQL ์‹œ๊ฐ„์ฐจ์ด ๊ณ„์‚ฐํ•˜๊ธฐ (TIMESTAMPDIFF ํ•จ์ˆ˜) ๋ณธ๋ฌธ

Database/MySQL

MySQL ์‹œ๊ฐ„์ฐจ์ด ๊ณ„์‚ฐํ•˜๊ธฐ (TIMESTAMPDIFF ํ•จ์ˆ˜)

Yusang 2021. 8. 23. 08:47

๐Ÿฅ MySQL ์‹œ๊ฐ„ ์ฐจ์ด ๊ณ„์‚ฐํ•˜๊ธฐ (TIMESTAMPDIFF ํ•จ์ˆ˜)

โŒ› Problem

์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•˜๋‹ค ๋ณด๋ฉด ์‹œ๊ฐ„ ์ฐจ์ด๋ฅผ ํ™œ์šฉํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ๊ฐ€ ์กด์žฌํ•ฉ๋‹ˆ๋‹ค.
์˜ˆ๋ฅผ ๋“ค์–ด ์˜ˆ์•ฝ์‹œ๊ฐ„ 30๋ถ„ ์ „์—๋Š” ์˜ˆ์•ฝ์„ ๋ถˆ๊ฐ€๋Šฅํ•˜๋„๋ก ๋ง‰์•„์•ผ ํ•œ๋‹ค๊ฑฐ๋‚˜
ํŠน์ • ์ด๋ฒคํŠธ์ผ์ด ํ˜„์žฌ๋ถ€ํ„ฐ ์–ผ๋งˆ๋‚˜ ๋‚จ์•˜๋Š”์ง€ ๋“ฑ๋“ฑ์˜ ๊ฒฝ์šฐ๊ฐ€ ์กด์žฌํ•  ๊ฒƒ์ž…๋‹ˆ๋‹ค.
์ด๋Ÿฐ ๊ฒฝ์šฐ์—๋Š” ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด์•ผ ํ• ๊นŒ์š”? ๐Ÿ˜• 

 

๐Ÿ“œ Solution

TIMESTAMPDIFF๋ผ๋Š” ํ•จ์ˆ˜๋ฅผ ํ™œ์šฉํ•˜๋ฉด ๋งค์šฐ ์‰ฝ๊ฒŒ ์‹œ๊ฐ„ ์ฐจ์ด๋ฅผ ๊ณ„์‚ฐํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

SELECT TIMESTAMPDIFF(HOUR,'2021-08-22 11:00','2021-08-22 14:00') AS dayCount;

SELECT TIMESTAMPDIFF(์‹œ๊ฐ„ ๋‹จ์œ„, '๋‚ ์งœ 1', '๋‚ ์งœ 2') AS ์ด๋ฆ„;

์œ„์™€ ๊ฐ™์€ ์ฟผ๋ฆฌ๋ฅผ ํ†ตํ•ด์„œ ๋‚ ์งœ 1๋ถ€ํ„ฐ ๋‚ ์งœ 2๊นŒ์ง€์˜

๋‚จ์€ ์‹œ๊ฐ„์„ ์ถœ๋ ฅํ•˜๋„๋ก ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์‹œ๊ฐ„ ๋‹จ์œ„ ์ž๋ฆฌ์—๋Š” DAY, HOUR, MINUTE ๋“ฑ์ด ๋“ค์–ด๊ฐˆ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

 

 

SELECT TIMESTAMPDIFF(HOUR,'2021-08-22 16:00','2021-08-22 14:00') AS dayCount;

์‹œ๊ฐ„์ด ์ด๋ฏธ ์ดˆ๊ณผ๋˜์—ˆ๋‹ค๋ฉด ์™ผ์ชฝ์ฒ˜๋Ÿผ ์Œ์ˆ˜ ๊ฐ’์ด ๋‚˜์˜ค๊ฒŒ ๋ฉ๋‹ˆ๋‹ค.

 

 

 

 

 

 

 

SELECT TIMESTAMPDIFF(HOUR,NOW(),?) AS dayCount;

์œ„์™€ ๊ฐ™์ด ์ฟผ๋ฆฌ๋ฅผ ์งœ์„œ ์‚ฌ์šฉ์ž๋กœ๋ถ€ํ„ฐ ์ž…๋ ฅ๋ฐ›์€ ์‹œ๊ฐ„์ด ํ˜„์žฌ ์‹œ๊ฐ„์œผ๋กœ๋ถ€ํ„ฐ ์–ผ๋งˆ๋‚˜ ๋‚จ์•˜๋Š”์ง€๋„

์ถœ๋ ฅํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

์ด๋ฅผ ์ด์šฉํ•˜๋ฉด ์‚ฌ์šฉ์ž๋กœ๋ถ€ํ„ฐ ์ž…๋ ฅ๋ฐ›์€ ์‹œ๊ฐ„์„ ๊ธฐ์ค€์œผ๋กœ ์ถœ๋ ฅ๋œ ๊ฐ’์ด ์Œ์ˆ˜์ด๋ฉด

์ด๋ฏธ ์ง€๋‚œ ์‹œ๊ฐ„์ด๋ฏ€๋กœ ๊ฐ’์„ ๋ฐ›์ง€ ์•Š๋„๋ก ์ฒ˜๋ฆฌ๋ฅผ ํ•˜์—ฌ ์˜ˆ์•ฝ ๊ด€๋ จ API์— ์ด๋ฏธ ์ง€๋‚œ ์‹œ๊ฐ„์„ ์˜ˆ์•ฝํ•  ์ˆ˜ ์—†๋„๋ก API๋ฅผ ์„ค๊ณ„ํ•  ์ˆ˜ ์žˆ์„ ๊ฒƒ์ž…๋‹ˆ๋‹ค.


์ด์ฒ˜๋Ÿผ TIMESTAMPDIFF ํ•จ์ˆ˜๋ฅผ ํ™œ์šฉํ•œ๋‹ค๋ฉด ์‹œ๊ฐ„๊ณผ ๊ด€๋ จ๋œ API๋ฅผ ์„ค๊ณ„ํ•  ๋•Œ ๋งค์šฐ ์œ ์šฉํ•  ๊ฒƒ ๊ฐ™๋„ค์š”! ๐Ÿค—

 

Comments