날짜와 시간 값 다루기
날짜와 시간 값 다루기 (Working with date and time values)
Snowflake에서 날짜와 시간 값을 다루는 실용적인 예제를 모은 문서예요. 날짜와 시간 계산은 분석과 데이터 마이닝에서 가장 널리 사용되고 가장 중요한 계산 중 하나예요. 날짜·타임스탬프 로딩, 현재 날짜·시간 조회, 날짜·시간 부분 추출, 업무 달력 계산, 날짜 산술, 차이 계산, 연간 달력 뷰 생성 같은 예제를 확인할 수 있어요.
본문
날짜와 시간 계산은 분석과 데이터 마이닝에서 가장 널리 사용되고 가장 중요한 계산 중 하나예요. 이 문서는 일반적인 날짜·시간 쿼리와 계산의 실용 예제를 제공해요.
날짜와 타임스탬프 로딩 (Loading dates and timestamps)
이 섹션은 날짜와 타임스탬프 값을 로딩하는 예제를 제공하고, 이 값을 로딩할 때 시간대 관련 고려 사항을 설명해요.
시간대가 붙지 않은 타임스탬프 로딩 (Loading timestamps with no time zone attached)
다음 예제에서 TIMESTAMP_TYPE_MAPPING 파라미터는 TIMESTAMP_LTZ(로컬 시간대)로 설정돼요. TIMEZONE 파라미터는 America/Chicago 시간으로 설정돼요. 일부 들어오는 타임스탬프에 시간대가 지정되지 않았다면, Snowflake는 TIMEZONE 파라미터에 설정된 시간대의 로컬 시간을 나타낸다고 가정하고 그 문자열을 로딩해요.
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';
CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-05-01 00:00:00.000');
SELECT * FROM time;
+-------------------------------+
| LTZ |
|-------------------------------|
| 2024-05-01 00:00:00.000 -0500 |
+-------------------------------+
시간대가 붙은 타임스탬프 로딩 (Loading timestamps with a time zone attached)
다음 예제에서 TIMESTAMP_TYPE_MAPPING 파라미터는 TIMESTAMP_LTZ(로컬 시간대)로 설정돼요. TIMEZONE 파라미터는 America/Chicago 시간으로 설정돼요. 일부 들어오는 타임스탬프에 다른 시간대가 지정되어 있다면, Snowflake는 America/Chicago 시간으로 문자열을 로딩해요.
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';
CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2024-04-30 19:00:00.000 -0800');
SELECT * FROM time;
+-------------------------------+
| LTZ |
|-------------------------------|
| 2024-04-30 22:00:00.000 -0500 |
+-------------------------------+
타임스탬프를 다른 시간대로 변환하기 (Converting timestamps to alternative time zones)
다음 예제에서 타임스탬프 값 집합이 시간대 데이터 없이 저장돼요. 타임스탬프는 UTC 시간으로 로딩되어 다른 시간대로 변환돼요.
ALTER SESSION SET TIMEZONE = 'UTC';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';
CREATE OR REPLACE TABLE utctime (ntz TIMESTAMP_NTZ);
INSERT INTO utctime VALUES ('2024-05-01 00:00:00.000');
SELECT * FROM utctime;
+-------------------------+
| NTZ |
|-------------------------|
| 2024-05-01 00:00:00.000 |
+-------------------------+
SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::TIMESTAMP_LTZ AS ChicagoTime
FROM utctime;
+---------------------------+
| CHICAGOTIME |
|---------------------------|
| 2024-04-30 19:00:00 +0000 |
+---------------------------+
SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::TIMESTAMP_LTZ AS LATime
FROM utctime;
+---------------------------+
| LATIME |
|---------------------------|
| 2024-04-30 17:00:00 +0000 |
+---------------------------+
테이블의 DATE 열에 유효한 날짜 문자열 삽입하기 (Inserting valid date strings into date columns in a table)
이 예제는 DATE 열에 값을 삽입해요.
CREATE OR REPLACE TABLE my_table(id INTEGER, date1 DATE);
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2024.07.23', 'YYYY.MM.DD'));
INSERT INTO my_table(id) VALUES (2);
SELECT id, date1
FROM my_table
ORDER BY id;
+----+------------+
| ID | DATE1 |
|----+------------|
| 1 | 2024-07-23 |
| 2 | NULL |
+----+------------+
TO_DATE 함수는 TIMESTAMP 값과 TIMESTAMP 형식의 문자열을 받지만, 시간 정보(시, 분 등)는 버려요.
INSERT INTO my_table(id, date1) VALUES
(3, TO_DATE('2024.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
(4, TO_TIMESTAMP('2024.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));
SELECT id, date1
FROM my_table
WHERE id >= 3;
+----+------------+
| ID | DATE1 |
|----+------------|
| 3 | 2024-02-20 |
| 4 | 2024-02-24 |
+----+------------+
시간만 있는 DATE를 삽입하면 기본 날짜는 1970년 1월 1일이에요.
INSERT INTO my_table(id, date1) VALUES
(5, TO_DATE('11:20:30', 'hh:mi:ss'));
SELECT id, date1
FROM my_table
WHERE id = 5;
+----+------------+
| ID | DATE1 |
|----+------------|
| 5 | 1970-01-01 |
+----+------------+
DATE 값을 검색할 때 TIMESTAMP 값으로 포맷할 수 있어요.
SELECT id,
TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
FROM my_table
ORDER BY id;
+----+----------------------+
| ID | DATE1 |
|----+----------------------|
| 1 | 23-Jul-2024 00:00:00 |
| 2 | NULL |
| 3 | 20-Feb-2024 00:00:00 |
| 4 | 24-Feb-2024 00:00:00 |
| 5 | 01-Jan-1970 00:00:00 |
+----+----------------------+
현재 날짜와 시간 조회 (Retrieving the current date and time)
현재 날짜를 DATE 값으로 가져오기:
SELECT CURRENT_DATE();
현재 날짜와 시간을 TIMESTAMP 값으로 가져오기:
SELECT CURRENT_TIMESTAMP();
날짜와 요일 조회 (Retrieving dates and days of the week)
EXTRACT 함수로 현재 요일을 숫자로 가져오기:
SELECT EXTRACT('dayofweek', CURRENT_DATE());
참고:
dayofweek_iso부분은 ISO-8601 데이터 요소 및 교환 형식 표준을 따라요. 이 함수는 요일을 1-7 범위의 정수 값으로 반환하며, 여기서 1은 월요일을 나타내요.- 일부 다른 시스템과의 호환을 위해
dayofweek부분은 UNIX 표준을 따라요. 이 함수는 요일을 0-6 범위의 정수 값으로 반환하며, 여기서 0은 일요일을 나타내요.
TO_VARCHAR 또는 DECODE 함수로 현재 요일을 문자열로 가져올 수 있어요.
현재 날짜의 짧은 영어 이름(예: Sun, Mon 등)을 반환하는 쿼리 실행:
SELECT TO_VARCHAR(CURRENT_DATE(), 'dy');
현재 날짜의 명시적으로 제공된 요일 이름을 반환하는 쿼리 실행:
SELECT DECODE(EXTRACT('dayofweek_iso', CURRENT_DATE()),
1, 'Monday',
2, 'Tuesday',
3, 'Wednesday',
4, 'Thursday',
5, 'Friday',
6, 'Saturday',
7, 'Sunday') AS weekday_name;
날짜와 시간 부분 조회 (Retrieving date and time parts)
DATE_PART 함수로 현재 날짜와 시간의 다양한 날짜·시간 부분을 가져올 수 있어요.
현재 달의 날짜 쿼리:
SELECT DATE_PART(day, CURRENT_TIMESTAMP());
현재 연도 쿼리:
SELECT DATE_PART(year, CURRENT_TIMESTAMP());
현재 월 쿼리:
SELECT DATE_PART(month, CURRENT_TIMESTAMP());
현재 시(hour) 쿼리:
SELECT DATE_PART(hour, CURRENT_TIMESTAMP());
현재 분 쿼리:
SELECT DATE_PART(minute, CURRENT_TIMESTAMP());
현재 초 쿼리:
SELECT DATE_PART(second, CURRENT_TIMESTAMP());
EXTRACT 함수로도 현재 날짜와 시간의 다양한 날짜·시간 부분을 가져올 수 있어요.
현재 달의 날짜 쿼리:
SELECT EXTRACT('day', CURRENT_TIMESTAMP());
현재 연도 쿼리:
SELECT EXTRACT('year', CURRENT_TIMESTAMP());
현재 월 쿼리:
SELECT EXTRACT('month', CURRENT_TIMESTAMP());
현재 시 쿼리:
SELECT EXTRACT('hour', CURRENT_TIMESTAMP());
현재 분 쿼리:
SELECT EXTRACT('minute', CURRENT_TIMESTAMP());
현재 초 쿼리:
SELECT EXTRACT('second', CURRENT_TIMESTAMP());
이 쿼리는 현재 날짜와 시간의 다양한 날짜·시간 부분이 포함된 표 형식 출력을 반환해요.
SELECT month(CURRENT_TIMESTAMP()) AS month,
day(CURRENT_TIMESTAMP()) AS day,
hour(CURRENT_TIMESTAMP()) AS hour,
minute(CURRENT_TIMESTAMP()) AS minute,
second(CURRENT_TIMESTAMP()) AS second;
+-------+-----+------+--------+--------+
| MONTH | DAY | HOUR | MINUTE | SECOND |
|-------+-----+------+--------+--------|
| 8 | 28 | 7 | 59 | 28 |
+-------+-----+------+--------+--------+
업무 달력 날짜와 시간 계산 (Calculating business calendar dates and times)
DATE_TRUNC 함수로 월의 첫 날을 DATE 값으로 가져오기. 예를 들어 현재 월의 첫 날:
SELECT DATE_TRUNC('month', CURRENT_DATE());
DATEADD와 DATE_TRUNC 함수로 현재 월의 마지막 날을 DATE 값으로 가져오기:
SELECT DATEADD('day',
-1,
DATE_TRUNC('month', DATEADD(day, 31, DATE_TRUNC('month',CURRENT_DATE()))));
대안으로 다음 예제는 DATE_TRUNC로 현재 월의 시작을 가져오고, 한 달을 더해 다음 달의 시작을 가져온 다음, 하루를 빼서 현재 월의 마지막 날을 결정해요.
SELECT DATEADD('day',
-1,
DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())));
이전 달의 마지막 날을 DATE 값으로 가져오기:
SELECT DATEADD(day,
-1,
DATE_TRUNC('month', CURRENT_DATE()));
현재 월의 짧은 영어 이름(예: Jan, Dec 등) 가져오기:
SELECT TO_VARCHAR(CURRENT_DATE(), 'Mon');
명시적으로 제공된 월 이름으로 현재 월 이름 가져오기:
SELECT DECODE(EXTRACT('month', CURRENT_DATE()),
1, 'January',
2, 'February',
3, 'March',
4, 'April',
5, 'May',
6, 'June',
7, 'July',
8, 'August',
9, 'September',
10, 'October',
11, 'November',
12, 'December') AS current_month;
현재 주의 월요일 날짜 가져오기:
SELECT DATEADD('day',
(EXTRACT('dayofweek_iso', CURRENT_DATE()) * -1) + 1,
CURRENT_DATE());
현재 주의 금요일 날짜 가져오기:
SELECT DATEADD('day',
(5 - EXTRACT('dayofweek_iso', CURRENT_DATE())),
CURRENT_DATE());
DATE_PART 함수로 현재 월의 첫 월요일 날짜 가져오기:
SELECT DATEADD(day,
MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', CURRENT_DATE())), 7),
DATE_TRUNC('month', CURRENT_DATE()));
참고: 위 쿼리에서
7 + 1의1값은 월요일을 나타내요. 첫 화요일, 수요일 등의 날짜를 가져오려면 각각2,3등을Sunday의7까지 대체하세요.
현재 연도의 첫 날을 DATE 값으로 가져오기:
SELECT DATE_TRUNC('year', CURRENT_DATE());
현재 연도의 마지막 날을 DATE 값으로 가져오기:
SELECT DATEADD('day',
-1,
DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE())));
이전 연도의 마지막 날을 DATE 값으로 가져오기:
SELECT DATEADD('day',
-1,
DATE_TRUNC('year', CURRENT_DATE()));
현재 분기의 첫 날을 DATE 값으로 가져오기:
SELECT DATE_TRUNC('quarter', CURRENT_DATE());
현재 분기의 마지막 날을 DATE 값으로 가져오기:
SELECT DATEADD('day',
-1,
DATEADD('month', 3, DATE_TRUNC('quarter', CURRENT_DATE())));
현재 요일 자정의 날짜와 타임스탬프 가져오기:
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP());
+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700 |
+----------------------------------------+
날짜와 시간 값 증가시키기 (Incrementing date and time values)
DATEADD 함수를 사용해 날짜와 시간 값을 증가시켜요.
현재 날짜에 2년 더하기:
SELECT DATEADD(year, 2, CURRENT_DATE());
현재 날짜에 2일 더하기:
SELECT DATEADD(day, 2, CURRENT_DATE());
현재 날짜와 시간에 2시간 더하기:
SELECT DATEADD(hour, 2, CURRENT_TIMESTAMP());
현재 날짜와 시간에 2분 더하기:
SELECT DATEADD(minute, 2, CURRENT_TIMESTAMP());
현재 날짜와 시간에 2초 더하기:
SELECT DATEADD(second, 2, CURRENT_TIMESTAMP());
유효한 문자 문자열을 날짜, 시간, 타임스탬프로 변환하기 (Converting valid character strings to dates, times, or timestamps)
대부분의 사용 사례에서 Snowflake는 문자열로 포맷된 날짜와 타임스탬프 값을 올바르게 처리해요. 문자열 기반 비교 같은 특정 경우나, 세션 파라미터에 설정된 형식과 다른 타임스탬프 형식에 결과가 의존하는 경우에는 예기치 않은 결과를 피하기 위해 값을 원하는 형식으로 명시적으로 변환하는 것을 권장해요.
예를 들어 명시적 캐스팅 없이 문자열 값을 비교하면 문자열 기반 결과가 나와요.
CREATE OR REPLACE TABLE timestamps(timestamp1 STRING);
INSERT INTO timestamps VALUES
('Fri, 05 Apr 2013 00:00:00 -0700'),
('Sat, 06 Apr 2013 00:00:00 -0700'),
('Sat, 01 Jan 2000 00:00:00 -0800'),
('Wed, 01 Jan 2020 00:00:00 -0800');
다음 쿼리는 명시적 캐스팅 없이 비교를 수행해요.
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';
+------------+
| TIMESTAMP1 |
|------------+
+------------+
다음 쿼리는 DATE로 명시적 캐스팅하여 비교를 수행해요.
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::DATE;
+---------------------------------+
| DATE1 |
|---------------------------------|
| Fri, 05 Apr 2013 00:00:00 -0700 |
| Sat, 06 Apr 2013 00:00:00 -0700 |
| Sat, 01 Jan 2000 00:00:00 -0800 |
+---------------------------------+
변환 함수에 대한 자세한 내용은 변환 함수의 날짜 및 시간 형식을 참조하세요.
날짜 문자열에 날짜 산술 적용하기 (Applying date arithmetic to date strings)
문자열로 표현된 날짜에 5일 더하기:
SELECT DATEADD('day',
5,
TO_TIMESTAMP('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'))
AS add_five_days;
+-------------------------+
| ADD_FIVE_DAYS |
|-------------------------|
| 2024-01-17 00:00:00.000 |
+-------------------------+
DATEDIFF 함수로 현재 날짜와 문자열로 표현된 날짜 사이의 일 수 차이를 계산할 수 있어요.
TO_TIMESTAMP 함수를 사용해 일 수 차이 계산:
SELECT DATEDIFF('day',
TO_TIMESTAMP ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
CURRENT_DATE())
AS to_timestamp_difference;
+-------------------------+
| TO_TIMESTAMP_DIFFERENCE |
|-------------------------|
| 229 |
+-------------------------+
TO_DATE 함수를 사용해 일 수 차이 계산:
SELECT DATEDIFF('day',
TO_DATE ('12-jan-2024 00:00:00','dd-mon-yyyy hh:mi:ss'),
CURRENT_DATE())
AS to_date_difference;
+--------------------+
| TO_DATE_DIFFERENCE |
|--------------------|
| 229 |
+--------------------+
지정된 날짜에 하루 더하기:
SELECT TO_DATE('2024-01-15') + 1 AS date_plus_one;
+---------------+
| DATE_PLUS_ONE |
|---------------|
| 2024-01-16 |
+---------------+
현재 날짜에서 9일 빼기 (예: 2024년 8월 28일):
SELECT CURRENT_DATE() - 9 AS date_minus_nine;
+-----------------+
| DATE_MINUS_NINE |
|-----------------|
| 2024-08-19 |
+-----------------+
날짜나 시간 사이의 차이 계산 (Calculating differences between dates or times)
현재 날짜와 3년 후 날짜 사이의 차이 계산:
SELECT DATEDIFF(year, CURRENT_DATE(),
DATEADD(year, 3, CURRENT_DATE()));
현재 날짜와 3개월 후 날짜 사이의 차이 계산:
SELECT DATEDIFF(month, CURRENT_DATE(),
DATEADD(month, 3, CURRENT_DATE()));
현재 날짜와 3일 후 날짜 사이의 차이 계산:
SELECT DATEDIFF(day, CURRENT_DATE(),
DATEADD(day, 3, CURRENT_DATE()));
현재 시간과 3시간 후 시간 사이의 차이 계산:
SELECT DATEDIFF(hour, CURRENT_TIMESTAMP(),
DATEADD(hour, 3, CURRENT_TIMESTAMP()));
현재 시간과 3분 후 시간 사이의 차이 계산:
SELECT DATEDIFF(minute, CURRENT_TIMESTAMP(),
DATEADD(minute, 3, CURRENT_TIMESTAMP()));
현재 시간과 3초 후 시간 사이의 차이 계산:
SELECT DATEDIFF(second, CURRENT_TIMESTAMP(),
DATEADD(second, 3, CURRENT_TIMESTAMP()));
연간 달력 뷰 만들기 (Creating yearly calendar views)
CREATE OR REPLACE VIEW calendar_2016 AS
SELECT n,
theDate,
DECODE (EXTRACT('dayofweek',theDate),
1 , 'Monday',
2 , 'Tuesday',
3 , 'Wednesday',
4 , 'Thursday',
5 , 'Friday',
6 , 'Saturday',
0 , 'Sunday') theDayOfTheWeek,
DECODE (EXTRACT(month FROM theDate),
1 , 'January',
2 , 'February',
3 , 'March',
4 , 'April',
5 , 'May',
6 , 'June',
7 , 'July',
8 , 'August',
9 , 'september',
10, 'October',
11, 'November',
12, 'December') theMonth,
EXTRACT(year FROM theDate) theYear
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY seq4()) AS n,
DATEADD(day, ROW_NUMBER() OVER (ORDER BY seq4())-1, TO_DATE('2016-01-01')) AS theDate
FROM table(generator(rowCount => 365)))
ORDER BY n ASC;
SELECT * from CALENDAR_2016;
+-----+------------+-----------------+-----------+---------+
| N | THEDATE | THEDAYOFTHEWEEK | THEMONTH | THEYEAR |
|-----+------------+-----------------+-----------+---------|
| 1 | 2016-01-01 | Friday | January | 2016 |
| 2 | 2016-01-02 | Saturday | January | 2016 |
...
| 364 | 2016-12-29 | Thursday | December | 2016 |
| 365 | 2016-12-30 | Friday | December | 2016 |
+-----+------------+-----------------+-----------+---------+