SQL에서 날짜와 시간 다루는 방법

소개

관계형 데이터베이스와 SQL(Structured Query Language)을 다룰 때, 특정 날짜나 시간을 나타내는 값을 다루어야 할 때가 있습니다. 예를 들어, 특정 활동에 소요된 총 시간을 계산해야 할 수도 있고, 혹은 날짜나 시간 값을 수학 연산자와 집계 함수를 사용하여 합이나 평균을 계산해야 할 수도 있습니다.

이 자습서에서는 SQL에서 날짜와 시간을 사용하는 방법을 배우게 될 것입니다. 먼저 SELECT 문만을 사용하여 날짜와 시간에 대한 산술 연산을 수행하고 다양한 함수를 사용하는 방법을 익힐 것입니다. 그런 다음 샘플 데이터에 대한 쿼리를 실행하고 출력을 더 읽기 쉽게 만들기 위해 CAST 함수를 구현하는 방법을 배울 것입니다.

필수 조건

이 자습서를 완료하려면 다음이 필요합니다:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• 서버에 MySQL이 설치되어 있고 보안 설정이 되어 있어야 합니다. 이를 위해 우분투 20.04에 MySQL 설치하기 가이드를 따르세요. 이 가이드는 이 가이드의 3단계에서 설명한 대로 루트가 아닌 MySQL 사용자도 설정했다고 가정합니다.

이 튜토리얼에서 날짜 및 시간을 사용하기 위해 샘플 데이터가로 로드된 데이터베이스와 테이블이 필요합니다. 삽입할 준비가 된 데이터베이스가 없는 경우 다음 MySQL에 연결하고 샘플 데이터베이스 설정 섹션을 읽어 데이터베이스와 테이블을 만드는 방법을 배울 수 있습니다. 이 튜토리얼에서는 이 샘플 데이터베이스와 테이블을 계속 참조할 것입니다.

MySQL에 연결하고 샘플 데이터베이스 설정

SQL 데이터베이스가 원격 서버에서 실행되는 경우 로컬 머신에서 서버에 SSH로 연결하십시오:

ssh sammy@your_server_ip

다음으로 MySQL 프롬프트를 열어 sammy를 귀하의 MySQL 사용자 계정 정보로 대체하십시오:

mysql -u sammy -p

datetimeDB라는 데이터베이스를 만듭니다:

CREATE DATABASE datetimeDB;

데이터베이스가 성공적으로 생성된 경우 다음 출력을 받게 됩니다:

Output
Query OK, 1 row affected (0.01 sec)

다음 USE 문을 실행하여 datetimeDB 데이터베이스를 선택합니다:

USE datetimeDB;

Output
Database changed

데이터베이스를 선택한 후에는 해당 내부에 테이블을 생성합니다. 이 튜토리얼 예제에서는 1년 동안 달린 다양한 경주의 두 명의 러너 결과를 보유하는 테이블을 생성합니다. 이 테이블은 다음 일곱 개의 열을 보유합니다:

• race_id: int 데이터 유형의 값을 표시하며 테이블의 기본 키로 작동하여이 열의 각 값이 해당하는 행의 고유 식별자로 작동합니다.
• runner_name: 두 명의 러너인 볼트와 펠릭스의 이름을 위해 최대 30자의 varchar 데이터 유형을 사용합니다.
• race_name: 최대 20자의 varchar 데이터 유형으로 경주 유형을 보유합니다.
• start_day: 특정 경주의 날짜를 연도, 월, 일 단위로 추적하기 위해 DATE 데이터 유형을 사용합니다. 이 데이터 유형은 다음 매개 변수를 준수합니다: 연도에는 네 자리 숫자, 월과 일에는 최대 두 자리 숫자(YYYY-MM-DD)가 있습니다.
• start_time: 시간, 분 및 초 단위로 경주 시작 시간을 나타냅니다. 이 데이터 유형은 24시간 형식으로, 예를 들어 3:00 pm의 경우 15:00과 같이 표시됩니다.
• total_miles: 각 레이스의 총 주행 마일리지를 나타내며, 많은 경우 레이스 당 총 마일 수가 정수가 아닙니다. 이 경우 decimal은 소수점 이하의 숫자 중 하나가 세 자리의 정밀도를 지정합니다.
• end_time: 레이스 종료 시간을 추적하기 위해 TIMESTAMP 데이터 유형을 사용합니다. 이 데이터 유형은 날짜와 시간을 하나의 문자열로 결합하며, 형식은 DATE와 TIME의 결합입니다: (YYYY-MM-DD HH:MM:SS).

CREATE TABLE 명령을 실행하여 테이블을 생성하십시오:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

다음으로 빈 테이블에 일부 샘플 데이터를 삽입하십시오:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

데이터를 삽입한 후에는 SQL에서 날짜 및 시간과 관련된 산술 및 함수를 연습할 준비가되었습니다.

날짜와 시간에 대한 산술 사용

SQL에서는 수학적 표현식을 사용하여 날짜 및 시간 값을 조작할 수 있습니다. 계산하려는 값과 수학 연산자가 필요합니다.

예를 들어, 특정 날짜에서 일정한 일 수 후의 날짜를 찾고자 한다고 가정해 보겠습니다. 다음 쿼리는 한 날짜 값(2022-10-05)을 가져와 거기에 17을 더하여 쿼리에서 지정된 날짜 이후 17일 후의 값을 반환합니다. 이 예제에서는 2022-10-05를 DATE 값으로 지정하여 DBMS가 이를 문자열이나 다른 데이터 유형으로 해석하지 않도록 합니다:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

이 출력에 따르면 2022-10-05의 17일 후는 2022-10-22이며, 2022년 10월 22일입니다.

다른 예로, 두 개의 다른 시간 사이의 총 시간을 계산하고자 한다고 가정해 보겠습니다. 이를 위해 두 시간을 서로 빼면 됩니다. 다음 쿼리에서 11:00은 첫 번째 시간 값이고 3:00은 두 번째 시간 값입니다. 여기서 두 시간이 TIME 값임을 지정하여 시간의 차이를 반환해야 합니다:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

이 출력에서 11:00과 3:00 사이의 차이는 80000이며, 8시간입니다.

이제 샘플 데이터에서 날짜 및 시간 정보에 대한 산술 연산을 연습해 보겠습니다. 첫 번째 쿼리에서는 각 경주의 완주에 소요된 총 시간을 계산하기 위해 end_time에서 start_time을 뺍니다:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

이 출력에서 total_time 열은 다소 길고 읽기 어려울 수 있습니다. 나중에, 이러한 데이터 값을 더 명확하게 읽을 수 있도록 변환하기 위해 CAST 함수를 사용하는 방법을 설명하겠습니다.

이제 각 러너의 장거리 레이스 성능에만 관심이 있다면, 반 마라톤 및 풀 마라톤과 같은 더 긴 레이스에 대한 각 러너의 성능을 쿼리할 수 있습니다. 이 쿼리를 위해 end_time에서 start_time을 뺀 다음, WHERE 절을 사용하여 total_miles가 12보다 큰 데이터를 검색합니다:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

이 섹션에서는 SELECT 문을 사용하여 날짜와 시간에 대해 일부 산술을 수행했으며, 실제 데이터에 대한 목적으로 수행되었습니다. 다음으로, 다양한 날짜 및 시간 함수를 사용하여 쿼리를 연습하게 될 것입니다.

날짜 및 시간 함수 및 간격 표현 사용하기

SQL에서는 날짜 및 시간 값을 찾고 조작하는 데 사용할 수 있는 여러 함수가 있습니다. SQL 함수는 일반적으로 데이터를 처리하거나 조작하는 데 사용되며, 사용 가능한 함수는 SQL 구현에 따라 다릅니다. 그러나 대부분의 SQL 구현에서는 current_date 및 current_time 값을 쿼리하여 현재 날짜와 시간을 찾을 수 있습니다.

예를 들어 오늘의 날짜를 찾으려면, 다음과 같이 SELECT 문과 current_date 함수만으로 구성된 구문을 사용합니다:

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

동일한 구문을 사용하여 current_time 함수로 현재 시간을 찾을 수 있습니다:

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

출력에서 날짜와 시간을 모두 쿼리하려면 current_timestamp 함수를 사용하십시오:

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

이전 섹션과 유사한 산술 함수 내에서 이와 같은 날짜 및 시간 함수를 사용할 수 있습니다. 예를 들어, 오늘 날짜로부터 11일 전의 날짜를 알고 싶다고 가정해 보겠습니다. 이 경우, 이전에 사용한 것과 동일한 구문 구조를 사용하여 current_date 함수를 쿼리하고 거기서 11을 빼서 열한 일 전의 날짜를 찾을 수 있습니다:

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

이 출력에서 나타나듯이 current_date (이 글을 쓸 때)로부터 11일 전은 2022-02-06 또는 2022년 2월 6일이었습니다. 이제 이와 같은 작업을 실행하는 것을 시도해 보세요. 단, current_date를 current_time 함수로 바꿉니다:

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

이 출력에서 current_time 값에서 11을 빼면 11초가 빠집니다. 이전에 실행한 작업은 current_date 함수를 사용하여 11을 일 수로 해석했습니다. 날짜 및 시간 함수를 사용할 때 숫자가 어떻게 해석되는지에 대한 이러한 일관성 부족은 혼란스러울 수 있습니다. 이렇게 날짜 및 시간 값을 이와 같은 산술로 조작하도록 요구하는 대신 많은 데이터베이스 관리 시스템에서는 INTERVAL 표현식의 사용을 통해 더 명시적으로 할 수 있습니다.

INTERVAL 표현식을 사용하면 주어진 날짜 또는 시간 표현식으로부터 설정된 간격 전후의 날짜 또는 시간을 찾을 수 있습니다. 다음 형식을 취해야 합니다:

INTERVAL value unit

예를 들어, 지금으로부터 다섯 일 후의 날짜를 찾으려면 다음 쿼리를 실행할 수 있습니다:

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

이 예제는 current_date 값을 찾은 다음에 그것에 간격 표현식 INTERVAL '5' DAY을 추가합니다. 이렇게 하면 현재로부터 5일 후의 날짜가 반환됩니다:

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

이 쿼리는 유사하지만 완전히 동일한 출력을 생성하지는 않는 다음 쿼리보다 모호합니다:

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

지정된 날짜 값보다 이전의 값들을 찾으려면 날짜나 시간에서 간격을 빼는 것도 가능합니다:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

INTERVAL 표현식에서 사용할 수 있는 단위는 사용하는 DBMS의 선택에 따라 다르지만, 대부분은 HOUR, MINUTE, SECOND 등의 옵션이 있습니다:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

이제 간격 표현식과 몇 가지 날짜 및 시간 함수에 대해 배웠으니, 첫 번째 단계에서 삽입한 샘플 데이터를 사용하여 연습을 계속해 보세요.

날짜 및 시간과 함께 CAST 및 집계 함수 사용하기

날짜 및 시간과 산술 사용하기 섹션의 세 번째 예제에서 end_time에서 start_time을 빼서 각 러너가 경주 당 완주한 총 시간을 계산하는 쿼리를 실행했던 것을 기억하십시오. 그러나 출력은 테이블에서 설정한 TIMESTAMP 데이터 유형을 따르는 매우 긴 출력이 포함된 열을 생성했습니다:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

end_time 열은 TIMESTAMP 값을, start_time 열은 TIME 값을 보유하고 있으므로 두 열 간의 작업을 수행하는 경우 데이터베이스는 결과를 출력할 때 사용할 데이터 유형을 알지 못합니다. 대신, 연산을 수행할 수 있도록 두 값을 모두 정수로 변환하고 total_time 열에 긴 숫자를 생성합니다.

데이터를 더 읽기 쉽고 해석하기 쉽도록 만들기 위해 CAST 함수를 사용하여 이러한 긴 정수 값을 TIME 데이터 유형으로 변환할 수 있습니다. 이를 위해 CAST로 시작한 다음 즉시 여는 괄호, 변환하려는 값을, 그런 다음 AS 키워드와 변환하려는 데이터 유형을 따릅니다.

다음 쿼리는 이전 예제와 동일하지만 CAST 함수를 사용하여 total_time 열을 time 데이터 유형으로 변환합니다:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST는 이 출력에서 데이터 값을 TIME으로 변환하여 읽고 이해하기 훨씬 쉽게 만들었습니다.

이제 몇 가지 집계 함수를 CAST 함수와 결합하여 각 러너의 최단, 가장 긴 및 총 시간 결과를 찾아봅시다. 먼저, MIN 집계 함수를 사용하여 최소(또는 가장 짧은) 시간을 쿼리합니다. 다시 한 번, 명확성을 위해 TIMESTAMP 데이터 값을 TIME 데이터 값으로 변환하기 위해 CAST를 사용해야 합니다. 이 예제와 같이 두 개의 함수를 사용할 때는 두 쌍의 괄호가 필요하며 총 시간 계산 (end_time - start_time)은 그 중 하나의 괄호 안에 중첩되어야 합니다. 마지막으로 GROUP BY 절을 추가하여 runner_name 열을 기준으로 이러한 값을 구성하면 출력에서 두 러너의 경주 결과가 표시됩니다:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

이 출력에서 각 러너의 최단 달리기 시간이 표시됩니다. 이 경우 볼트는 6분 30초의 최소 시간이 걸렸으며, 펠릭스는 7분 15초의 최소 시간이 걸렸습니다.

다음으로 각 러너의 가장 긴 달리기 시간을 찾아보겠습니다. 이전 쿼리와 동일한 구문을 사용할 수 있지만, 이번에는 MIN을 MAX로 대체하면 됩니다:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

이 출력은 볼트의 가장 긴 달리기 시간이 총 3시간 23분 10초이며, 펠릭스는 총 4시간 2분 10초였음을 알려줍니다.

이제 각 러너가 러닝에 소요한 총 시간에 대한 고수준 정보를 쿼리해 보겠습니다. 이 쿼리에서는 end_time - start_time을 기반으로 한 시간의 총 합을 찾기 위해 SUM 집계 함수를 결합하고, 데이터 값을 TIME으로 변환하기 위해 CAST를 사용합니다. 또한 두 러너의 결과 값을 구성하기 위해 GROUP BY를 포함해야 합니다:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

흥미로운 점은 이 출력이 실제로 MySQL의 해석을 보여준다는 것인데, 이는 실제로 정수로 총 시간을 계산합니다. 이 결과를 시간으로 읽으면, 볼트의 총 시간은 다섯 시간, 28분, 80초로 나뉘며, 펠릭스의 시간은 일곱 시간, 61분, 49초로 나뉩니다. 이 시간 분해는 의미가 없다는 것을 알 수 있는데, 이는 정수로 계산되고 있고 시간이 아닌 것을 나타냅니다. PostgreSQL과 같은 다른 DBMS에서 이것을 시도하면, 동일한 쿼리가 약간 다르게 나타납니다:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

이 경우 PostgreSQL의 쿼리는 값을 시간으로 해석하고 그에 따라 계산하므로, 펠릭스의 결과가 총 10시간, 1분, 44초로 나뉘며, 볼트의 결과는 6시간, 9분, 20초로 나뉩니다. 이는 동일한 쿼리와 데이터 세트를 사용하더라도 다양한 DBMS 구현이 데이터 값을 서로 다르게 해석할 수 있는 예입니다.

결론

SQL에서 날짜와 시간을 사용하는 방법을 이해하는 것은 특정 결과를 쿼리할 때 유용합니다. 분, 초, 시간, 일, 월, 연도 또는 이 모든 것의 조합을 사용하여 쿼리할 수 있습니다. 또한 날짜와 시간에 대해 사용할 수 있는 많은 함수가 있어 특정 값을 찾는 데 도움이 됩니다. 이 튜토리얼에서는 SQL에서 날짜와 시간에 대해 덧셈과 뺄셈만 사용했지만, 수학 표현식과 함께 날짜와 시간 값을 사용할 수 있습니다. 수학 표현식 및 집계 함수에 대한 당사의 가이드에서 더 자세히 알아보고 날짜 및 시간 쿼리와 함께 시도해 보세요.