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. Consultez la rubrique Définition de fuseaux horaires pour plus d'informations sur la spécification d'un fuseau horaire.

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 la part à l'objet DATETIME.

DATETIME_ADD accepte les valeurs suivantes pour la part :

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK (équivalent à 7 DAYs (jours)).
  • 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 la part de DATETIME.

DATETIME_SUB accepte les valeurs suivantes pour la part :

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK (équivalent à 7 DAYs (jours)).
  • 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 de la part entre les deux expressions datetime_expression. Si la première DATETIME (date/heure) précède la deuxième 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 la valeur INT64 maximale possible.

DATETIME_DIFF accepte les valeurs suivantes pour la 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 où 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 éléments DATETIME espacés de 24 heures. DATETIME_DIFF avec la partie WEEK (semaine) renvoie 1 car DATETIME_DIFF compte le nombre de limites de la partie dans cette plage d'éléments 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 dans des années différentes. DATETIME_DIFF avec la partie de date YEAR renvoie 3 car il compte le nombre de limites d'année du calendrier grégorien entre les deux paramètres DATETIME. DATETIME_DIFF avec la partie de date ISOYEAR renvoie 2 car le deuxième élément DATETIME appartient à l'année ISO 2015. Le premier jeudi de l'année civile 2015 était 2015-01-01, donc l'année ISO 2015 commence le lundi précédent, 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 deuxième 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, la valeur DATETIME d'origine tombe un dimanche. Étant donné que la part est WEEK(MONDAY), DATE_TRUNC renvoie le paramètre 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 l'expression 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 2015-01-01, donc l'année ISO 2015 commence le lundi précédent, 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 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 de l'élément format_string spécifié. Consultez la rubrique Éléments de format acceptés pour DATETIME pour connaître la liste des Éléments de format acceptés pour 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 élément format_string et une représentation STRING d'un élément DATETIME pour renvoyer un DATETIME. Consultez la rubrique Éléments de format acceptés pour DATETIME pour connaître la liste des éléments de format acceptés pour 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 de 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, 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 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. Lorsque deux éléments de format ou plus 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 éléments écrase le plus récent. Consultez les descriptions de %s, %C et %y dans Éléments de format acceptés pour DATETIME.

Exemples

L'exemple suivant analyse un littéral de 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 STRING contenant une date au format 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 Le nombre de secondes depuis le 1970-01-01 00:00:00. Remplace toujours 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.