datetime 데이터 조작

Snowflake의 데이터 타입과 함수

Jake Roach

Field Data Engineer

타임스탬프 구성요소 추출

Snowflake은 타임스탬프에서 구성요소를 추출하는 함수를 제공합니다

SELECT
    <fields>,

    DAY(<1>)  -- 다른 옵션도 많습니다!

FROM ...;

<1>: DATE, TIME, 또는 TIMESTAMP

DAY
MONTH
YEAR
HOUR
MINUTE
SECOND
DAYNAME
MONTHNAME

타임스탬프 파싱

SELECT
    exam_completed_time,

    YEAR(exam_completed_time) AS y,
    MONTHNAME(exam_completed_time) AS mn,
    DAYNAME(exam_completed_time) AS dn,
    DAY(exam_completed_time) AS d,

    HOUR(exam_completed_time) AS h,
    MINUTE(exam_completed_time) AS m,
    SECOND(exam_completed_time) AS s

FROM STUDENTS.grades;

타임스탬프 파싱

     exam_completed_time  |    y   |   mn  |   dn  |   d  |   h  |   m  |   s 
    --------------------- | ------ | ----- | ----- | ---- | ---- | ---- | ----

     2025-06-25 11:17:02  |  2025  |  Jun  |  Wed  |  25  |  11  |   6  |   2

     2025-06-26 08:49:49  |  2025  |  Jun  |  Thu  |  26  |   8  |  49  |  49
     2025-06-25 09:56:07  |  2025  |  Jun  |  Wed  |  25  |   9  |  56  |   7
     2025-06-24 22:14:27  |  2025  |  Jun  |  Tue  |  24  |  22  |  14  |  27
     2025-06-26 13:36:55  |  2025  |  Jun  |  Thu  |  26  |  13  |  36  |  55
     2025-06-25 16:23:09  |  2025  |  Jun  |  Wed  |  25  |  16  |  23  |   9

                                        ...

DATEDIFF

DATEDIFF는 두 날짜/타임스탬프 간의 간격을 구합니다

SELECT
    <fields>,


    DATEDIFF(<1>, <2>, <3>)   -- 단위, 첫 번째 타임스탬프, 두 번째 타임스탬프


FROM ...;

<1>: 결과의 시간 단위. 예: MINUTE, HOUR, DAY, YEAR, WEEK 등

<2>: 시작 타임스탬프

<3>: 종료 타임스탬프

DATEDIFF 사용

First TIMESTAMP:  2025-05-12 08:24:08
Second TIMESTAMP: 2025-11-13 03:05:46

SELECT 
    DATEDIFF(
        DAY, 
        TO_DATE('2025-05-12 08:24:08'), 
        TO_DATE('2025-11-13 03:05:46')
    )

                                   185 (DAYS)

DATEADD

SELECT

    -- 단위, 단위 수, 타임스탬프
    DATEADD(
        DAY, 
        185, 
        TO_DATE('2025-05-12 08:24:08')
    )

FROM ...;

2025-05-12 08:24:08 + 185 DAYs

         = 2025-11-13 08:24:08

DATE, TIME, 또는 TIMESTAMP에 시간 간격을 더할 때는 DATEADD를 사용합니다

<1>: 더할 시간 단위. 예: MINUTE, HOUR, DAY, YEAR, WEEK 등

<2>: 추가할 "단위"의 개수

<3>: 더할 대상 타임스탬프

DATE 조작

SELECT
    s_id,
    exam_completed_time AS completed_time,
    exam_due_time AS due_time,

    -- 완료 시점과 마감일의 차이 계산
    DATEDIFF(HOUR, exam_completed_time, exam_due_time) AS hours_early,

    -- 채점 마감일 계산
    DATEADD(WEEK, 1, exam_completed_time) AS grading_due

FROM STUDENTS.grades;

DATE 조작


  s_id  |     completed_time    |        due_time       |  hours_early  |      grading_due
 ------ | --------------------- | --------------------- | ------------- | ---------------------

   919  |  2025-06-25 11:17:02  |  2025-06-26 10:00:00  |       22      |  2025-07-02 11:17:02

   871  |  2025-06-26 08:49:49  |  2025-06-26 10:00:00  |        1      |  2025-07-03 08:49:49
   111  |  2025-06-25 09:56:07  |  2025-06-26 10:00:00  |       24      |  2025-07-02 09:56:07
   465  |  2025-06-24 22:14:27  |  2025-06-26 10:00:00  |       35      |  2025-07-01 22:14:27
   248  |  2025-06-26 13:36:55  |  2025-06-26 10:00:00  |       -3      |  2025-07-23 13:36:55
   767  |  2025-06-25 16:23:09  |  2025-06-26 10:00:00  |       17      |  2025-07-02 16:23:09

                                           ...

Ayo berlatih!

Snowflake의 데이터 타입과 함수