Manipuler des données datetime

Types de données et fonctions dans Snowflake

Jake Roach

Field Data Engineer

Extraire des composants de timestamp

Snowflake propose des fonctions pour extraire des composants d’un timestamp

SELECT
    <fields>,

    DAY(<1>)  -- Many other options!

FROM ...;

<1> : DATE, TIME ou TIMESTAMP

DAY
MONTH
YEAR
HOUR
MINUTE
SECOND
DAYNAME
MONTHNAME

Analyser des timestamps

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;

Analyser des timestamps

     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 calcule l’intervalle entre deux dates ou timestamps

SELECT
    <fields>,


    DATEDIFF(<1>, <2>, <3>)   -- unit, first timestamp, second timestamp


FROM ...;

<1> : Unité du résultat, comme MINUTE, HOUR, DAY, YEAR, WEEK, etc.

<2> : Timestamp de début

<3> : Timestamp de fin

Utiliser DATEDIFF

Premier TIMESTAMP :  2025-05-12 08:24:08
Deuxième 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 (JOURS)

DATEADD

SELECT

    -- unité, nombre d’unités, timestamp
    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

Ajoutez des intervalles à DATE, TIME ou TIMESTAMP avec DATEADD

<1> : Unité à ajouter, comme MINUTE, HOUR, DAY, YEAR, WEEK, etc.

<2> : nb d’« unités » à ajouter

<3> : Le timestamp de départ

Manipuler une DATE

SELECT
    s_id,
    exam_completed_time AS completed_time,
    exam_due_time AS due_time,

    -- Trouver l’écart entre la fin et l’échéance
    DATEDIFF(HOUR, exam_completed_time, exam_due_time) AS hours_early,

    -- Déterminer la date limite de correction pour l’enseignant
    DATEADD(WEEK, 1, exam_completed_time) AS grading_due

FROM STUDENTS.grades;

Manipuler une 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

                                           ...

Passons à la pratique !

Types de données et fonctions dans Snowflake