개발옹

데이터베이스에서 필수로 쓰이는 날짜형 데이터에 대해 정리해두고자 합니다.

4가지 유형의 데이터 타입에 대한 정리와 일자와 시간을 모두 표현하는 DATETIME과 TIMESTAMP의 차이를 알아보겠습니다.

1. DATE, DATETIME, TIMESTAMP, TIME

MySQL에서는 일자와 시간을 표현하기위해 DATE, DATETIME, TIMESTAMP, TIME 타입을 사용할 수 있습니다.

일자와 시간을 모두 표현하는 DATETIME과 TIMESTAMP는 얼핏봐서는 비슷한데, 차이점이 있습니다. 

이 둘의 차이를 알고 있어야 예상치 못한 에러사항을 대비할 수 있기 때문에, 차이점을 한 번 알아보겠습니다.

2. DATETIME vs TIMESTAMP

1) 범위

일자와 시간을 모두 표현하는 두 타입은 일단 표에서 볼 수 있듯 범위에서 차이가 있습니다.

DATETIME의 범위는 '1000-01-01 00:00:00' 부터 '9999-12-31 23:59:59' 로, 시간대에 관계없이 절대적인 일자와 시간을 저장합니다.

TIMESTAMP의 범위는 '1970-01-01 00:00:01' UTC 부터 '2038-01-19 03:14:07' UTC 로, 데이터가 UTC로 변환되어 저장되며 검색시 현재 시간대로 변환이 됩니다. TIMESTAMP의 범위는 Unix 시간과 관련이 있어, 2038년 문제로 인해 최대 범위가 2038년까지로 제한됩니다.

2) Timezone의 적용 여부

두 타입은 Timezone의 적용 여부에 차이가 있습니다.

Timezone은 동일한 시간을 따르는 지역을 의미하고, 해당 국가에 의해 법적으로 지정되는 값입니다.

UTC, Asia/Seoul 등의 표기가 해당 시간이 어떤 로컬을 기준으로 작성한 것인지를 명시하는 것입니다.

MySQL은 TIMESTAMP 값을 현재 시간대에서 *UTC로 변화하여 저장하고, 다시 UTC에서 현재 시간대로 변환하여 검색합니다.

*UTC: 국제 표준시

기본적으로 설정되는 시간대는 서버 시간으로, 시간대 설정이 일정하게 유지된다면 저장한 값과 동일한 값을 반환받습니다.

만약 TIMESTAMP 값을 저장한 후, 시간대를 변경하고 값을 조회하면, 조회 결과는 저장한 값과 다르게 됩니다. 시간대 변환시 동일한 시간대를 사용하지 않았기 때문에 이러한 결과가 발생하는 것입니다.

*현 시간대는 시스템 변수인 time_zone 에 설정된 값으로 확인할 수 있습니다.

예시

실제로 데이터가 어떻게 저장되는지 확인해봅시다.

create table date_example
(
  test_date date,
  test_datetime datetime,
  test_timestamp timestamp,
  test_time time
);

insert into date_example values (now(), now(), now(), now());

테스트 테이블을 만들고 현재 시간으로 데이터를 저장했습니다.

mysql> select * from date_example;
+------------+---------------------+---------------------+-----------+
| test_date  | test_datetime       | test_timestamp      | test_time |
+------------+---------------------+---------------------+-----------+
| 2024-07-09 | 2024-07-09 10:36:17 | 2024-07-09 10:36:17 | 10:36:17  |
+------------+---------------------+---------------------+-----------+
1 row in set (0.00 sec)

왼쪽부터 DATE, DATETIME, TIMESTAMP, TIME 형식의 데이터가 저장된 것을 확인할 수 있습니다.

여기서 time_zone을 수정해보고 다시 같은 데이터를 확인해보겠습니다.

mysql> SET time_zone = '+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from date_example;
+------------+---------------------+---------------------+-----------+
| test_date  | test_datetime       | test_timestamp      | test_time |
+------------+---------------------+---------------------+-----------+
| 2024-07-09 | 2024-07-09 10:36:17 | 2024-07-09 02:36:17 | 10:36:17  |
+------------+---------------------+---------------------+-----------+
1 row in set (0.00 sec)

time_zone에 변화를 주고 조회해보니 TIMESTAMP 형식의 test_timstamp 값만 변한 것을 알 수 있습니다.

• SET time_zone = '+01:00' : UTC+09:00 보다 8시간 이전으로 설정

이는 위에서 기재했듯 TIMESTAMP 는 시스템 변수인 time_zone을 기준으로 결과를 반환하기 때문입니다.

그렇기때문에 TIMESTAMP를 사용하고, 로컬 시간이 다른 글로벌 서비스를 운영할 때에는 이를 유의해서 관리해야합니다.

TIMESTAMP를 사용하면 로컬 시간에 따라 혼란이 있을 수는 있으나, 글로벌 서비스를 운영할때에는 유용하게 쓰일 수 있습니다.

만약 DATETIME을 사용한다면 로컬 시간이 제대로 반영되지 않아서, 서울에서 오전 11시에 작성한 글을 미국에서 조회하는 경우, 동일하게 오전 11시로 반영되는 등의 문제가 발생합니다. 이렇게 시간이 다른 지역이 있는 경우, TIMESTAMP가 더 적합할 수도 있습니다.

이러한 특징들로 DATETIME은 절대적인 시간 기록에 적합하고 TIMESTAMP는 시간대에 따라 변환이 필요한 경우 유용하게 쓰입니다.

3) DATETIME 과 TIMESTAMP의 자동 초기화

DATETIME와 TIMESTAMP는 현재 일자와 시간을 기본값, 자동 업데이트 값으로 설정할 수 있습니다.

• 자동 초기화: 데이터 삽입시 명시적으로 지정하지 않아도 현재 타임스탬프로 설정
• 자동 업데이트: 데이터 갱신시 자동으로 현재 타임 스탬프로 갱신

설정 방법

자동 초기화와 업데이트를 설정하려면 DATETIME과 TIMESTAMP 열 정의에서 지정해주면 됩니다.

• 자동 초기화를 위한 초기값 설정: DEFAULT ~~
• 자동 업데이트를 위한 설정: ON UPDATE ~~

DATETIME과 TIMESTAMP를 각각 형식으로 가지는 column이 있는 테이블을 아래와 같이 정의하면 자동으로 값이 들어갑니다.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

위 테이블에 아무 값도 지정하지 않고 INSERT를 하고 조회를 하면, 

insert into t1 values();

아무 값도 넣지 않았는데도 현재 시간이 저장된 것을 확인할 수 있습니다.

mysql> select * from t1;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2024-07-09 12:13:49 | 2024-07-09 12:13:49 |
+---------------------+---------------------+
1 row in set (0.00 sec)

이번엔 아래와 같은 테이블을 생성해봅시다.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

위에서 정의한 것과 다른점은 DEFAULT NULL 부분입니다.

이는 기본값을 NULL로 두겠다는 것을 의미합니다.

mysql> INSERT INTO t1 VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM T1;
+------+------+
| ts   | dt   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

위와 동일하게 아무 값도 넣지 않고 INSERT를 하면, 이번에는 NULL 값이 저장된 것을 확인할 수 있습니다.

마치며,

서비스를 운영하면 필수적으로 포함되는 날짜와 시간 데이터형에 대한 내용을 정리했습니다.

특히 DATETIME과 TIMESTAMP의 차이에서 헷갈리는 부분이 많았는데, 이번에 정리하고 찾아보면서 한번 더 확인할 수 있었던 시간이었습니다. 날짜 데이터와 관련해서는 다음에 문자열로 변환하는 등 실제로 값을 조회하고 처리하는 부분에 대해서 알아보겠습니다.

Reference

📌요약

윈도우 함수 중 하나인 순위 함수를 사용하면 결과 집합의 각 행에 순위를 할당할 수 있습니다.

1. RANK() 함수

[MySQL Tutorial]
RANK() 함수는 결과 집합의 파티션 내 각 행에 순위를 할당합니다.
행의 순위는 1에 그 앞에 오는 순위 수를 더한 값으로 지정됩니다.

[Oracle Tutorial]
RANK() 함수는 각 집합에서 값의 순위를 계산하는 분석 함수입니다.
RANK() 함수는 동일한 값을 가진 행에 대해 동일한 순위를 반환합니다. 동률 순위에 동률 행 수를 더해 다음 순위를 계산합니다. 따라서 순위는 연속된 숫자가 아닐 수도 있습니다.
RANK() 함수는 상위 N 및 하위 N 쿼리에 유용합니다.

RANK() 함수는 윈도우 함수 중 그룹 내 순위 함수로, 특정 윈도우(부분)내에서 순위를 할당하는 데 사용합니다.

동일한 값을 가진 행에 대해 동일한 순위를 부여하고 그 다음 순위는 동일 순위를 가진 행 수만큼의 시퀀스 간격이 생깁니다.

예를 들어 3위가 3명이라면, 1,2,3,3,3,6,7,8, ... 이렇게 순위가 매겨집니다.

RANK() 문법

MySQL과 Oracle의 RANK() 함수 문법은 각각 아래와 같습니다.

MySQL 

RANK() OVER (
	PARTITION BY <expression>[{,<expression>...}]
	ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

Oracle

RANK() OVER ([query_partition_clause] order_by_clause)

• PARTITION BY | query_partition_clause : 결과 집합을 파티션으로 나눕니다.
  • 옵션 값이며, 생략할 경우 조회 결과의 모든 행을 하나의 파티션으로 취급합니다.
• ORDER BY | order_by_clause : 하나 이상의 열이나 표현식을 기준으로 파티션 내의 행을 정렬합니다.
  • 필수 값이며, 함수가 적용되는 각 파티션의 행의 논리적인 정렬 순서를 결정합니다.

2. DENSE_RANK() 함수

[MySQL Tutorial]
DENSE_RANK() 함수는 순위 값에 차이 없이 파티션 또는 결과 집합 내의 각 행에 순위를 할당하는 윈도우 함수입니다.
행의 순위는 행 앞에 오는 고유한 순위 값에서 1씩 증가합니다.

[Oracle Tutorial]
DENSE_RANK() 함수는 정렬된 행 집합에서 행의 순위를 계산하는 분석 함수입니다.
RANK() 함수와 달리 DENSE_RANK() 함수는 순위 값을 연속된 정수로 할당합니다. 동률인 경우에는 순위를 건너뛰지 않습니다.
순위 기준 값이 동일한 행에는 동일한 순위 값이 적용됩니다. 

DENSE_RANK() 함수는 RANK() 함수와 동일하게 동일한 값을 가진 행에 대해 동일한 순위를 부여하지만 그 다음에 나오는 행에는 순위를 건너뛰지 않고 이어서 순위를 부여합니다.

예를 들어 3위가 3명이라면, 1,2,3,3,3,4,5,6, ... 이렇게 순위가 매겨집니다.

DENSE_RANK() 문법

MySQL과 Oracle의 DENSE_RANK() 함수 문법은 각각 아래와 같습니다.

MySQL 

DENSE_RANK() OVER (
    PARTITION BY partition_expression
    ORDER BY sort_expression [ASC|DESC]
)

Oracle

DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

PARTITION BY | query_partition_clause 과 ORDER BY | order_by_clause는 RANK() 함수에서의 설명과 같습니다.

DENSE_RANK()는 파티션에 동일한 순위 값을 가진 두 개 이상의 행이 있는 경우, 각 행에 동일 순위가 부여되고, RANK()함수와는 달리 항상 연속된 순위 값을 할당 합니다. (동일 순위가 있다하더라도)

3. ROW_NUMBER() 함수

[MySQL Tutorial]
ROW_NUMBER() 함수는 결과 집합의 각 행에 일련 번호를 할당하는 윈도우 함수 혹은 분석 함수입니다.
첫번째 숫자는 1로 시작합니다.

[Oracle Tutorial]
ROW_NUMBER() 함수는 적용되는 각 행(파티션의 각 행 또는 결과 집합의 각 행)에 순차적으로 고유한 정수를 할당하는 분석 함수입니다.

ROW_NUMBER() 함수는 결과 집합 내의 각 행에 고유 번호를 할당합니다.

RANK()나 DENSE_RANK() 함수보다 매우 간단합니다.

ROW_NUMBER() 함수는 각 행에 순위를 부여할 때 동일한 값이 있어도 이를 무시하고 연속적인 순위를 부여합니다.

예를 들어 3위가 3명이라해도, 1,2,3,4,5,6, ... 이렇게 순위가 매겨집니다.

ROW_NUMBER() 문법

MySQL과 Oracle의 RANK() 함수 문법은 각각 아래와 같습니다.

MySQL 

ROW_NUMBER() OVER (
    PARTITION BY <expression>,[{,<expression>}...]
    ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
)

Oracle

ROW_NUMBER() OVER (
   [query_partition_clause] 
   order_by_clause
)

PARTITION BY | query_partition_clause 과 ORDER BY | order_by_clause는 RANK() 함수에서의 설명과 같습니다.

4. RANK() vs DENSE_RANK() vs ROW_NUMBER()

지금까지 알아본 3가지 윈도우 함수인 RANK(), DENSE_RANK(), ROW_NUMBER()의 실제 결과값의 차이를 확인해보겠습니다.

학생 정보가 담긴 테이블을 간단하게 만들고, 학생별 평점으로 각 순위 함수의 차이를 확인해보겠습니다.

CREATE TABLE `students` (
  `student_id` int NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `major` varchar(100) DEFAULT NULL,
  `gpa` decimal(3,2) DEFAULT NULL, // 평점
  PRIMARY KEY (`student_id`)
)

적당한 데이터를 여러개 INSERT 한 후 아래 쿼리를 실행시켜 보겠습니다.

SELECT student_id , first_name , gpa 
		, RANK() OVER (ORDER BY gpa DESC) AS 'RANK'
		, DENSE_RANK () OVER (ORDER BY gpa DESC) AS 'DENSE_RANK'
		, ROW_NUMBER() OVER (ORDER BY gpa DESC, first_name ASC) AS 'ROW_NUM'
FROM students;

실행시킨 결과는 아래와 같습니다.

3,4번 행을 보면, 3번 행의 지우와 4번 행의 소민이의 순위 값이 각 함수별로 다른 것을 확인할 수 있습니다.

그리고 각 함수별로 순위도 다르게 매겨지는 것을 알 수 있습니다.

• RANK: 1 ⭢ 2 ⭢ 4 ⭢ 6 ⭢ 8 ⭢ 10
• DENSE_RANK: 1 ⭢ 2 ⭢ 3 ⭢ 4 ⭢ 5 ⭢ 6
• ROW_NUM: 1 ⭢ 2 ⭢ 3 ⭢ 4 ⭢ 5 ⭢ 6 ⭢ 7 ⭢ 8 ⭢ 9 ⭢ 10

5. PARTITION BY  문법

추가로 세가지 함수에서 모두 옵션 값으로 사용되는 파티션 구문에 대한 사용법을 알아보겠습니다.

PARTITION BY는 그룹 내 순위 및 그룹별 집계를 구할 때 사용합니다.

SELECT student_id , major , gpa 
	,RANK() OVER (PARTITION BY major ORDER BY gpa DESC) AS 'PARTITION'
FROM STUDENTS;

이 쿼리는 학생들의 전공(major)별로 학생들의 평점(gpa)순위를 매깁니다.

• PARTITION BY major ORDER BY gpa DESC: major 컬럼이 같은 행을 그룹으로 묶어서, 그룹 내 gpa 컬럼의 순위를 내림차순(DESC)으로 정렬

위 쿼리를 실행한 결과는 아래와 같습니다.

마치며,

윈도우 함수 중 하나인 순위 함수에 대해 알아보았습니다.

코딩테스트를 하면서 RANK() 함수를 활용해야하는 쿼리를 제대로 작성하지 못해 다시 공부하면서 정리하니 전에 공부했던 내용이 다시 생각나기도 하고 새롭게 다시 배우기도 했습니다.

관련한 다른 함수들도 많이 있으니, 다음에는 이외 함수들도 정리하는 시간을 가져보아야겠습니다 :)

참고 링크