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 :

Dates et horodatages de chargement

Horodatages de chargement sans fuseau horaire joint

Dans l’exemple suivant, le paramètre TIMESTAMP_TYPE_MAPPING est réglé sur TIMESTAMP_LTZ (fuseau horaire local). Le paramètre TIMEZONE est réglé sur l’heure Amérique/Chicago. Si un ensemble d’horodatages entrants n’a 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.

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

Horodatages de chargement avec un fuseau horaire joint

Dans l’exemple suivant, le paramètre TIMESTAMP_TYPE_MAPPING est réglé sur TIMESTAMP_LTZ (fuseau horaire local). Le paramètre TIMEZONE est réglé sur l’heure Amérique/Chicago. Supposons qu’un ensemble d’horodatages entrants ait un fuseau horaire différent spécifié. Snowflake charge la chaîne à l’heure Amérique/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 ('2016-04-30 19:00:00.000 -0800');

SELECT * FROM time;

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

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 ('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

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

Cet exemple montre comment insérer une valeur dans une colonne 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

La fonction TO_DATE accepte les valeurs TIMESTAMP et même 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('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

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;
+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+
Copy

Lorsque vous récupérez des informations de DATE, vous pouvez les formater en TIMESTAMP si vous le souhaitez :

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

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.

Obtenez le jour de la semaine en cours sous la forme d’une chaîne à l’aide de la fonction TO_VARCHAR ou 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

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

Obtenez diverses parties de date et d’heure pour la date et l’heure actuelles à l’aide de la fonction 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

Autre solution - la fonction 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

Autre sortie tabulaire :

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

Calcul des dates et heures du calendrier d’opérations

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

Autre option. Dans l’exemple suivant, DATE_TRUNC récupère le début du mois actuel, ajoute un mois pour récupérer le début du mois suivant, puis soustrait 1 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 mois actuel de l’année par nom :

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

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

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

Incrémentation des 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 différent 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 :

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

Pour plus d’informations sur les fonctions de conversion, voir Formats de date et 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-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

Calculez la différence en jours entre la date actuelle et la date exprimée dans une chaîne à l’aide de la fonction 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

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

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

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

Soustraire 9 jours à la date actuelle (par ex. : 21 janvier 2019) :

SELECT CURRENT_DATE() - 9;

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

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;

+-----+------------+-----------------+-----------+---------+
|   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
Langage: Français