Après le dernier article sur les fonctions Window, on va maintenant faire un focus sur les dates. Pour moi, c'est un sujet indispensable à maîtriser car on s'en sert tout le temps, et c'est aussi un des endroits où on peut vite se tromper sans s'en rendre compte.

Dans cet article, on va aller droit au but avec les fonctions qu'on utilise tout le temps : DATE_TRUNC, DATE_PART / EXTRACT, DATEADD, DATEDIFF, etc., avec des cas concrets et du SQL.

USE DATABASE demo_db;
USE SCHEMA demo;

CREATE OR REPLACE TABLE demo_events (
  event_id    INTEGER,
  user_id     INTEGER,
  event_ts    TIMESTAMP_LTZ,
  event_type  VARCHAR,
  amount      NUMBER(10,2)
);

INSERT INTO demo_events (event_id, user_id, event_ts, event_type, amount) VALUES
  (1, 101, '2025-01-31 23:50:00'::TIMESTAMP_LTZ, 'purchase', 120),
  (2, 101, '2025-02-01 00:10:00'::TIMESTAMP_LTZ, 'purchase', 80),
  (3, 101, '2025-02-15 14:05:00'::TIMESTAMP_LTZ, 'refund',   -20),
  (4, 102, '2025-02-03 09:00:00'::TIMESTAMP_LTZ, 'purchase', 60),
  (5, 102, '2025-03-01 08:30:00'::TIMESTAMP_LTZ, 'purchase', 90),
  (6, 103, '2025-03-15 18:20:00'::TIMESTAMP_LTZ, 'purchase', 200);

DATE vs TIMESTAMP

Je vais peut-être rien t'apprendre, mais sur Snowflake (et ailleurs), c'est simple DATE = uniquement la date, sans heure. TIMESTAMP = date + heure, mais avec plusieurs variantes (dont TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ).

Le point important (qu'on oublie trop souvent) c'est que avec TIMESTAMP_LTZ, Snowflake stocke en interne en UTC, mais les affichages se font dans le fuseau horaire de la session (TIMEZONE).
Donc deux sessions peuvent voir différemment la même valeur si chacune utilise un fuseau horaire différent.

ALTER SESSION SET TIMEZONE = 'UTC';
SELECT '2025-02-01 00:30:00'::TIMESTAMP_LTZ AS ts_seen_in_utc;

ALTER SESSION SET TIMEZONE = 'Europe/Paris';
SELECT '2025-02-01 00:30:00'::TIMESTAMP_LTZ AS ts_seen_in_paris;

Scénario 1 : Regrouper par mois ou semaine avec DATE_TRUNC

DATE_TRUNC sert à ramener une date ou heure au début d'une période (début du mois, du trimestre, de l'heure, etc...)

ALTER SESSION SET WEEK_START = 1;  -- 1 = lundi (dimanche = 7)

SELECT
  event_ts,
  DATE_TRUNC('week', event_ts) AS week_start,
  DATE_TRUNC('month', event_ts) AS month_start,
  DATE_TRUNC('quarter', event_ts) AS quarter_start,
  DATE_TRUNC('year', event_ts) AS year_start
FROM demo_events
ORDER BY event_ts;
Regrouper par mois ou semaine avec DATE_TRUNC
Regrouper par mois ou semaine avec DATE_TRUNC

/!\ Attention au jour de début de semaine DATE_TRUNC('week', …)

Quand tu tronques à la semaine, le résultat dépend du paramètre de session WEEK_START et donc ce paramètre influence aussi d'autres fonctions semaine (comme DATEDIFF en weeks).

Scénario 2 : Extraire une partie d'une date (année, mois, jour...) avec DATE_PART ou EXTRACT

Quand tu veux splitter la date sur plusieurs colonnes par exemple (mois = 1..12, année, jour de semaine…), l'utilisation de DATE_PART est très pratique (ou EXTRACT, qui est une alternative).

SELECT
  event_ts,
  DATE_PART('year',  event_ts) AS y,
  DATE_PART('month', event_ts) AS m,
  DATE_PART('day',   event_ts) AS d
FROM demo_events;
Extraire une partie d’une date (année, mois, jour...) avec DATE_PART ou EXTRACT
Extraire une partie d'une date (année, mois, jour...) avec DATE_PART

Version EXTRACT (même idée) :

SELECT
  event_ts,
  EXTRACT(year  FROM event_ts) AS y,
  EXTRACT(month FROM event_ts) AS m,
  EXTRACT(week FROM event_ts) AS w,
  EXTRACT(day FROM event_ts) AS d
FROM demo_events;
💡
Certaines extractions liées aux semaines (week, dayofweek, yearofweek, etc.) peuvent dépendre de WEEK_START et parfois de WEEK_OF_YEAR_POLICY.
Extraire une partie d’une date (année, mois, jour...) avec EXTRACT
Extraire une partie d'une date (année, mois, jour...) avec EXTRACT

Scénario 3 : Ajouter ou soustraire du temps avec DATEADD

DATEADD(type, value, expr) permet d'ajouter (ou soustraire si value est négatif) une quantité à une date ou heure.
Nb : Il existe des alias qui font la même chose pour du time et timestamp (TIMEADD, TIMESTAMPADD).

SELECT
  event_ts,
  DATEADD('day', 7, event_ts)   AS plus_7_days,
  DATEADD('hour', -2, event_ts) AS minus_2_hours
FROM demo_events;
Ajouter ou soustraire du temps avec DATEADD
Ajouter ou soustraire du temps avec DATEADD

Scénario 4 : Calculer un écart entre deux dates avec DATEDIFF

DATEDIFF(<type>, 'exp1', 'exp2') calcule une différence entre deux dates selon une unité (jours, heures, mois etc...).

SELECT
  event_id,
  user_id,
  event_ts,
  DATEDIFF('day',
           LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts),
           event_ts
  ) AS days_since_prev,

  DATEDIFF('week',
           LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts),
           event_ts
  ) AS week_since_prev
FROM demo_events
ORDER BY user_id, event_ts;
Calculer un écart entre deux dates avec DATEDIFF
Calculer un écart entre deux dates avec DATEDIFF

Scénario 5 : Fin de période ou début de période (LAST_DAY, NEXT_DAY, PREVIOUS_DAY)

Dernier jour d'une période

LAST_DAY(date, part) renvoie le dernier jour de la période demandée (souvent le dernier jour du mois).

SELECT
  event_ts::DATE                             AS d,
  LAST_DAY(event_ts::DATE)                   AS last_day_of_month,
  LAST_DAY(event_ts::DATE, 'quarter')        AS last_day_of_quarter
FROM demo_events;
Fin de période ou début de période (LAST_DAY, NEXT_DAY, PREVIOUS_DAY)
Fin de période ou début de période (LAST_DAY, NEXT_DAY, PREVIOUS_DAY)

Prochain lundi / précédent lundi

NEXT_DAY = renvoie le prochain jour de semaine après une date (ex : le prochain lundi).

PREVIOUS_DAY = fait l'inverse et renvoie le dernier jour de semaine avant la date (ex : le lundi précédent).

SELECT
  event_ts::DATE                           AS d,
  NEXT_DAY(event_ts::DATE, 'MONDAY')       AS next_monday,
  PREVIOUS_DAY(event_ts::DATE, 'MONDAY')   AS prev_monday
FROM demo_events;
Fin de période ou début de période (LAST_DAY, NEXT_DAY, PREVIOUS_DAY)
Début de période (LAST_DAY, NEXT_DAY, PREVIOUS_DAY)

Scénario 6 : Parser proprement une date ou un timestamp (TO_DATE, TO_TIMESTAMP, TRY_*)

  • TO_DATE prend un timestamp (ou une string au format timestamp) et supprimer l'heure.
  • TO_TIMESTAMP convertit vers un timestamp et mappe vers TO_TIMESTAMP_NTZ/LTZ/TZ selon le paramètre de session TIMESTAMP_TYPE_MAPPING (par défaut TIMESTAMP_NTZ).
  • Et pour éviter de faire planter un chargement quand le format varie (ou quand une valeur est invalide), les fonctions TRY_* (TRY_TO_DATE, TRY_TO_TIMESTAMP etc...) sont super pratiques car au lieu d'erreur, elles renvoient NULL.
SELECT
  raw_value,

  TO_DATE('2025.02.01 09:30:00', 'YYYY.MM.DD HH24:MI:SS') AS d,
  TO_TIMESTAMP('2025.02.01 09:30:00', 'YYYY.MM.DD HH24:MI:SS') AS ts,
  
  -- Si ça matche, OK. Sinon => NULL
  TRY_TO_DATE(raw_value) AS parsed_date_default,

  -- Parsing avec format explicite
  TRY_TO_DATE(raw_value, 'DD/MM/YYYY') AS parsed_date_fr,

  -- Timestamp : default ou avec format explicite
  TRY_TO_TIMESTAMP(raw_value) AS parsed_ts_default,
  TRY_TO_TIMESTAMP(raw_value, 'YYYY-MM-DD HH24:MI:SS') AS parsed_ts_fmt

FROM (
  SELECT column1 AS raw_value
  FROM VALUES
    ('2025-02-03'),
    ('03/02/2025'),
    ('2025-02-03 10:15:00'),
    ('not_a_date'),
    (NULL)
);
Parser proprement une date ou un timestamp (TO_DATE, TO_TIMESTAMP, TRY_*)
Parser proprement une date ou un timestamp (TO_DATE, TO_TIMESTAMP, TRY_*)

Aller plus loin : Formation Snowflake

J'ai regroupé tous mes articles Snowflake dans un parcours complet.

👉 Accéder à la Formation Snowflake

Vous voulez que je vous accompagne sur votre projet data (Snowflake, ingestion, modélisation, performance, coûts, gouvernance) ?
👉 Réserver un appel de 30 minutes