날짜 및 시간 값 작업하기¶

날짜 및 시간 계산은 분석 및 데이터 마이닝에서 가장 광범위하게 사용되는 가장 중요한 계산 중 하나입니다. 이 항목에서는 일반적인 날짜와 시간 쿼리 및 계산에 대한 실제 예를 제공합니다.

이 항목의 내용:

날짜 및 타임스탬프 로드하기¶

타임존이 연결되지 않은 타임스탬프 로드하기¶

다음 예에서 TIMESTAMP_TYPE_MAPPING 매개 변수는 TIMESTAMP_LTZ(현지 타임존)로 설정됩니다. TIMEZONE 매개 변수는 미국/시카고 시간으로 설정됩니다. 수신 중인 타임스탬프 세트에 타임존이 지정되지 않은 경우, Snowflake는 해당 타임스탬프가 설정된 타임존의 현지 시간을 나타내는 것으로 가정하여 해당 문자열을 로드합니다.

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 ('2016-05-01 00:00:00.000');

SELECT * FROM time;

+---------------------------------+
| LTZ                             |
|---------------------------------|
| Sun, 01 May 2016 00:00:00 -0500 |
+---------------------------------+

Copy

타임존이 연결된 타임스탬프 로드하기¶

다음 예에서 TIMESTAMP_TYPE_MAPPING 매개 변수는 TIMESTAMP_LTZ(현지 타임존)로 설정됩니다. TIMEZONE 매개 변수는 미국/시카고 시간으로 설정됩니다. 수신 타임스탬프 세트에 다른 타임존이 지정된 것으로 가정합니다. Snowflake는 미국/시카고 시간으로 문자열을 로드합니다.

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 ('2016-04-30 19:00:00.000 -0800');

SELECT * FROM time;

+---------------------------------+
| LTZ                             |
|---------------------------------|
| Sat, 30 Apr 2016 22:00:00 -0500 |
+---------------------------------+

Copy

타임스탬프를 대체 타임존으로 변환하기¶

다음 예에서 타임스탬프 값 세트는 타임존 데이터를 제외하고 저장됩니다. 타임스탬프는 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 ('2016-05-01 00:00:00.000');

SELECT * FROM utctime;

SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::timestamp_ltz AS ChicagoTime
FROM utctime;

SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::timestamp_ltz AS LATime
FROM utctime;

Copy

테이블의 날짜 열에 유효한 날짜 문자열 삽입하기¶

이 예에서는 DATE 열에 값을 삽입하는 방법을 보여줍니다.

CREATE TABLE my_table(id INTEGER, date1 DATE);
-- July 23, 2016.
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2016.07.23', 'YYYY.MM.DD'));
-- NULL.
INSERT INTO my_table(id) VALUES (2);

Copy

SELECT id, date1 
    FROM my_table 
    ORDER BY id;
+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2016-07-23 |
|  2 | NULL       |
+----+------------+

Copy

TO_DATE 함수에서는 TIMESTAMP 값 및 TIMESTAMP 형식의 문자열을 사용할 수 있지만, 시간 정보(시간, 분 등)는 무시됩니다.

INSERT INTO my_table(id, date1) VALUES
    (3, TO_DATE('2020.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
    (4, TO_TIMESTAMP('2020.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));

Copy

SELECT id, date1 
    FROM my_table 
    WHERE id >= 3;
+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2020-02-20 |
|  4 | 2020-02-24 |
+----+------------+

Copy

시간만으로 정의된 DATE를 삽입하는 경우의 기본 날짜는 1970년 1월 1일입니다.

INSERT INTO my_table(id, date1) VALUES 
    (5, TO_DATE('11:20:30', 'hh:mi:ss'));

Copy

SELECT id, date1 
    FROM my_table 
    WHERE id = 5;
+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+

Copy

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-2016 00:00:00 |
|  2 | NULL                 |
|  3 | 20-Feb-2020 00:00:00 |
|  4 | 24-Feb-2020 00:00:00 |
|  5 | 01-Jan-1970 00:00:00 |
+----+----------------------+

Copy

현재 날짜 및 시간 검색하기¶

현재 날짜를 DATE 값으로 가져오기:

SELECT current_date();

Copy

현재 날짜 및 시간을 TIMESTAMP 값으로 가져오기:

SELECT current_timestamp();

Copy

날짜 및 요일 검색하기¶

EXTRACT 함수를 사용하여 현재 요일을 숫자로 가져오기:

SELECT EXTRACT('dayofweek',current_date());

Copy

참고

dayofweek_iso 부분은 ISO-8601 데이터 요소 및 호환 형식 표준을 따릅니다. 이 함수는 요일을 1~7의 정수값(1은 월요일을 나타냄)으로 반환합니다.
기타 시스템과의 호환성을 위해 dayofweek 부분은 UNIX 표준을 따릅니다. 이 함수는 요일을 0~6의 정수값(0은 일요일을 나타냄)으로 반환합니다.

TO_VARCHAR 또는 DECODE 함수를 사용하여 현재 요일을 문자열로 가져오기:

-- Output short English names, e.g. "Sun", "Mon" etc.
SELECT TO_VARCHAR(current_date(), 'DY');

-- Output arbitrary, explicitly-provided weekday names:
SELECT DECODE(EXTRACT ('dayofweek_iso',current_date()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday');

Copy

날짜 및 시간 부분 검색하기¶

DATE_PART 함수를 사용하여 현재 날짜 및 시간에 대한 다양한 날짜 및 시간 부분 가져오기:

-- Current day of the month
SELECT DATE_PART(day, current_timestamp());

-- Current year
SELECT DATE_PART(year, current_timestamp());

-- Current month
SELECT DATE_PART(month, current_timestamp());

-- Current hour
SELECT DATE_PART(hour, current_timestamp());

-- Current minute
SELECT DATE_PART(minute, current_timestamp());

-- Current second
SELECT DATE_PART(second, current_timestamp());

Copy

EXTRACT 함수를 사용한 대체 옵션:

-- Current day of the month
SELECT EXTRACT('day', current_timestamp());

-- Current year
SELECT EXTRACT('year', current_timestamp());

-- Current month
SELECT EXTRACT('month', current_timestamp());

-- Current hour
SELECT EXTRACT('hour', current_timestamp());

-- Current minute
SELECT EXTRACT('minute', current_timestamp());

-- Current second
SELECT EXTRACT('second', current_timestamp());

Copy

대체 테이블 형식 출력:

SELECT day(current_timestamp() ) ,
  hour( current_timestamp() ) ,
  second(current_timestamp()) ,
  minute(current_timestamp()) ,
  month(current_timestamp());

  +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+
  | DAY(CURRENT_TIMESTAMP() ) | HOUR( CURRENT_TIMESTAMP() ) | SECOND(CURRENT_TIMESTAMP()) | MINUTE(CURRENT_TIMESTAMP()) | MONTH(CURRENT_TIMESTAMP()) |
  |---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------|
  |                         7 |                           6 |                          43 |                          44 |                          9 |
  +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+

Copy

비즈니스 달력 날짜 및 시간 계산하기¶

DATE_TRUNC 함수를 사용하여 월의 첫 번째 날을 DATE 값으로 가져오기: 예를 들어, 현재 월의 첫 번째 날 가져오기:

SELECT DATE_TRUNC('month', current_date());

Copy

DATEADD 및 DATE_TRUNC 함수를 사용하여 이번 달의 마지막 날을 DATE 값으로 가져오기:

SELECT DATEADD('day',-1, DATE_TRUNC('month', DATEADD(day,31,DATE_TRUNC('month',current_date()) ) ) );

Copy

대체 옵션입니다. 다음 예에서는 DATE_TRUNC는 이번 달의 시작을 검색하고 1개월을 더하여 다음 달의 시작을 검색한 후 1일을 빼서 현재 월의 마지막 날을 결정합니다.

SELECT DATEADD('day', -1,
  DATEADD('month', 1,
  DATE_TRUNC('month', current_date())));

Copy

이전 달의 마지막 날을 DATE 값으로 가져오기:

SELECT DATEADD(day, -1, DATE_TRUNC('month',current_date()) );

Copy

이름을 기준으로 연도의 이번 달 가져오기:

-- Output short English names, e.g. "Jan", "Dec", etc.
SELECT TO_VARCHAR(current_date(), 'Mon');

-- Output arbitrary, explicitly-provided month names
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');

Copy

이번 주의 월요일 날짜 가져오기:

SELECT DATEADD('day', (EXTRACT('dayofweek_iso', current_date()) * -1) +1 , current_date() );

Copy

이번 주의 금요일 날짜 가져오기:

SELECT DATEADD('day', (5 - EXTRACT('dayofweek_iso', current_date()) ) , current_date() );

Copy

DATE_PART 함수를 사용하여 이번 달의 첫 번째 월요일 날짜 가져오기:

SELECT DATEADD(
   day,
   MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', current_date()) ), 7),
   DATE_TRUNC('month', current_date()));

Copy

참고

위의 쿼리에서 7 + 1 의 1 값은 월요일로 해석됩니다. 첫 번째 화요일, 수요일 등에 대한 날짜를 검색하려면, Sunday 에서 7 까지 2, 3 등으로 바꿉니다.

올해의 첫 번째 날을 DATE 값으로 가져오기:

SELECT DATE_TRUNC('year', current_date());

Copy

올해의 마지막 날을 DATE 값으로 가져오기:

SELECT DATEADD('day', -1,
       DATEADD('year', 1,
       DATE_TRUNC('year', current_date())));

Copy

작년의 마지막 날을 DATE 값으로 가져오기:

SELECT DATEADD('day', -1, DATE_TRUNC('year',current_date()) );

Copy

이번 사분기의 첫 번째 날을 DATE 값으로 가져오기:

SELECT DATE_TRUNC('quarter',current_date());

Copy

이번 사분기의 마지막 날을 DATE 값으로 가져오기:

SELECT DATEADD('day', -1,
  DATEADD('month', 3,
  DATE_TRUNC('quarter', current_date())));

Copy

오늘의 자정에 대한 날짜 및 타임스탬프 가져오기:

SELECT DATE_TRUNC('day', current_timestamp());

+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700        |
+----------------------------------------+

Copy

날짜 및 시간 값 증분¶

오늘에 2년 추가:

SELECT DATEADD(year, 2, current_date());

Copy

오늘에 2일 추가:

SELECT DATEADD(day,2,current_date());

Copy

현재 날짜 및 시간에 2시간 추가:

SELECT DATEADD(hour,2,current_timestamp());

Copy

현재 날짜 및 시간에 2분 추가:

SELECT DATEADD(minute,2,current_timestamp());

Copy

현재 날짜 및 시간에 2초 추가:

SELECT DATEADD(second,2,current_timestamp());

Copy

유효 문자 문자열을 날짜, 시간 또는 타임스탬프로 변환하기¶

대부분의 경우, Snowflake는 문자열 형식의 날짜 및 타임스탬프 값을 올바르게 처리합니다. 문자열 기반 비교 또는 결과에서 세션 매개 변수에 설정된 타임스탬프와 다른 형식을 사용하는 경우와 같은 특정한 상황에서는 값을 원하는 형식으로 명시적으로 변환하여 예기치 않은 결과가 발생하는 것을 방지하는 것이 좋습니다.

예를 들어, 명시적으로 캐스팅하지 않고 문자열 값을 비교하면 다음과 같은 문자열 기반 결과가 제공됩니다.

-- Note the column data type is string
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');

-- Comparison without explicit casting
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';

+------------+
| TIMESTAMP1 |
|------------|
+------------+

-- Comparison with explicit casting to 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 |
+---------------------------------+

Copy

변환 함수에 대한 자세한 내용은 변환 함수의 날짜 및 시간 형식 을 참조하십시오.

날짜 문자열에 날짜 연산 적용하기¶

문자열로 표현된 날짜에 5일 더하기:

SELECT DATEADD('day',5,TO_TIMESTAMP ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss') );

+--------------------------------------------------------------------------------+
| DATEADD('DAY',5,TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') ) |
|--------------------------------------------------------------------------------|
| Thu, 17 Jan 2016 00:00:00 -0800                                                |
+--------------------------------------------------------------------------------+

Copy

DATEDIFF 함수를 사용하여 현재 날짜와 문자열로 표현된 날짜 사이의 차이를 계산합니다.

-- Using the TO_TIMESTAMP function:
SELECT DATEDIFF('day', TO_TIMESTAMP ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss') , current_date() );

+-------------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') , CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------------|
|                                                                                            240  |
+-------------------------------------------------------------------------------------------------+

-- Using the TO_DATE function:
SELECT DATEDIFF('day', TO_DATE ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss'), current_date() );

+-------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_DATE ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS'), CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------|
|                                                                                      240  |
+-------------------------------------------------------------------------------------------+

Copy

지정된 날짜에 1일 더하기:

SELECT TO_DATE('2019-01-15') + 1;

+---------------------------+
| TO_DATE('2019-01-15') + 1 |
|---------------------------|
| 2019-01-16                |
+---------------------------+

Copy

현재 날짜에서(예: 2019년 1월 21일)에서 9일 빼기:

SELECT CURRENT_DATE() - 9;

+--------------------+
| CURRENT_DATE() - 9 |
|--------------------|
| 2019-01-12         |
+--------------------+

Copy

날짜 또는 시간 사이의 차이 계산하기¶

오늘과 3년 후 날짜 사이의 차이 계산:

SELECT DATEDIFF(year, current_date(),
  DATEADD(year, 3, current_date() ) );

Copy

오늘과 3개월 후 날짜 사이의 차이 계산:

SELECT DATEDIFF(month, current_date(),
  DATEADD(month, 3, current_date()) );

Copy

오늘과 3일 후 날짜 사이의 차이 계산:

SELECT DATEDIFF(day, current_date(),
  DATEADD(day, 3, current_date()) );

Copy

현재 시간과 3시간 후 시간 사이의 차이 계산:

SELECT DATEDIFF(hour, current_timestamp(),
  DATEADD(hour, 3, current_timestamp()) );

Copy

현재 시간과 3분 후 시간 사이의 차이 계산:

SELECT DATEDIFF(minute, current_timestamp(),
  DATEADD(minute, 3, current_timestamp()) );

Copy

현재 시간과 3초 후 시간 사이의 차이 계산:

SELECT DATEDIFF(second, current_timestamp(),
  DATEADD(second, 3, current_timestamp()) );

Copy

연간 달력 뷰 만들기¶

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 |
+-----+------------+-----------------+-----------+---------+

Copy