Types de données de date et heure

Snowflake prend en charge les types de données pour la gestion des dates, des heures et des horodatages (date + heure combinées). Snowflake prend également en charge les formats pour les constantes de chaîne utilisées lors de la manipulation des dates, des heures et des horodatages.

Dans ce chapitre :

Types de données

Snowflake prend en charge les types de données de date et d’heure suivants :

Note

Pour les données DATE et TIMESTAMP, Snowflake recommande d’utiliser des années comprises entre 1582 et 9999. Snowflake accepte certaines années en dehors de cette plage, mais les années antérieures à 1582 doivent être évitées en raison des limitations du calendrier grégorien.

DATE

Snowflake prend en charge un seul type de données DATE pour stocker les dates (sans éléments horaires).

DATE accepte les dates sous les formes les plus courantes (YYYY-MM-DD, DD-MON-YYYY, etc.).

De plus, toutes les valeurs TIMESTAMP acceptées sont des entrées valides pour les dates ; cependant, les informations TIME sont tronquées.

DATETIME

DATETIME est un alias pour TIMESTAMP_NTZ.

TIME

Snowflake prend en charge un seul type de données TIME pour stocker les heures sous la forme de HH:MI:SS.

TIME prend en charge un paramètre de précision optionnel pour les fractions de seconde (par exemple TIME(3)). La précision temporelle peut varier de 0 (secondes) à 9 (nanosecondes). La précision par défaut est 9.

Toutes les valeurs de TIME doivent être comprises entre 00:00:00 et 23:59:59.999999999. TIME stocke en interne l’heure « wallclock », et toutes les opérations sur les valeurs TIME sont effectuées sans tenir compte d’aucun fuseau horaire.

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

Snowflake accepte trois variations d’horodatage.

TIMESTAMP_LTZ:

TIMESTAMP_LTZ stocke en interne l’heure UTC avec une précision spécifiée. Cependant, toutes les opérations sont effectuées dans le fuseau horaire de la session en cours, contrôlé par le paramètre de session TIMEZONE.

Alias pour TIMESTAMP_LTZ :

  • TIMESTAMPLTZ

  • TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP_NTZ:

TIMESTAMP_NTZ stocke en interne le temps « wallclock » avec une précision spécifiée. Toutes les opérations sont effectuées sans tenir compte d’aucun fuseau horaire.

Si le format de sortie contient un fuseau horaire, l’indicateur UTC (Z) est affiché.

TIMESTAMP_NTZ est la valeur par défaut pour TIMESTAMP.

Alias pour TIMESTAMP_NTZ :

  • TIMESTAMPNTZ

  • TIMESTAMP WITHOUT TIME ZONE

  • DATETIME

TIMESTAMP_TZ:

TIMESTAMP_TZ stocke en interne l’heure UTC ainsi qu’un décalage de fuseau horaire associé. Lorsqu’un fuseau horaire n’est pas fourni, le décalage de fuseau horaire de session est utilisé. Toutes les opérations sont effectuées avec le décalage de fuseau horaire spécifique à chaque enregistrement.

Alias pour TIMESTAMP_TZ :

  • TIMESTAMPTZ

  • TIMESTAMP WITH TIME ZONE

Les valeurs TIMESTAMP_TZ sont comparées en fonction de leur heure en UTC. Par exemple, la comparaison suivante entre différentes heures dans des fuseaux horaires différents renvoie TRUE, car les deux valeurs ont des heures équivalentes en UTC.

SELECT '2024-01-01 00:00:00 +0000'::TIMESTAMP_TZ = '2024-01-01 01:00:00 +0100'::TIMESTAMP_TZ;
Copy

Attention

TIMESTAMP_TZ ne stocke actuellement que le décalage d’un fuseau horaire donné au moment de la création d’une valeur donnée, et non le fuseau horaire réel. Cela est particulièrement important lorsqu’il s’agit de la période de l’heure d’été, qui n’est pas utilisée par UTC.

Par exemple, avec le paramètre TIMEZONE réglé sur "America/Los_Angeles", la conversion d’une valeur en TIMESTAMP_TZ en janvier d’une année donnée stocke un décalage horaire de -0800. Si plus tard, six mois sont ajoutés à la valeur, le décalage -0800 est conservé, même si en juillet le décalage pour Los Angeles est de -0700. En effet, une fois la valeur créée, l’information de fuseau horaire actuel ("America/Los_Angeles") n’est plus disponible. L’exemple de code suivant illustre ce comportement :

SELECT '2024-01-01 12:00:00'::TIMESTAMP_TZ;
Copy
+-------------------------------------+
| '2024-01-01 12:00:00'::TIMESTAMP_TZ |
|-------------------------------------|
| 2024-01-01 12:00:00.000 -0800       |
+-------------------------------------+

SELECT DATEADD(MONTH, 6, '2024-01-01 12:00:00'::TIMESTAMP_TZ);
Copy
+--------------------------------------------------------+
| DATEADD(MONTH, 6, '2024-01-01 12:00:00'::TIMESTAMP_TZ) |
|--------------------------------------------------------|
| 2024-07-01 12:00:00.000 -0800                          |
+--------------------------------------------------------+

TIMESTAMP

TIMESTAMP dans Snowflake est un alias spécifié par l’utilisateur associé à l’une des variations TIMESTAMP_*. Dans toutes les opérations où TIMESTAMP est utilisé, la variation TIMESTAMP_* spécifiée est automatiquement utilisée. Le type de données TIMESTAMP n’est jamais stocké dans les tables.

La variation TIMESTAMP_* associée à TIMESTAMP est spécifiée par le paramètre de session TIMESTAMP_TYPE_MAPPING. La valeur par défaut est TIMESTAMP_NTZ.

Toutes les variations d’horodatage, y compris l’alias TIMESTAMP, prennent en charge un paramètre de précision optionnel pour les secondes fractionnaires (par exemple TIMESTAMP(3)). La précision de l’horodatage peut varier de 0 (secondes) à 9 (nanosecondes). La précision par défaut est 9.

Exemples d’horodatage

Ces exemples créent une table en utilisant différents horodatages.

Tout d’abord, créez une table avec une colonne TIMESTAMP (mappée sur TIMESTAMP_NTZ) :

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP);

DESC TABLE ts_test;
Copy
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| TS   | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

Ensuite, utilisez explicitement l’une des variations TIMESTAMP (TIMESTAMP_LTZ) :

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);

DESC TABLE ts_test;
Copy
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| TS   | TIMESTAMP_LTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

Utiliser TIMESTAMP_LTZ avec des fuseaux horaires différents :

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');
Copy

Cette requête montre que l’heure du 2 janvier est 08h00 à Los Angeles (qui est 16h00 à UTC) :

SELECT ts, hour(ts) FROM ts_test;
Copy
+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 |       16 |
| 2024-01-02 08:00:00.000 -0800 |        8 |
+-------------------------------+----------+

Ensuite, notez que les horaires changent avec un fuseau horaire différent :

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, HOUR(ts) FROM ts_test;
Copy
+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 19:00:00.000 -0500 |       19 |
| 2024-01-02 11:00:00.000 -0500 |       11 |
+-------------------------------+----------+

Créez une table et utilisez TIMESTAMP_NTZ :

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_NTZ);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');
Copy

Notez que les deux heures de fuseaux horaires différents sont converties à la même heure « d’horloge murale » :

SELECT ts, HOUR(ts) FROM ts_test;
Copy
+-------------------------+----------+
| TS                      | HOUR(TS) |
|-------------------------+----------|
| 2024-01-01 16:00:00.000 |       16 |
| 2024-01-02 16:00:00.000 |       16 |
+-------------------------+----------+

Ensuite, notez que la modification du fuseau horaire de la session n’affecte pas les résultats :

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, HOUR(ts) FROM ts_test;
Copy
+-------------------------+----------+
| TS                      | HOUR(TS) |
|-------------------------+----------|
| 2024-01-01 16:00:00.000 |       16 |
| 2024-01-02 16:00:00.000 |       16 |
+-------------------------+----------+

Créez une table et utilisez TIMESTAMP_TZ :

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_TZ);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test VALUES('2024-01-01 16:00:00');
INSERT INTO ts_test VALUES('2024-01-02 16:00:00 +00:00');
Copy

Notez que l’enregistrement du 1er janvier a hérité du fuseau horaire de la session et que « Amérique/Los_Angeles » a été converti en décalage de fuseau horaire numérique :

SELECT ts, HOUR(ts) FROM ts_test;
Copy
+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 |       16 |
| 2024-01-02 16:00:00.000 +0000 |       16 |
+-------------------------------+----------+

Ensuite, notez que le changement du fuseau horaire de la session n’influence pas les résultats :

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, HOUR(ts) FROM ts_test;
Copy
+-------------------------------+----------+
| TS                            | HOUR(TS) |
|-------------------------------+----------|
| 2024-01-01 16:00:00.000 -0800 |       16 |
| 2024-01-02 16:00:00.000 +0000 |       16 |
+-------------------------------+----------+

Calendrier pris en charge

Snowflake utilise le calendrier grégorien pour toutes les dates et tous les horodatages. Le calendrier grégorien commence à l’année 1582, mais reconnaît les années précédentes. Il est important de noter que Snowflake n’ajuste pas les dates antérieures à 1582 (ou les calculs impliquant des dates antérieures à 1582) pour correspondre au calendrier julien. L’élément de format UUUU prend en charge les années négatives.

Formats de date et d’heure

Tous ces types de données acceptent la plupart des formats de date, d’heure ou de date + heure non ambigus. Voir Formats pris en charge pour la détection AUTO pour les formats que Snowflake reconnaît lorsque est configuré pour détecter le format automatiquement.

Vous pouvez également spécifier le format de la date et de l’heure manuellement. Lorsque vous spécifiez le format, vous pouvez utiliser les éléments non sensibles à la casse énumérés dans le tableau suivant :

Élément de format

Description

YYYY

Année à quatre chiffres.

YY

Année à deux chiffres, contrôlée par le paramètre de session TWO_DIGIT_CENTURY_START. Par exemple, lorsqu’il est défini sur 1980, les valeurs de 79 et 80 sont analysées comme 2079 et 1980 respectivement.

MM

Mois à deux chiffres (01 = janvier, et ainsi de suite).

MON

Nom complet ou abrégé du mois.

MMMM

Nom complet du mois.

DD

Jour du mois à deux chiffres (01 à 31).

DY

Jour de la semaine abrégé.

HH24

Deux chiffres pour l’heure (00 à 23). Vous ne devez pas spécifier AM / PM.

HH12

Deux chiffres pour l’heure (01 à 12). Vous pouvez spécifier AM / PM.

AM , PM

Ante meridiem (matin) (AM) / post meridiem (après-midi) (PM). À utiliser uniquement avec HH12 (pas avec HH24).

MI

Deux chiffres pour les minutes (00 à 59).

SS

Deux chiffres pour les secondes (00 à 59).

FF[0-9]

Fractions de secondes d’une précision de 0 (secondes) à 9 (nanosecondes), par exemple FF, FF0, FF3, FF9. Spécifier FF équivaut à FF9 (nanosecondes).

TZH:TZM , TZHTZM , TZH

Heure et minute du fuseau horaire, décalage par rapport à UTC. Peut être préfixé par un signe +/-.

UUUU

Année à quatre chiffres au format ISO, qui sont négatifs pour les années BCE.

Note

Exemples d’utilisation des formats de date et d’heure

L’exemple suivant utilise FF pour indiquer que la sortie a 9 chiffres dans le champ des secondes fractionnaires :

CREATE TABLE timestamp_demo_table(
  t TIMESTAMP,
  t_tz TIMESTAMP_TZ,
  t_ntz TIMESTAMP_NTZ,
  t_ltz TIMESTAMP_LTZ);
INSERT INTO timestamp_demo_table (t, t_tz, t_ntz, t_ltz) VALUES (
  '2024-03-12 01:02:03.123456789',
  '2024-03-12 01:02:03.123456789',
  '2024-03-12 01:02:03.123456789',
  '2024-03-12 01:02:03.123456789');
Copy
ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_TZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Copy
SELECT t, t_tz, t_ntz, t_ltz
  FROM timestamp_demo_table;
Copy
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| T                             | T_TZ                          | T_NTZ                         | T_LTZ                         |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 | 2024-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+

Constantes de date et d’heure

Les constantes (aussi connues sous le nom de littéraux) sont des valeurs de données fixes. Snowflake prend en charge la spécification de valeurs fixes de date, d’heure ou d’horodatage à l’aide de valeurs d’horodatage. Les constantes de chaînes doivent toujours être délimitées par des caractères de délimitation. Snowflake accepte l’utilisation de guillemets simples pour délimiter les constantes de chaîne de caractères.

Par exemple :

DATE '2024-08-14'
TIME '10:03:56'
TIMESTAMP '2024-08-15 10:59:43'
Copy

La chaîne est analysée en tant que valeur DATE, TIME ou TIMESTAMP en fonction du format d’entrée du type de données, défini via les paramètres suivants :

DATE:

DATE_INPUT_FORMAT

TIME:

TIME_INPUT_FORMAT

TIMESTAMP:

TIMESTAMP_INPUT_FORMAT

Par exemple, insérez une date spécifique dans une colonne à l’intérieur d’une table :

CREATE TABLE t1 (d1 DATE);

INSERT INTO t1 (d1) VALUES (DATE '2024-08-15');
Copy

Constantes d’intervalle

Vous pouvez utiliser des constantes d’intervalle pour ajouter ou soustraire la période d’une date, d’une heure ou d’un horodatage. Les constantes d’intervalle sont implémentées en utilisant le mot-clé INTERVAL, qui a la syntaxe suivante :

{ + | - } INTERVAL '<integer> [ <date_time_part> ] [ , <integer> [ <date_time_part> ] ... ]'
Copy

Comme pour toutes les constantes de chaîne, Snowflake requiert des guillemets simples pour délimiter les constantes d’intervalle.

Le mot-clé INTERVAL prend en charge un ou plusieurs autres entiers et, éventuellement, une ou plusieurs parties de date ou d’heure. Par exemple :

  • INTERVAL '1 year' représente une année.

  • INTERVAL '4 years, 5 months, 3 hours' représente quatre ans, cinq mois et trois heures.

Si aucune partie de date ou d’heure n’est spécifiée, l’intervalle représente les secondes (par exemple INTERVAL '2' équivaut à INTERVAL '2 seconds'). Notez que cela diffère de l’unité de temps par défaut pour l’exécution de l’arithmétique des dates. Pour plus de détails, voir Arithmétique simple pour les dates.

Pour consulter la liste des parties de date et d’heure prises en charge, voir Date et heure prises en charge pour les intervalles.

Note

  • L’ordre des incréments d’intervalle est important. Les incréments sont ajoutés ou soustraits dans l’ordre indiqué. Par exemple :

    • INTERVAL '1 year, 1 day' ajoute ou soustrait d’abord une année, puis un jour.

    • INTERVAL '1 day, 1 year' ajoute ou soustrait d’abord un jour, puis une année.

    Les différentes de tri peuvent affecter les calculs influencés par des événements du calendrier tels que les années bissextiles :

    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 day, 1 year';
    Copy
    +---------------------------------------------------+
| TO_DATE ('2019-02-28') + INTERVAL '1 DAY, 1 YEAR' |
|---------------------------------------------------|
| 2020-03-01                                        |
+---------------------------------------------------+

    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 year, 1 day';
    Copy
    +---------------------------------------------------+
| TO_DATE ('2019-02-28') + INTERVAL '1 YEAR, 1 DAY' |
|---------------------------------------------------|
| 2020-02-29                                        |
+---------------------------------------------------+

  • INTERVAL n’est pas un type de données (à savoir que vous ne pouvez pas définir une colonne de table comme étant du type de données INTERVAL). Les intervalles ne peuvent être utilisés que dans l’arithmétique de la date, de l’heure et de l’horodatage.

  • Vous ne pouvez pas utiliser un intervalle avec une variable SQL. Par exemple, la requête suivante renvoie une erreur :

    SET v1 = '1 year';

SELECT TO_DATE('2023-04-15') + INTERVAL $v1;
    Copy

Parties de date et d’heure prises en charge pour les intervalles

Le mot clé INTERVAL prend en charge les parties de date et heure suivantes comme arguments (non sensibles à la casse) :

Partie de date ou d’heure

Abréviations/variations

year

y , yy , yyy , yyyy , yr , years , yrs

quarter

q , qtr , qtrs , quarters

month

mm, mon, mons, months

week

w, wk, weekofyear, woy, wy, weeks

day

d , dd , days, dayofmonth

hour

h , hh , hr , hours , hrs

minute

m , mi , min , minutes , mins

second

s , sec , seconds , secs

millisecond

ms , msec , milliseconds

microsecond

us , usec , microseconds

nanosecond

ns, nsec, nanosec, nsecond, nanoseconds, nanosecs, nseconds

Exemples d’intervalle

Ajouter un intervalle d’une année à une date précise :

SELECT TO_DATE('2023-04-15') + INTERVAL '1 year';
Copy
+-------------------------------------------+
| TO_DATE('2023-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2024-04-15                                |
+-------------------------------------------+

Ajouter un intervalle de 3 heures et 18 minutes à une heure spécifique :

SELECT TO_TIME('04:15:29') + INTERVAL '3 hours, 18 minutes';
Copy
+------------------------------------------------------+
| TO_TIME('04:15:29') + INTERVAL '3 HOURS, 18 MINUTES' |
|------------------------------------------------------|
| 07:33:29                                             |
+------------------------------------------------------+

Ajouter un intervalle complexe à la sortie de la fonction CURRENT_TIMESTAMP :

SELECT CURRENT_TIMESTAMP + INTERVAL
    '1 year, 3 quarters, 4 months, 5 weeks, 6 days, 7 minutes, 8 seconds,
    1000 milliseconds, 4000000 microseconds, 5000000001 nanoseconds'
  AS complex_interval1;
Copy
+-------------------------------+
| COMPLEX_INTERVAL1             |
|-------------------------------|
| 2026-11-07 18:07:19.875000001 |
+-------------------------------+

Ajouter un intervalle complexe avec notation abrégée de la date et de l’heure à une date spécifique :

SELECT TO_DATE('2025-01-17') + INTERVAL
    '1 y, 3 q, 4 mm, 5 w, 6 d, 7 h, 9 m, 8 s,
    1000 ms, 445343232 us, 898498273498 ns'
  AS complex_interval2;
Copy
+-------------------------------+
| COMPLEX_INTERVAL2             |
|-------------------------------|
| 2027-03-30 07:31:32.841505498 |
+-------------------------------+

Interroger une table d’informations relatives aux employés et renvoyer les noms des employés qui ont été embauchés au cours des deux dernières années et des trois derniers mois :

SELECT name, hire_date
  FROM employees
  WHERE hire_date > CURRENT_DATE - INTERVAL '2 y, 3 month';
Copy

Filtrer une colonne TIMESTAMP nommée ts d’une table nommée t1 et ajouter quatre secondes à chaque valeur renvoyée :

SELECT ts + INTERVAL '4 seconds'
  FROM t1
  WHERE ts > TO_TIMESTAMP('2024-04-05 01:02:03');
Copy

Arithmétique simple pour les dates

En plus d’utiliser des constantes d’intervalle pour ajouter et soustraire des dates, des heures et des horodatages, vous pouvez également ajouter et soustraire des jours aux valeurs DATE, sous la forme { + | - } integer, où integer spécifie le nombre de jours à ajouter/soustraire.

Note

Les valeurs TIME et TIMESTAMP ne prennent pas encore en charge l’arithmétique simple.

Exemples arithmétiques de date

Ajouter un jour à une date spécifique :

SELECT TO_DATE('2024-04-15') + 1;
Copy
+---------------------------+
| TO_DATE('2024-04-15') + 1 |
|---------------------------|
| 2024-04-16                |
+---------------------------+

Soustraire quatre jours à une date précise :

SELECT TO_DATE('2024-04-15') - 4;
Copy
+---------------------------+
| TO_DATE('2024-04-15') - 4 |
|---------------------------|
| 2024-04-11                |
+---------------------------+

Interroger une table nommée employees et renvoyer les noms des personnes qui ont quitté l’entreprise mais qui ont été employées pendant plus de 365 jours :

SELECT name
  FROM employees
  WHERE end_date > start_date + 365;
Copy
Langage: Français