Types de données de date et heure¶

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.

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.

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

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.

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

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 '2021-01-01 00:00:00 +0000'::timestamp_tz = '2021-01-01 01:00:00 +0100'::timestamp_tz;

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

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

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

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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