Fonctions Datetime en langage SQL standard

Les fonctions DATETIME suivantes sont possibles avec BigQuery.

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

Description

Renvoie l'heure actuelle en tant qu'objet DATETIME.

Cette fonction accepte un paramètre timezone facultatif. Pour en savoir plus sur la spécification d'un fuseau horaire, consultez la section Définition de fuseaux horaires.

Type de données renvoyé

DATETIME

Exemple

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression, time_expression)
3. DATETIME(timestamp_expression [, timezone])

Description

  1. Construit un objet DATETIME (date et heure) à partir de valeurs INT64 représentant l'année, le mois, le jour, l'heure, la minute et la seconde.
  2. Construit un objet DATETIME à l'aide d'un objet DATE et d'un objet TIME.
  3. Construit un objet DATETIME à l'aide d'un objet 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é.

Type de données renvoyé

DATETIME

Exemple

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

Description

Ajoute les unités INT64_expr de part à l'objet DATETIME.

DATETIME_ADD accepte les valeurs suivantes pour part :

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • 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 l'objet DATETIME d'origine, le jour renvoyé en résultat est le dernier jour du nouveau mois.

Type de données renvoyé

DATETIME

Exemple

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

Description

Soustrait les unités INT64_expr de part de l'objet DATETIME.

DATETIME_SUB accepte les valeurs suivantes pour part :

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • 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 l'objet DATETIME d'origine, le jour renvoyé en résultat est le dernier jour du nouveau mois.

Type de données renvoyé

DATETIME

Exemple

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

Description

Renvoie le nombre de limites part entre les deux expressions datetime_expression. Si le premier objet DATETIME est antérieur au second objet DATETIME, le résultat est "non positif". La fonction renvoie une erreur si le calcul excède le type du résultat, par exemple si la différence en microsecondes entre les deux objets DATETIME dépasse une valeur INT64.

DATETIME_DIFF accepte les valeurs suivantes pour part :

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • 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 : utilise 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
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07 10:20:00        | 2008-12-25 15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+

SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

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

L'exemple ci-dessus montre le résultat de DATETIME_DIFF pour deux objets DATETIME espacés de 24 heures. DATETIME_DIFF avec la partie WEEK renvoie 1, car DATETIME_DIFF compte le nombre de limites de partie dans cette plage d'objets DATETIME. Chaque WEEK commence le dimanche. Il existe donc une limite de partie entre le samedi (2017-10-14 00:00:00) et le dimanche (2017-10-15 00:00:00).

L'exemple suivant montre le résultat de DATETIME_DIFF pour deux dates avec des années différentes. La fonction DATETIME_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 objets DATETIME. DATETIME_DIFF avec la partie de date ISOYEAR renvoie 2, car le second objet DATETIME 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
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', YEAR) AS year_diff,
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;

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

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

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

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

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Description

Tronque un objet DATETIME selon le niveau de précision de part.

DATETIME_TRUNC accepte les valeurs suivantes pour la part :

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>) : tronque l'expression datetime_expression à la limite de la semaine précédente, les semaines commençant le WEEKDAY. Les valeurs valides pour WEEKDAY sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY et SATURDAY.
  • ISOWEEK : tronque datetime_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 datetime_expression à la limite d'année par numérotation de semaines 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é

DATETIME

Exemples

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25 15:30:00        | 2008-12-25 00:00:00    |
+----------------------------+------------------------+

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

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP '2017-11-05 00:00:00') AS datetime);

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

Dans l'exemple suivant, l'expression datetime_expression d'origine se trouve dans l'année civile grégorienne 2015. En revanche, DATETIME_TRUNC avec la partie de date ISOYEAR tronque datetime_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 datetime_expression 2015-06-15 00:00:00 est le 2014-12-29.

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

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

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Description

Formate un objet DATETIME en fonction du paramètre format_string spécifié. Consultez la section Éléments de format acceptés pour DATETIME pour obtenir une liste des éléments de format acceptés par cette fonction.

Type de données renvoyé

STRING

Exemple

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

Description

Utilise un paramètre format_string et une représentation de type STRING d'un objet DATETIME pour renvoyer un objet DATETIME. Consultez la section Éléments de format acceptés pour DATETIME pour obtenir une liste des éléments de format acceptés par cette fonction.

PARSE_DATETIME analyse l'élément string selon les règles suivantes :

  • Champs non spécifiés : tout champ non spécifié est initialisé à compter du 1970-01-01 00:00:00.0. Par exemple, si l'année n'est pas spécifiée, la valeur par défaut est 1970.
  • Noms qui ne sont pas sensibles à la casse : certains noms, tels que Monday et February, ne sont pas sensibles à la casse.
  • Espace blanc : un ou plusieurs espaces blancs consécutifs dans la chaîne de format correspondent à zéro, un ou plusieurs espaces blancs consécutifs dans la chaîne DATETIME. Les espaces blancs au début et à la fin de la chaîne DATETIME 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, le dernier d'entre eux remplace généralement les éléments précédents, à quelques exceptions près. Par exemple, %F et %Y affectent tous deux l'année, donc le plus ancien des deux écrase le plus récent. Consultez les descriptions de %s, %C et %y dans la section Éléments de format acceptés pour DATETIME.

Exemples

L'exemple suivant analyse un littéral de type STRING en tant que DATETIME.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

La requête ci-dessus renvoie le résultat suivant :

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18 13:45:55 |
+---------------------+

L'exemple suivant analyse un littéral de type STRING contenant une date au format de langage naturel en tant que DATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
  AS datetime;

La requête ci-dessus renvoie le résultat suivant :

+---------------------+
| datetime            |
+---------------------+
| 2018-12-19 00:00:00 |
+---------------------+

Type de données renvoyé

DATETIME

Éléments de format acceptés pour DATETIME

Sauf indication contraire, les fonctions DATETIME 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).
%c Représentation de la date et de l'heure.
%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.
%H Heure (format 24 heures) sous forme de nombre décimal (00-23).
%I Heure (format 12 heures) sous forme de nombre décimal (01-12).
%j Jour de l'année sous forme de nombre décimal (001-366).
%k Heure (format 24 heures) sous forme de nombre décimal (0-23) ; les valeurs à un seul chiffre sont précédées d'une espace.
%l Heure (format 12 heures) sous forme de nombre décimal (1-12) ; les valeurs à un seul chiffre sont précédées d'une espace.
%M Minutes sous forme de nombre décimal (00-59).
%m Mois sous forme de nombre décimal (01-12).
%n Caractère de nouvelle ligne.
%P am ou pm.
%p AM ou PM.
%R Heure au format %H:%M.
%r Heure au format 12 heures (avec notation AM/PM).
%S Secondes sous forme de nombre décimal (00-60).
%s Nombre de secondes écoulées depuis le 1970-01-01 00:00:00. Remplace systématiquement tous les autres éléments de format, indépendamment de l'endroit où %s apparaît dans la chaîne. Si plusieurs éléments %s apparaissent, le dernier est prioritaire.
%T Heure au format %H:%M:%S.
%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 l'heure au format HH:MM:SS.
%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.
%% Caractère % unique.
%E#S Secondes avec # chiffres de précision fractionnelle.
%E*S Secondes avec précision fractionnelle complète (littéral '*').
%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.