Date Functions in Standard SQL

BigQuery accepte les fonctions DATE ci-après.

CURRENT_DATE

CURRENT_DATE([time_zone])

Description

Renvoie la date actuelle pour le fuseau horaire spécifié ou celui par défaut.

Cette fonction accepte un paramètre time_zone (facultatif). Ce paramètre est une chaîne représentant le fuseau horaire à utiliser. Si aucun fuseau horaire n'est spécifié, le fuseau horaire par défaut (UTC) est utilisé. Pour en savoir plus sur la spécification d'un fuseau horaire, consultez la section Définition de fuseaux horaires.

Si l'évaluation du paramètre time_zone donne NULL, cette fonction renvoie NULL.

Type de données renvoyé

DATE

Exemple

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

Description

Renvoie la valeur correspondant à la partie de la date spécifiée. La valeur part doit être l'un des éléments suivants :

  • DAYOFWEEK : renvoie des valeurs comprises dans la plage [1,7], le dimanche étant considéré comme le premier jour de la semaine.
  • DAY
  • DAYOFYEAR
  • WEEK : renvoie le numéro de semaine de la date (compris dans la plage [0, 53]). Les semaines commencent le dimanche et les dates antérieures au premier dimanche de l'année correspondent à la semaine 0.

  • WEEK(<WEEKDAY>) : renvoie le numéro de semaine de la date (compris dans la plage [0, 53]). Les semaines commencent un jour de semaine (WEEKDAY). Les dates antérieures au premier WEEKDAY de l'année correspondent à la semaine 0. Les valeurs valides pour WEEKDAY sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY et SATURDAY.

  • ISOWEEK : renvoie le numéro de semaine ISO 8601 de date_expression. Les ISOWEEK commencent le lundi. Les valeurs renvoyées sont comprises dans la plage [1, 53]. La première ISOWEEK de chaque année ISO commence le lundi précédant le premier jeudi de l'année civile grégorienne.

  • MONTH

  • QUARTER : renvoie des valeurs comprises dans la plage [1,4].

  • YEAR

  • ISOYEAR : renvoie l'année à numérotation de semaine ISO 8601, qui correspond à l'année civile grégorienne contenant le jeudi de la semaine à laquelle date_expression appartient.

Type de données renvoyé

INT64

Exemples

Dans l'exemple suivant, EXTRACT renvoie une valeur correspondant à la partie "Jour" (DAY) de la date.

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

Dans l'exemple suivant, EXTRACT renvoie des valeurs correspondant à différents éléments de temps d'une colonne de dates situées vers la fin de l'année.

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

Dans l'exemple suivant, date_expression tombe un dimanche. EXTRACT calcule la première colonne à l'aide de semaines commençant le dimanche, et la seconde colonne à l'aide de semaines commençant le lundi.

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

Description

  1. Construit une DATE à partir de valeurs INT64 représentant l'année, le mois et le jour.
  2. Convertit une expression timestamp_expression en un type de données DATE. Cette étape accepte un paramètre facultatif permettant de spécifier un fuseau horaire. Si aucun fuseau horaire n'est spécifié, le fuseau horaire par défaut (UTC) est utilisé.

Type de données renvoyé

DATE

Exemple

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

Description

Ajoute un intervalle de temps spécifié à une DATE.

DATE_ADD accepte les valeurs date_part suivantes :

  • DAY
  • WEEK (équivalent à 7 DAY).
  • MONTH
  • QUARTER
  • YEAR

Une manipulation particulière est requise pour les parties MONTH (mois), QUARTER (trimestre) et YEAR (année) lorsque la date correspond au dernier jour du mois (ou à un jour très proche). Si le mois résultant ne comporte pas suffisamment de jours pour inclure le jour de la date d'origine, le jour renvoyé en résultat est le dernier jour du nouveau mois.

Type de données renvoyé

DATE

Exemple

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

Description

Soustrait un intervalle de temps spécifié d'une DATE.

DATE_SUB accepte les valeurs date_part suivantes :

  • DAY
  • WEEK (équivalent à 7 DAY).
  • MONTH
  • QUARTER
  • YEAR

Une manipulation particulière est requise pour les parties MONTH (mois), QUARTER (trimestre) et YEAR (année) lorsque la date correspond au dernier jour du mois (ou à un jour très proche). Si le mois résultant ne comporte pas suffisamment de jours pour inclure le jour de la date d'origine, le jour renvoyé en résultat est le dernier jour du nouveau mois.

Type de données renvoyé

DATE

Exemple

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

Description

Renvoie le nombre de limites date_part entre les deux expressions date_expression. Si la première date précède la deuxième, le résultat est "non positif".

DATE_DIFF accepte les valeurs date_part suivantes :

  • DAY
  • WEEK (cette partie de la date commence le dimanche).
  • WEEK(<WEEKDAY>) : cette partie de la date commence un jour de semaine WEEKDAY. Les valeurs admises pour WEEKDAY sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY et SATURDAY.
  • ISOWEEK : utilise les limites de semaine ISO 8601. Les semaines ISO commencent le lundi.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR : renvoie la limite d'année par numérotation de semaines ISO 8601. La limite d'année ISO est le lundi de la première semaine dont le jeudi appartient à l'année civile grégorienne correspondante.

Type de données renvoyé

INT64

Exemple

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+

SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

L'exemple ci-dessus montre le résultat de DATE_DIFF pour deux jours consécutifs. L'opération DATE_DIFF avec la partie de date WEEK renvoie 1, car DATE_DIFF compte le nombre de limites de partie de date dans cette plage de dates. Chaque WEEK commence le dimanche. Il existe donc une limite de partie de date entre le samedi (2017-10-14) et le dimanche (2017-10-15).

L'exemple suivant montre le résultat de DATE_DIFF pour deux dates avec des années différentes. La fonction DATE_DIFF avec la partie de date YEAR renvoie 3, car elle compte le nombre de limites de l'année civile grégorienne entre les deux dates. DATE_DIFF avec la partie de date ISOYEAR renvoie 2, car la seconde date appartient à l'année ISO 2015. Le premier jeudi de l'année civile 2015 était le 2015-01-01, donc l'année ISO 2015 commence le lundi précédent, soit le 2014-12-29.

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

L'exemple suivant montre le résultat de DATE_DIFF pour deux jours consécutifs. La première date tombe un lundi et la seconde un dimanche. DATE_DIFF avec la partie de date WEEK renvoie 0, car cette partie utilise des semaines commençant le dimanche. DATE_DIFF avec la partie de date WEEK(MONDAY) renvoie 1. DATE_DIFF avec la partie de date ISOWEEK renvoie également 1, car les semaines ISO commencent le lundi.

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

Description

Tronque la date selon le niveau de précision spécifié.

DATE_TRUNC accepte les valeurs suivantes pour date_part :

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>) : tronque date_expression à la limite de la semaine précédente, où les semaines commencent un jour de semaine (WEEKDAY). Les valeurs valides pour WEEKDAY sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY et SATURDAY.
  • ISOWEEK : tronque date_expression à la limite de la semaine ISO 8601 précédente. Les semaines ISOWEEK commencent le lundi. La première ISOWEEK de chaque année ISO englobe le premier jeudi de l'année civile grégorienne correspondante. Toute expression date_expression antérieure à celle-ci sera tronquée au lundi précédent.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR : tronque date_expression à la limite de l'année en numérotation de semaine ISO 8601 précédente. La limite d'année ISO est le lundi de la première semaine dont le jeudi appartient à l'année civile grégorienne correspondante.

Type de données renvoyé

DATE

Exemples

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

Dans l'exemple suivant, la date d'origine tombe un dimanche. Étant donné que date_part a la valeur WEEK(MONDAY), DATE_TRUNC renvoie l'objet DATE pour le lundi précédent.

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

Dans l'exemple suivant, l'expression date_expression d'origine se trouve dans l'année civile grégorienne 2015. En revanche, DATE_TRUNC avec la partie de date ISOYEAR tronque date_expression au début de l'année ISO, et non de l'année civile grégorienne. Le premier jeudi de l'année civile 2015 était le 2015-01-01, donc l'année ISO 2015 commence le lundi précédent, soit le 2014-12-29. Par conséquent, la limite de l'année ISO précédant l'expression date_expression 2015-06-15 est le 2014-12-29.

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

Description

Interprète INT64_expression comme le nombre de jours écoulés depuis le 1er janvier 1970 (1970-01-01).

Type de données renvoyé

DATE

Exemple

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

Description

Formate date_expr en fonction de la chaîne de format (format_string) spécifiée.

Consultez la section Éléments de format acceptés pour DATE pour obtenir une liste des éléments de format acceptés par cette fonction.

Type de données renvoyé

STRING

Exemple

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Description

Utilise une chaîne de format (format_string) et la représentation d'une date sous forme de chaîne pour renvoyer un objet DATE.

Lorsque vous utilisez PARSE_DATE, tenez compte des points suivants :

  • Champs non spécifiés : tout champ non spécifié est initialisé à compter de la date par défaut 1970-01-01.
  • Noms qui ne sont pas sensibles à la casse : certains noms (tels que Monday, February, etc.) ne sont pas sensibles à la casse.
  • Espace blanc : un ou plusieurs espaces blancs consécutifs dans la chaîne de format correspondent à zéro ou plusieurs espaces blancs consécutifs dans la chaîne de date. En outre, les espaces blancs au début et à la fin de la chaîne de date sont toujours autorisés, même s'ils ne figurent pas dans la chaîne de format.
  • Priorité de format : lorsqu'au moins deux éléments de format incluent des informations qui se chevauchent (par exemple, %F et %Y affectent tous deux l'année), le dernier d'entre eux remplace généralement les éléments précédents.

Consultez la section Éléments de format acceptés pour DATE pour obtenir une liste des éléments de format acceptés par cette fonction.

Type de données renvoyé

DATE

Exemple

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

Description

Renvoie le nombre de jours écoulés depuis le 1er janvier 1970 (1970-01-01).

Type de données renvoyé

INT64

Exemple

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

Éléments de format acceptés pour DATE

Sauf indication contraire, les fonctions de date utilisant des chaînes de format acceptent les éléments suivants :

Élément de format Description
%A Nom complet du jour de la semaine.
%a Nom du jour de la semaine sous forme abrégée.
%B Nom complet du mois.
%b ou %h Nom du mois sous forme abrégée.
%C Siècle (une année divisée par 100 et tronquée pour obtenir un entier) sous forme de nombre décimal (00-99).
%D Date au format %m/%d/%y.
%d Jour du mois sous forme de nombre décimal (01-31).
%e Jour du mois sous forme de nombre décimal (1-31) ; les valeurs à un seul chiffre (1-9) sont précédées d'une espace.
%F Date au format %Y-%m-%d.
%G Année ISO 8601 (avec le siècle) sous forme de nombre décimal. Chaque année ISO commence le lundi précédant le premier jeudi de l'année civile grégorienne. Notez que %G et %Y peuvent produire des résultats différents près des limites de l'année grégorienne, où celle-ci et l'année ISO peuvent diverger.
%g Année ISO 8601 (sans le siècle) sous forme de nombre décimal (00-99). Chaque année ISO commence le lundi précédant le premier jeudi de l'année civile grégorienne. Notez que %g et %y peuvent produire des résultats différents près des limites de l'année grégorienne, où celle-ci et l'année ISO peuvent diverger.
%j Jour de l'année sous forme de nombre décimal (001-366).
%m Mois sous forme de nombre décimal (01-12).
%n Caractère de nouvelle ligne.
%t Caractère de tabulation.
%U Numéro de la semaine dans l'année (dimanche considéré comme premier jour de la semaine) sous forme de nombre décimal (00-53).
%u Jour de la semaine (lundi considéré comme premier jour de la semaine) sous forme de nombre décimal (1-7).
%V Numéro de la semaine dans l'année (lundi considéré comme premier jour de la semaine) sous forme de nombre décimal (01-53). Si la semaine du 1er janvier compte quatre jours ou plus dans la nouvelle année, elle est considérée comme la semaine 1. Dans le cas contraire, elle est considérée comme semaine 53 de l'année précédente et la semaine qui suit est la semaine 1.
%W Numéro de la semaine dans l'année (lundi considéré comme premier jour de la semaine) sous forme de nombre décimal (00-53).
%w Jour de la semaine (dimanche considéré comme premier jour de la semaine) sous forme de nombre décimal (0-6).
%x Représentation de la date au format MM/JJ/AA.
%Y Année (avec le siècle) sous forme de nombre décimal.
%y Année (sans le siècle) sous forme de nombre décimal (00-99), le premier zéro est facultatif. Peut être associé à %C. Si %C n'est pas spécifié, les années 00-68 sont considérées comme les années 2000 et les années 69-99 sont les années 1900.
%E4Y Années sous forme de nombres à quatre caractères (0001 ... 9999). Notez que %Y produit autant de caractères que nécessaire pour un rendu complet de l'année.
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.