Types de données de date et heure

Cette rubrique décrit les types de données pris en charge dans Snowflake pour la gestion des dates, des heures et des horodatages (combinaison date + heure). Elle décrit également les formats pris en charge 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

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.). Tous les horodatages acceptés sont également valables pour des entrées de 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 supporte 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

TIMESTAMP dans Snowflake est un alias spécifié par l’utilisateur associé à l’une des variantes 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.

TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

Snowflake accepte trois variations d’horodatage. 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.

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

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

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. Ceci est particulièrement important à noter 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, 6 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 '2017-01-01 12:00:00'::TIMESTAMP_TZ;

-------------------------------------+
 '2017-01-01 12:00:00'::TIMESTAMP_TZ |
-------------------------------------+
 2017-01-01 12:00:00 -0800           |
-------------------------------------+

SELECT DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ);

--------------------------------------------------------+
 DATEADD(MONTH, 6, '2017-01-01 12:00:00'::TIMESTAMP_TZ) |
--------------------------------------------------------+
 2017-07-01 12:00:00 -0800                              |
--------------------------------------------------------+

Exemples d’horodatage

Créer une table à l’aide de différents horodatages :

-- First, use TIMESTAMP (mapped to TIMESTAMP_NTZ)

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP);

DESC TABLE ts_test;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

-- Next, explicitly use one of the TIMESTAMP variations (TIMESTAMP_LTZ)

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_LTZ);

DESC TABLE ts_test;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| TS   | TIMESTAMP_LTZ(9) | COLUMN | Y     | NULL    | N           | N          | 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('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that the time for January 2nd is 08:00 in Los Angeles (which is 16:00 in UTC)

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 08:00:00 -0800 |        8 |
+---------------------------------+----------+

-- Next, note that the times change with a different time zone

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 19:00:00 -0500 |       19 |
| Thu, 02 Jan 2014 11:00:00 -0500 |       11 |
+---------------------------------+----------+

Utiliser TIMESTAMP_NTZ :

CREATE OR REPLACE TABLE ts_test(ts timestamp_ntz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that both times from different time zones are converted to the same "wallclock" time

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------+----------+
| TS                        | HOUR(TS) |
|---------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 |       16 |
| Thu, 02 Jan 2014 16:00:00 |       16 |
+---------------------------+----------+

Utiliser TIMESTAMP_TZ :

CREATE OR REPLACE TABLE ts_test(ts timestamp_tz);

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';

INSERT INTO ts_test values('2014-01-01 16:00:00');
INSERT INTO ts_test values('2014-01-02 16:00:00 +00:00');

-- Note that the January 1st record inherited the session time zone,
-- and "America/Los_Angeles" was converted into a numeric time zone offset

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +0000 |       16 |
+---------------------------------+----------+

-- Next, note that changing the session time zone does not influence the results

ALTER SESSION SET TIMEZONE = 'America/New_York';

SELECT ts, hour(ts) FROM ts_test;

+---------------------------------+----------+
| TS                              | HOUR(TS) |
|---------------------------------+----------|
| Wed, 01 Jan 2014 16:00:00 -0800 |       16 |
| Thu, 02 Jan 2014 16:00:00 +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.

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 et raisonnables. Les formats de date et d’heure spécifiés manuellement peuvent inclure les éléments (insensibles à la casse) suivants :

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 réglé sur 1980, les valeurs de 79 et 80 sont respectivement analysées en tant que valeurs 2079 et 1980.

MM

Mois à deux chiffres (01=janvier, etc.).

MON

Nom abrégé du mois.

DD

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

DY

Jour de la semaine abrégé.

HH24

Deux chiffres pour l’heure (00 à 23) ; am/pm non autorisé.

HH12

Deux chiffres pour l’heure (01 à 12) ; am/pm autorisé.

AM , PM

Ante meridiem (am) / post meridiem (pm) ; pour une utilisation avec HH12.

MI

Deux chiffres pour la minute (00 à 59).

SS

Deux chiffres pour la seconde (00 à 59).

FF

Fractions de seconde avec une précision de 0 (secondes) à 9 (nanosecondes), par ex. 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 +/-.

Note

Lorsqu’un format de date comprenant uniquement une date est utilisé, l’heure associée est supposée être minuit ce jour-là.

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

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

CREATE TABLE gilkey_temporary(t  TIMESTAMP, 
                              t_tz TIMESTAMP_TZ,
                              t_ntz TIMESTAMP_NTZ,
                              t_ltz TIMESTAMP_LTZ);
INSERT INTO gilkey_temporary (t, t_tz, t_ntz, t_ltz) VALUES (
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789',
    '2020-03-12 01:02:03.123456789'
    );
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';
SELECT t, t_tz, t_ntz, t_ltz 
    FROM gilkey_temporary;
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
| T                             | T_TZ                          | T_NTZ                         | T_LTZ                         |
|-------------------------------+-------------------------------+-------------------------------+-------------------------------|
| 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 | 2020-03-12 01:02:03.123456789 |
+-------------------------------+-------------------------------+-------------------------------+-------------------------------+

Constantes de date et d’heure

Les constantes (aussi connues sous le nom de littéraux) font référence à 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 '2010-09-14'
time '10:03:56'
timestamp '2009-09-15 10:59:43'

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 '2011-10-29');

Constantes d’intervalle

Vous pouvez utiliser des constantes d’intervalle pour ajouter ou soustraire une période de temps 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> ] ... ]'

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 1 année.

  • INTERVAL '4 years, 5 months, 3 hours' représente 4 années, 5 mois et 3 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 (dans ce chapitre).

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

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' commence par ajouter/soustraire une année, puis un jour.

    • INTERVAL '1 day, 1 year' commence par ajouter/soustraire un jour, puis une année.

    Cela peut 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';
    
    +---------------------------------------------------+
    | TO_DATE ('2019-02-28') + INTERVAL '1 DAY, 1 YEAR' |
    |---------------------------------------------------|
    | 2020-03-01                                        |
    +---------------------------------------------------+
    
    SELECT TO_DATE ('2019-02-28') + INTERVAL '1 year, 1 day';
    
    +---------------------------------------------------+
    | TO_DATE ('2019-02-28') + INTERVAL '1 YEAR, 1 DAY' |
    |---------------------------------------------------|
    | 2020-02-29                                        |
    +---------------------------------------------------+
    
  • INTERVAL n’est pas un type de données (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.

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('2018-04-15') + INTERVAL '1 year';

+-------------------------------------------+
| TO_DATE('2018-04-15') + INTERVAL '1 YEAR' |
|-------------------------------------------|
| 2019-04-15                                |
+-------------------------------------------+

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

select to_time('04:15:29') + INTERVAL '3 hours, 18 minutes';

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

+-------------------------------+
| COMPLEX_INTERVAL              |
|-------------------------------|
| 2020-12-28 08:08:01.325 -0800 |
+-------------------------------+

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;

+-------------------------+
| COMPLEX_INTERVAL2       |
|-------------------------|
| 2027-03-30 07:31:32.841 |
+-------------------------+

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

Filtrer une colonne d’horodatage nommée ts d’une table nommée tl et ajouter 4 secondes à chaque valeur renvoyée :

select ts + INTERVAL '4 seconds' from t1 where ts > to_timestamp('2014-04-05 01:02:03');

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, Snowflake prend également en charge l’addition et la soustraction de base des jours aux valeurs DATE, sous la forme { + | - } <entier>, où <entier> 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 1 jour à une date spécifique :

select to_date('2018-04-15') + 1;

+---------------------------+
| TO_DATE('2018-04-15') + 1 |
|---------------------------|
| 2018-04-16                |
+---------------------------+

Soustraire 4 jours à partir d’une date précise :

select to_date('2018-04-15') - 4;

+---------------------------+
| TO_DATE('2018-04-15') - 4 |
|---------------------------|
| 2018-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;