Fonctions de date et d’heure

Cette famille de fonctions peut être utilisée pour construire, convertir, extraire ou modifier des données DATE/TIME/TIMESTAMP.

Dans ce chapitre :

Liste des fonctions

Sous-catégorie

Fonction

Remarques

Construction

DATE_FROM_PARTS

TIME_FROM_PARTS

TIMESTAMP_FROM_PARTS

Extraction

DATE_PART

Accepte toutes les parties de date et d’heure (voir la section suivante pour plus de détails).

DAYNAME

EXTRACT

Alternative pour DATE_PART.

HOUR / MINUTE / SECOND

Alternative pour DATE_PART.

LAST_DAY

Accepte les parties de date pertinentes (voir la section suivante pour plus de détails).

MONTHNAME

NEXT_DAY

PREVIOUS_DAY

YEAR* / DAY* / WEEK* / MONTH / QUARTER

Alternative pour DATE_PART.

Addition/Soustraction

ADD_MONTHS

DATEADD

Accepte les parties de date et d’heure pertinentes (voir la section suivante pour plus de détails).

DATEDIFF

Accepte les parties de date et d’heure pertinentes (voir la section suivante pour plus de détails).

TIMEADD

Alias pour DATEADD.

TIMEDIFF

Alias pour DATEDIFF.

TIMESTAMPADD

Alias pour DATEADD.

TIMESTAMPDIFF

Alias pour DATEDIFF.

Troncature

DATE_TRUNC

Accepte les parties de date et d’heure pertinentes (voir la section suivante pour plus de détails).

TIME_SLICE

Permet à une heure d’être « arrondie » au début d’un intervalle régulier.

TRUNC

Alternative pour DATE_TRUNC.

Conversion

TO_DATE , DATE

TO_TIME , TIME

TO_TIMESTAMP / TO_TIMESTAMP_*

Fuseau horaire

CONVERT_TIMEZONE

Parties de date et d’heure prises en charge

Certaines fonctions (ainsi que leurs alias et alternatives appropriés) acceptent une partie de date ou d’heure comme argument. Les deux tableaux suivants énumèrent les parties (insensibles à la casse) qui peuvent être utilisées avec ces fonctions.

Parties de date

Abréviations/variations

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

year

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

month

mm , mon , mons , months

day

d , dd , days, dayofmonth

dayofweek [1]

weekday , dow , dw

dayofweekiso [2]

weekday_iso , dow_iso , dw_iso

dayofyear

yearday , doy , dy

week [1]

w , wk , weekofyear , woy , wy

weekiso [2]

week_iso , weekofyeariso , weekofyear_iso

quarter

q , qtr , qtrs , quarters

yearofweek [1]

yearofweekiso [2]

[1] Pour plus de détails sur l’utilisation, voir la section suivante, qui décrit comment Snowflake gère les semaines civiles et les jours de semaine.

[2] Non contrôlé par les paramètres de session WEEK_START et WEEK_OF_YEAR_POLICY, comme décrit dans la section suivante.

Parties d’heure

Abréviations/variations

DATEADD

DATEDIFF

DATE_PART

DATE_TRUNC

LAST_DAY

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

epoch_second

epoch , epoch_seconds

epoch_millisecond

epoch_milliseconds

epoch_microsecond

epoch_microseconds

epoch_nanosecond

epoch_nanoseconds

timezone_hour

tzh

timezone_minute

tzm

Semaines civiles et jours de semaine

Le comportement des fonctions liées aux semaines dans Snowflake est contrôlé par les paramètres de session WEEK_START et WEEK_OF_YEAR_POLICY. Un aspect important pour comprendre comment ces paramètres interagissent est le concept des semaines ISO.

Semaines ISO

Comme défini dans la norme de format de date et d’heure ISO 8601, les semaines ISO commencent toujours le lundi et « appartiennent » à l’année qui contient le jeudi de cette semaine. Cela signifie qu’un jour d’une année peut appartenir à une semaine d’une autre année :

  • Pour les premiers jours de janvier, la valeur WOY (semaine de l’année) peut être 52 ou 53 (c’est-à-dire que le jour appartient à la dernière semaine de l’année précédente).

  • Pour les derniers jours de décembre, la valeur WOY peut être 1 (c’est-à-dire que le jour appartient à la première semaine de l’année suivante).

Snowflake fournit un ensemble spécial de fonctions de date liées à la semaine (et des parties de données équivalentes) dont le comportement est cohérent avec la sémantique de la semaine ISO : DAYOFWEEKISO , WEEKISO , YEAROFWEEKISO.

Ces fonctions (et parties de date) ne tiennent pas compte des paramètres de session, (c’est-à-dire qu’elles suivent toujours la sémantique ISO).

Pour plus de détails sur le traitement de toutes les autres fonctions de date liées à la semaine, reportez-vous aux sections suivantes (dans ce chapitre).

Premier jour de la semaine

La plupart des fonctions liées à la semaine sont contrôlées uniquement par le paramètre de session WEEK_START. Notez que les résultats de la fonction diffèrent selon la façon dont le paramètre est défini :

Fonction

Paramètre réglé sur 0 (comportement par défaut/ancien)

Paramètre réglé sur 1 - 7 (lundi - dimanche)

DAY et DAYOFWEEK

Retourne 0 (dimanche) à 6 (samedi).

Retourne 1 (défini le premier jour de la semaine) à 7 (dernier jour de la semaine par rapport au premier jour défini).

DATE_TRUNC (avec une partie WEEK)

Tronque la semaine d’entrée pour commencer le lundi.

Tronque la semaine d’entrée pour commencer le premier jour défini de la semaine.

LAST_DAY (avec une partie WEEK)

Retourne le dimanche de la semaine d’entrée.

Retourne le dernier jour de la semaine d’entrée par rapport au premier jour défini de la semaine.

DATEDIFF (avec une partie WEEK)

Calculé sur la base des semaines à partir du lundi.

Calculé en utilisant les semaines commençant le premier jour défini de la semaine.

Astuce

La valeur par défaut du paramètre est 0, ce qui préserve le comportement existant de Snowflake (sémantique de type ISO) ; cependant, nous recommandons de changer cette valeur pour contrôler explicitement le comportement résultant des fonctions. D’après les commentaires que nous avons reçus, le scénario le plus courant consiste à régler le paramètre sur 1.

Première/dernière semaine de l’année

Le paramètre de session WEEK_OF_YEAR_POLICY contrôle le comportement des fonctions WEEK et YEAROFWEEK . Le paramètre peut avoir deux valeurs :

  • 0: Les fonctions liées à la semaine concernée utilisent une sémantique similaire à celle de la norme ISO. Une semaine appartient à une année donnée si au moins 4 jours de cette semaine sont compris dans cette année. Cela signifie que toutes les semaines ont 7 jours, mais les premiers jours de janvier et les derniers jours de décembre peuvent appartenir à une semaine dans une année différente. Pour cette raison, les deux fonctions YEAROFWEEK et YEAROFWEEKISO peuvent fournir l’année à laquelle la semaine appartient.

  • 1: Le 1er janvier commence toujours la première semaine de l’année et le 31 décembre correspond toujours à la dernière semaine de l’année. Cela signifie que la première et la dernière semaine de l’année peuvent avoir moins de 7 jours.

Notez que ce comportement est également influencé par le jour de début de la semaine, contrôlé par la valeur définie pour le paramètre de session WEEK_START :

  • 0 , 1: Le comportement est équivalent à la sémantique ISO d’une semaine, la semaine commençant le lundi.

  • de 2 à 7 : La logique des « 4 jours » est conservée, mais le premier jour de la semaine est différent.

Astuce

La valeur par défaut des deux paramètres est 0, ce qui préserve le comportement existant de Snowflake (sémantique similaire à la norme ISO) ; cependant, nous recommandons de changer ces valeurs pour contrôler explicitement le comportement résultant des fonctions. D’après les commentaires que nous avons reçus, le scénario le plus courant consiste à régler les deux paramètres sur 1.

Exemples

Note

Ces exemples interrogent le même ensemble de fonctions de date, mais avec des valeurs différentes pour les paramètres de session WEEK_OF_YEAR_POLICY et WEEK_START pour illustrer comment ils influencent les résultats des fonctions.

Contrôle du premier jour de la semaine

Le réglage de WEEK_START sur 0 (comportement existant) ou 1 (lundi) n’a pas d’effet significatif, comme l’illustrent les deux exemples suivants :

ALTER SESSION SET WEEK_START = 0;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   0 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   0 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

ALTER SESSION SET WEEK_START = 1;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   5 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2016-12-31 | Sat |   6 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-01 | Sun |   7 | 2016-12-26 | Mon       | 2017-01-01    | Sun          |                                  0 |
| 2017-01-02 | Mon |   1 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-03 | Tue |   2 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-04 | Wed |   3 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-01-05 | Thu |   4 | 2017-01-02 | Mon       | 2017-01-08    | Sun          |                                  1 |
| 2017-12-30 | Sat |   6 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
| 2017-12-31 | Sun |   7 | 2017-12-25 | Mon       | 2017-12-31    | Sun          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
  1. Avec WEEK_START défini sur 0, DOW pour le dimanche est 0.

  2. Avec WEEK_START défini sur 1, DOW pour le dimanche est 7.

Les résultats diffèrent plus significativement si WEEK_START est réglé sur un autre jour que le lundi. Par exemple, le réglage du paramètre sur 3 (mercredi) modifie les résultats de toutes les fonctions liées à la semaine (colonnes 3 à 8) :

ALTER SESSION SET WEEK_START = 3;

SELECT d "Date", DAYNAME(d) "Day",
       DAYOFWEEK(d) "DOW",
       DATE_TRUNC('week', d) "Trunc Date",
       DAYNAME("Trunc Date") "Trunc Day",
       LAST_DAY(d, 'week') "Last DOW Date",
       DAYNAME("Last DOW Date") "Last DOW Day",
       DATEDIFF('week', '2017-01-01', d) "Weeks Diff from 2017-01-01 to Date"
FROM week_examples;

+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+
| Date       | Day | DOW | Trunc Date | Trunc Day | Last DOW Date | Last DOW Day | Weeks Diff from 2017-01-01 to Date |
|------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------|
| 2016-12-30 | Fri |   3 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2016-12-31 | Sat |   4 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-01 | Sun |   5 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-02 | Mon |   6 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-03 | Tue |   7 | 2016-12-28 | Wed       | 2017-01-03    | Tue          |                                  0 |
| 2017-01-04 | Wed |   1 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-01-05 | Thu |   2 | 2017-01-04 | Wed       | 2017-01-10    | Tue          |                                  1 |
| 2017-12-30 | Sat |   4 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
| 2017-12-31 | Sun |   5 | 2017-12-27 | Wed       | 2018-01-02    | Tue          |                                 52 |
+------------+-----+-----+------------+-----------+---------------+--------------+------------------------------------+

Contrôle de l’année et des jours pour les première et dernière semaines de l’année

L’exemple suivant définit les deux paramètres sur 0 afin de suivre une sémantique de type ISO (c’est-à-dire que la semaine commence le lundi et que toutes les semaines ont 7 jours) :

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=0;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  52 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  52 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  52 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

L’exemple suivant illustre l’effet de maintenir WEEK_OF_YEAR_POLICY à 0, mais de remplacer WEEK_START par 3 (mercredi) :

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=0, WEEK_START=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |  53 |        52 | 2016 |      2016 |
| 2017-01-02 | Mon |  53 |         1 | 2016 |      2017 |
| 2017-01-03 | Tue |  53 |         1 | 2016 |      2017 |
| 2017-01-04 | Wed |   1 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   1 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  52 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  52 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  • 2016 compte maintenant 53 semaines (au lieu de 52).

  • WOY pour le 1er janvier 2017 passe à la semaine 53 (anciennement 52).

  • WOY pour les 2 et 3 janvier 2017 passe à la semaine 53 (anciennement 1).

  • YOW pour les 2 et 3 janvier 2017 passe à 2016 (anciennement 2017).

  • WOY (ISO) et YOW (ISO) ne sont pas concernés par le changement de paramètre.

Les deux derniers exemples définissent WEEK_OF_YEAR_POLICY sur 1 et WEEK_START en premier sur 1 (lundi) puis sur 3 (mercredi) :

ALTER_SESSION SET WEEK_OF_YEAR_POLICY=1, WEEK_START=1;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   2 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   2 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+

alter session set week_of_year_policy=1, week_start=3;

SELECT d "Date", DAYNAME(d) "Day",
       WEEK(d) "WOY",
       WEEKISO(d) "WOY (ISO)",
       YEAROFWEEK(d) "YOW",
       YEAROFWEEKISO(d) "YOW (ISO)"
FROM week_examples;

+------------+-----+-----+-----------+------+-----------+
| Date       | Day | WOY | WOY (ISO) |  YOW | YOW (ISO) |
|------------+-----+-----+-----------+------+-----------|
| 2016-12-30 | Fri |  53 |        52 | 2016 |      2016 |
| 2016-12-31 | Sat |  53 |        52 | 2016 |      2016 |
| 2017-01-01 | Sun |   1 |        52 | 2017 |      2016 |
| 2017-01-02 | Mon |   1 |         1 | 2017 |      2017 |
| 2017-01-03 | Tue |   1 |         1 | 2017 |      2017 |
| 2017-01-04 | Wed |   2 |         1 | 2017 |      2017 |
| 2017-01-05 | Thu |   2 |         1 | 2017 |      2017 |
| 2017-12-30 | Sat |  53 |        52 | 2017 |      2017 |
| 2017-12-31 | Sun |  53 |        52 | 2017 |      2017 |
+------------+-----+-----+-----------+------+-----------+
  1. Avec WEEK_OF_YEAR_POLICY défini sur 1 et WEEK_START défini sur 1 (lundi) :

    • WOY pour 2017-01-01 est 1.

    • La semaine 1 est composée d’une journée.

    • La semaine 2 commence le Mon.

    Notez qu’il s’agit du scénario d’utilisation le plus courant, basé sur les commentaires que nous avons reçus.

  2. Avec WEEK_OF_YEAR_POLICY défini sur 1 et WEEK_START défini sur 3 (mercredi) :

    • WOY pour le 01-01-2017 est toujours 1.

    • La semaine 1 est composée de 3 jours.

    • La semaine 2 commence le Wed.

Dans les deux exemples, WOY (ISO) et YOW (ISO) ne sont pas concernés par le changement de paramètre.