Fonctions de date en SQL standard

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 est 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. Le paramètre part doit correspondre à l'une des valeurs suivantes :

  • 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 le jour spécifié par WEEKDAY. Les dates antérieures au premier jour 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 semaines ISOWEEK commencent le lundi. Les valeurs renvoyées sont comprises dans la plage [1, 53]. La première semaine 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 conforme à l'ISO 8601, qui correspond à l'année grégorienne contenant le jeudi de la semaine à laquelle date_expression appartient.

Type de données renvoyées

INT64

Exemples

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

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

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

Dans l'exemple suivant, EXTRACT renvoie des valeurs correspondant aux différentes parties de date 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])
3. DATE(datetime_expression)

Description

  1. Construit une DATE à partir de valeurs INT64 représentant l'année, le mois et le jour.
  2. Extrait la DATE d'une expression TIMESTAMP. 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é.
  3. Extrait la DATE d'une expression DATETIME.

Type de données renvoyé

DATE

Exemple

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

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

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Description

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

La fonction DATE_ADD est compatible avec les valeurs date_part suivantes :

  • DAY
  • WEEK. Équivaut à sept jours (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ées

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_expression date_part)

Description

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

La fonction DATE_SUB est compatible avec les valeurs date_part suivantes :

  • DAY
  • WEEK. Équivaut à sept jours (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ées

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_a, date_expression_b, date_part)

Description

Renvoie le nombre d'intervalles date_part entiers spécifiés entre deux objets DATE (date_expression_a – date_expression_b). Si la première DATE est antérieure à la seconde, la sortie est négative.

La fonction DATE_DIFF est compatible avec les valeurs date_part suivantes :

  • DAY
  • WEEK : cette partie de date commence le dimanche.
  • WEEK(<WEEKDAY>) : cette partie de date commence le jour de la semaine désigné par WEEKDAY. Les valeurs valides 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 : utilise la limite d'année à numérotation de semaines ISO 8601. La limite d'année ISO est le lundi de la première semaine où le jeudi appartient à l'année civile grégorienne correspondante.

Type de données renvoyées

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. 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 est compatible avec 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 le jour désigné par 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 semaine ISOWEEK de chaque année ISO englobe le premier jeudi de l'année civile grégorienne correspondante. Toute date_expression précédente sera tronquée au lundi précédent.
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR : tronque date_expression à la limite de l'année à numérotation de semaine ISO 8601 précédente. La limite d'année ISO est le lundi de la première semaine où le jeudi appartient à l'année civile grégorienne correspondante.

Type de données renvoyées

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 correspondant au 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, le code d'origine date_expression date de 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 1er janvier 2015 (2015-01-01), donc l'année ISO 2015 commence le lundi précédent, soit le 29 décembre 2014 (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 29 décembre 2014 (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 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 un objet date_expr en fonction du paramètre format_string spécifié.

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

Type de données renvoyées

STRING

Exemples

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

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

LAST_DAY

LAST_DAY(date_expression[, date_part])

Description

Renvoie le dernier jour d'une expression de date. Cette fonction est généralement utilisée pour renvoyer le dernier jour du mois.

Vous pouvez également spécifier la partie de date pour laquelle le dernier jour est renvoyé. Si ce paramètre n'est pas utilisé, la valeur par défaut est MONTH. LAST_DAY est compatible avec les valeurs suivantes pour date_part :

  • YEAR
  • QUARTER
  • MONTH
  • WEEK. Équivaut à sept jours (DAY).
  • WEEK(<WEEKDAY>). <WEEKDAY> représente le jour de début de la semaine. Les valeurs valides sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY et SATURDAY.
  • ISOWEEK. Utilise les limites de semaine ISO 8601. Les semaines ISO commencent le lundi.
  • ISOYEAR. Utilise la limite d'année à 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é

DATE

Exemple

Ces deux exemples renvoient le dernier jour du mois :

SELECT LAST_DAY(DATE '2008-11-25', MONTH) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+
SELECT LAST_DAY(DATE '2008-11-25') AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+

Cet exemple renvoie le dernier jour de l'année :

SELECT LAST_DAY(DATE '2008-11-25', YEAR) AS last_day

+------------+
| last_day   |
+------------+
| 2008-12-31 |
+------------+

Cet exemple renvoie le dernier jour d'une semaine commençant un dimanche :

SELECT LAST_DAY(DATE '2008-11-10', WEEK(SUNDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-15 |
+------------+

Cet exemple renvoie le dernier jour d'une semaine commençant un lundi :

SELECT LAST_DAY(DATE '2008-11-10', WEEK(MONDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-16 |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

Description

Convertit une représentation de la date sous forme de chaîne en objet DATE.

format_string contient les éléments de format qui définissent le format de date_string. Chaque élément de date_string doit avoir un élément correspondant dans format_string. L'emplacement de chaque élément dans format_string doit correspondre à celui de chaque élément dans date_string.

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

La chaîne de format est entièrement compatible avec la plupart des éléments de format, à l'exception de %Q, %a, %A, %g, %G, %j, %u, %U, %V, %w et %W.

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 1970-01-01.
  • Noms qui ne sont pas sensibles à la casse : les 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.

Type de données renvoyé

DATE

Exemples

Cet exemple convertit une chaîne au format MM/DD/YY en objet DATE :

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

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

Cet exemple convertit une chaîne au format YYYYMMDD en objet DATE :

SELECT PARSE_DATE("%Y%m%d", "20081225") 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.
%Q Trimestre sous forme de nombre décimal (1-4).
%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 ISO 8601 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 correspondent aux années 2000, tandis que les années 69-99 correspondent aux 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.