Utilisation des valeurs de date et d’heure¶

Les calculs de date et d’heure sont parmi les calculs les plus utilisés et les plus critiques dans l’analyse et l’exploration de données. Ce chapitre fournit des exemples pratiques de requêtes et de calculs courants concernant la date et l’heure.

Dans ce chapitre :

Chargement des dates et horodatages¶

Cette section fournit des exemples de chargement de valeurs de date et d’horodatage et décrit les considérations liées aux fuseaux horaires lors du chargement de ces valeurs.

Chargement d’horodatages sans fuseau horaire joint¶

Dans l’exemple suivant, le paramètre TIMESTAMP_TYPE_MAPPING est réglé sur TIMESTAMP_LTZ (fuseau horaire local). Lorsque le paramètre TIMEZONE est défini sur America/Chicago : Si certains horodatages entrants n’ont pas de fuseau horaire spécifié, Snowflake charge ces chaînes en supposant que les horodatages représentent l’heure locale dans le fuseau horaire défini pour le paramètre 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;

Copy

+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-05-01 00:00:00.000 -0500 |
+-------------------------------+

Chargement d’horodatage avec un fuseau horaire joint¶

Dans l’exemple suivant, le paramètre TIMESTAMP_TYPE_MAPPING est réglé sur TIMESTAMP_LTZ (fuseau horaire local). Lorsque le paramètre TIMEZONE est défini sur America/Chicago : Si certains horodatages entrants ont un fuseau horaire différent spécifié, Snowflake charge la chaîne à l’heure 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;

Copy

+-------------------------------+
| LTZ                           |
|-------------------------------|
| 2024-04-30 22:00:00.000 -0500 |
+-------------------------------+

Conversion d’horodatages en autres fuseaux horaires¶

Dans l’exemple suivant, un ensemble de valeurs d’horodatage est enregistré sans données de fuseau horaire. Les horodatages sont chargés en heure UTC et convertis en d’autres fuseaux horaires :

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');

Copy

SELECT * FROM utctime;

Copy

+-------------------------+
| NTZ                     |
|-------------------------|
| 2024-05-01 00:00:00.000 |
+-------------------------+

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

Copy

+---------------------------+
| CHICAGOTIME               |
|---------------------------|
| 2024-04-30 19:00:00 +0000 |
+---------------------------+

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

Copy

+---------------------------+
| LATIME                    |
|---------------------------|
| 2024-04-30 17:00:00 +0000 |
+---------------------------+

Insertion de chaînes de dates valides dans des colonnes de dates dans une table¶

Cet exemple insère des valeurs dans une colonne 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);

Copy

SELECT id, date1
  FROM my_table
  ORDER BY id;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2024-07-23 |
|  2 | NULL       |
+----+------------+

La fonction TO_DATE accepte les valeurs TIMESTAMP et les chaînes au format TIMESTAMP, mais ignore les informations de temps (heures, minutes, etc.).

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'));

Copy

SELECT id, date1
  FROM my_table
  WHERE id >= 3;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2024-02-20 |
|  4 | 2024-02-24 |
+----+------------+

Si vous insérez un élément DATE défini avec une seule heure, la date par défaut est le 1er janvier 1970.

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;

Copy

+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+

Lorsque vous récupérez des valeurs DATE, vous pouvez les formater comme valeurs TIMESTAMP :

SELECT id,
       TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
  FROM my_table
  ORDER BY id;

Copy

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

Récupération de la date et de l’heure actuelles¶

Obtenez la date du jour sous la forme d’une valeur DATE :

SELECT CURRENT_DATE();

Copy

Obtenez la date et l’heure actuelles sous la forme d’une valeur TIMESTAMP :

SELECT CURRENT_TIMESTAMP();

Copy

Récupération des dates et des jours de la semaine¶

Obtenez le jour de la semaine en cours sous la forme d’un nombre à l’aide de la fonction EXTRACT :

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

Copy

Note

La partie dayofweek_iso suit la norme ISO-8601 sur les éléments de données et les formats d’échange. La fonction retourne le jour de la semaine sous la forme d’une valeur entière comprise entre 1 et 7, où 1 représente le lundi.
Pour la compatibilité avec d’autres systèmes, la partie dayofweek suit la norme UNIX. La fonction retourne le jour de la semaine sous la forme d’une valeur entière comprise entre 0 et 6, où 0 représente le dimanche.

Vous pouvez obtenir le jour de la semaine en cours sous la forme d’une chaîne à l’aide de la fonction TO_VARCHAR ou DECODE.

Exécutez une requête qui renvoie le nom anglais court (par exemple, « Sun », « Mon », etc.) de la date actuelle :

SELECT TO_VARCHAR(CURRENT_DATE(), 'dy');

Copy

Exécutez une requête qui renvoie les noms des jours de la semaine explicitement fournis pour la date actuelle :

SELECT DECODE(EXTRACT('dayofweek_iso', CURRENT_DATE()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday') AS weekday_name;

Copy

Récupération des parties de date et d’heure¶

Vous pouvez obtenir diverses parties de date et d’heure pour la date et l’heure actuelles à l’aide de la fonction DATE_PART.

Requête pour le jour actuel du mois :

SELECT DATE_PART(day, CURRENT_TIMESTAMP());

Copy

Requête pour l’année en cours :

SELECT DATE_PART(year, CURRENT_TIMESTAMP());

Copy

Requête pour le mois en cours :

SELECT DATE_PART(month, CURRENT_TIMESTAMP());

Copy

Requête pour l’heure actuelle :

SELECT DATE_PART(hour, CURRENT_TIMESTAMP());

Copy

Requête pour la minute actuelle :

SELECT DATE_PART(minute, CURRENT_TIMESTAMP());

Copy

Requête pour la seconde actuelle :

SELECT DATE_PART(second, CURRENT_TIMESTAMP());

Copy

Vous pouvez également utiliser la fonction EXTRACT pour obtenir différentes parties de date et d’heure pour la date et l’heure actuelles.

Requête pour le jour actuel du mois :

SELECT EXTRACT('day', CURRENT_TIMESTAMP());

Copy

Requête pour l’année en cours :

SELECT EXTRACT('year', CURRENT_TIMESTAMP());

Copy

Requête pour le mois en cours :

SELECT EXTRACT('month', CURRENT_TIMESTAMP());

Copy

Requête pour l’heure actuelle :

SELECT EXTRACT('hour', CURRENT_TIMESTAMP());

Copy

Requête pour la minute actuelle :

SELECT EXTRACT('minute', CURRENT_TIMESTAMP());

Copy

Requête pour la seconde actuelle :

SELECT EXTRACT('second', CURRENT_TIMESTAMP());

Copy

Cette requête renvoie une sortie tabulaire avec différentes parties de date et d’heure pour la date et l’heure actuelles :

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;

Copy

+-------+-----+------+--------+--------+
| MONTH | DAY | HOUR | MINUTE | SECOND |
|-------+-----+------+--------+--------|
|     8 |  28 |    7 |     59 |     28 |
+-------+-----+------+--------+--------+

Calcul des dates et heures du calendrier ouvrable¶

Obtenez le premier jour du mois sous la forme d’une valeur DATE à l’aide de la fonction DATE_TRUNC. Par exemple, obtenez le premier jour du mois actuel :

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

Copy

Obtenez le dernier jour du mois actuel sous forme d’une valeur DATE à l’aide des fonctions DATEADD et DATE_TRUNC :

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

Copy

Pour une option différente, l’exemple suivant utilise DATE_TRUNC pour récupérer le début du mois actuel, ajoute un mois pour récupérer le début du mois suivant, puis soustrait un jour pour déterminer le dernier jour du mois actuel.

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

Copy

Obtenez le dernier jour du mois précédent sous forme d’une valeur DATE :

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

Copy

Obtenez le nom court en anglais (par exemple, « Jan », « Dec », etc.) pour le mois en cours :

SELECT TO_VARCHAR(CURRENT_DATE(), 'Mon');

Copy

Obtenez le nom du mois actuel en utilisant les noms de mois fournis explicitement :

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

Obtenez la date du lundi de la semaine en cours :

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

Copy

Obtenez la date du vendredi de la semaine en cours :

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

Copy

Obtenez la date du premier lundi du mois actuel à l’aide de la fonction DATE_PART :

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

Copy

Note

Dans la requête ci-dessus, la valeur 1 de 7 + 1 se traduit par lundi. Pour récupérer la date du premier mardi, mercredi, etc., remplacez 2, 3, etc., respectivement, par 7 pour Sunday.

Obtenez le premier jour de l’année actuelle sous forme d’une valeur DATE :

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

Copy

Obtenez le dernier jour de l’année actuelle sous forme d’une valeur DATE :

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

Copy

Obtenez le dernier jour de l’année précédente sous forme d’une valeur DATE :

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

Copy

Obtenez le premier jour du trimestre actuel sous forme d’une valeur DATE :

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

Copy

Obtenez le dernier jour du trimestre actuel sous forme d’une valeur DATE :

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

Copy

Obtenez la date et l’heure de minuit de la journée actuelle :

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

Copy

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

Incrémentation des valeurs de date et d’heure¶

Utilisez la fonction DATEADD pour incrémenter les valeurs de date et d’heure.

Ajoutez deux ans à la date actuelle :

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

Copy

Ajoutez deux jours à la date actuelle :

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

Copy

Ajoutez deux heures à la date et à l’heure actuelles :

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

Copy

Ajoutez deux minutes à la date et à l’heure actuelles :

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

Copy

Ajoutez deux secondes à la date et à l’heure actuelles :

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

Copy

Conversion de chaînes de caractères valides en dates, heures ou horodatages¶

Dans la plupart des cas, Snowflake gère correctement les valeurs de date et d’horodatage formatées en chaînes. Dans certains cas, comme pour les comparaisons basées sur des chaînes ou lorsqu’un résultat dépend d’un format d’horodatage issu de celui défini dans les paramètres de session, nous recommandons de convertir explicitement les valeurs au format souhaité pour éviter des résultats inattendus.

Par exemple, sans conversion explicite, comparer des valeurs de chaîne produit des résultats basés sur les chaînes :

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');

Copy

La requête suivante effectue une comparaison sans conversion explicite :

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';

Copy

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

La requête suivante effectue une comparaison avec une conversion explicite pour DATE :

SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::DATE;

Copy

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

Pour plus d’informations sur les fonctions de conversion, voir Formats de date et d’heure dans les fonctions de conversion.

Application du calcul de date aux chaînes de dates¶

Ajoutez cinq jours à la date exprimée dans une chaîne :

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

Copy

+-------------------------+
| ADD_FIVE_DAYS           |
|-------------------------|
| 2024-01-17 00:00:00.000 |
+-------------------------+

Vous pouvez calculer la différence en jours entre la date actuelle et la date exprimée dans une chaîne à l’aide de la fonction DATEDIFF.

Calculez la différence en jours en utilisant la fonction 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;

Copy

+-------------------------+
| TO_TIMESTAMP_DIFFERENCE |
|-------------------------|
|                     229 |
+-------------------------+

Calculez la différence en jours en utilisant la fonction 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;

Copy

+--------------------+
| TO_DATE_DIFFERENCE |
|--------------------|
|                229 |
+--------------------+

Ajouter un jour à une date spécifiée :

SELECT TO_DATE('2024-01-15') + 1 AS date_plus_one;

Copy

+---------------+
| DATE_PLUS_ONE |
|---------------|
| 2024-01-16    |
+---------------+

Soustrayez neuf jours de la date actuelle (par exemple, le 28 août 2024) :

SELECT CURRENT_DATE() - 9 AS date_minus_nine;

Copy

+-----------------+
| DATE_MINUS_NINE |
|-----------------|
| 2024-08-19      |
+-----------------+

Calcul des écarts entre les dates ou les heures¶

Calculez la différence entre la date actuelle et la date dans trois ans :

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

Copy

Calculez la différence entre la date actuelle et la date dans trois mois :

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

Copy

Calculez la différence entre la date actuelle et la date dans trois jours :

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

Copy

Calculez la différence entre l’heure actuelle et l’heure dans trois heures :

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

Copy

Calculez la différence entre l’heure actuelle et l’heure dans trois minutes :

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

Copy

Calculez la différence entre l’heure actuelle et l’heure dans trois secondes :

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

Copy

Création de vues de calendrier annuelles¶

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;

Copy

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