Fonctions date-heure dans SQL standard

BigQuery prend en charge les fonctions DATETIME suivantes.

Toutes les sorties sont automatiquement formatées selon la norme ISO 8601,en séparant la date et l’heure par unT.

CURRENT_DATETIME

CURRENT_DATETIME()

Description

Retourne l’heure actuelle sous forme d’un objet DATETIME.

Cette fonction supporte un paramètre timezone facultatif.Voir les définitions de fuseau horaire pour desinformations sur la façon de spécifier un fuseau horaire.

Retourne le type de données

DATETIME

Exemple

SELECT CURRENT_DATETIME() as now;+----------------------------+| now |+----------------------------+| 2016-05-19T10:38:47.046465 |+----------------------------+

DATETIME

Description

1. Construit un objet DATETIME en utilisant des valeurs INT64représentant l’année, le mois, le jour, l’heure, la minute et la seconde.
2. Construit un objet DATETIMEen utilisant un objet DATE et un objet TIME facultatif.
3. Construit un objet DATETIMEen utilisant un objet TIMESTAMP. Il supporte un paramètre optionnel pour spécifier un fuseau horaire. Si aucun fuseau horaire n’est spécifié, le fuseau horaire par défaut, UTC, est utilisé.

Retourne le type de données

DATETIME

Exemple

EXTRACT

EXTRACT(part FROM datetime_expression)

Description

Retourne une valeur qui correspond au part spécifié à partir d’un datetime_expression fourni.

Les valeurs part autorisées sont :

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK : Renvoie le numéro de semaine de la date dans la plage . Les semaines commencent par le dimanche, et les dates antérieures au premier dimanche de l’année sont en semaine 0.
• WEEK(<WEEKDAY>) : Renvoie le numéro de semaine de datetime_expression dans l’intervalle . Les semaines commencent le WEEKDAY.datetimes antérieures au premier WEEKDAY de l’année sont dans 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 la datetime_expression. Les ISOWEEK commencent le lundi. Les valeurs retournées sont comprises dans l’intervalle . Le premier ISOWEEK de chaque année ISO commence le lundi avant le premier jeudi de l’année civile grégorienne.
• MONTH
• QUARTER
• YEAR
• ISOYEAR : Renvoie l’année ISO 8601 de numérotation des semaines, qui est l’année du calendrier grégorien contenant leJeudi de la semaine à laquelle date_expression appartient.
• DATE
• TIME

Les valeurs renvoyées tronquent les périodes de temps d’ordre inférieur. Par exemple, lors de l’extraction des secondes, EXTRACT tronque les valeurs millisecondes et microsecondes.

Retourner le type de données

INT64, sauf dans les cas suivants :

• Si part est DATE, renvoie un objet DATE.
• Si part est TIME, renvoie un objet TIME.

Exemples

Dans l’exemple suivant, EXTRACT renvoie une valeur correspondant à la partie temps HOUR.

SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;+------------------+| hour |+------------------+| 15 |+------------------+

Dans l’exemple suivant, EXTRACT renvoie des valeurs correspondant à différentes parties temps d’une colonne de dates.

Dans l’exemple suivant, datetime_expression tombe un dimanche. EXTRACTcalcule la première colonne en utilisant les semaines qui commencent le dimanche, et il calculela deuxième colonne en utilisant les semaines qui commencent le lundi.

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

Description

Ajoute int64_expressionunités de part à l’objet DATETIME.

DATETIME_ADD supporte les valeurs suivantes pour part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalent à 7 DAYs.
• MONTH
• QUARTER
• YEAR

Une manipulation spéciale est requise pour les parties MONTH, QUARTER et YEAR lorsque la date est au (ou proche du) dernier jour du mois. Si le mois résultant a moins de jours que le jour de la DATETIME d’origine, alors le jour résultant est le dernier jour du nouveau mois.

Type de données de retour

DATETIME

Exemple

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

Description

Soustrait int64_expression unités de part de la DATETIME.

DATETIME_SUB supporte les valeurs suivantes pour part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK. Equivalent à 7 DAYs.
• MONTH
• QUARTER
• YEAR

Une manipulation spéciale est requise pour les parties MONTH, QUARTER, et YEAR lorsque la date est au (ou proche du) dernier jour du mois. Si le mois résultant a moins de jours que le jour de l’original DATETIME, alors le jour résultant est le dernier jour du nouveau mois.

Type de données retournées

DATETIME

Exemple

DATETIME_DIFF

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

Description

Retourne le nombre d’intervalles entiers spécifiés part entre deuxDATETIME objets (datetime_expression_a – datetime_expression_b).Si le premier DATETIME est antérieur au second, la sortie est négative. Lance une erreur si le calcul déborde le type de résultat, par exemple si la différence en microsecondes entre les deux objets DATETIME déborde une valeurINT64.

DATETIME_DIFF supporte 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 le WEEKDAY. Les valeurs valides pourWEEKDAY sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY, et SATURDAY.
• ISOWEEK : Utilise les frontières de la semaine ISO 8601. Les semaines ISO commencent le lundi.
• MONTH
• QUARTER
• YEAR
• ISOYEAR : Utilise la limite d’année de la numérotation hebdomadaire ISO 8601. La limite de l’année ISO est le lundi de la première semaine dont le jeudi appartient à l’année calendaire grégorienne correspondante.

Retourner le type de données

INT64

Exemple

L’exemple ci-dessus montre le résultat de DATETIME_DIFF pour deux DATETIME qui sont séparés de 24 heures. DATETIME_DIFF avec la pièce WEEK renvoie 1 carDATETIME_DIFF compte le nombre de limites de pièces dans cette plage deDATETIMEs. Chaque WEEK commence le dimanche, il y a 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 d’années indifférentes. DATETIME_DIFF avec la partie date YEAR renvoie 3 car ilcompte le nombre de bornes de l’année civile grégorienne entre les deuxDATETIME. DATETIME_DIFF avec la partie date ISOYEAR renvoie 2 car ce deuxième DATETIME appartient à l’année ISO 2015. Le premier jeudi de l’année civile 2015 était le 01-01-2015, donc l’année ISO 2015 commence le lundi précédent, le 29-12-2014.

L’exemple suivant montre le résultat de DATETIME_DIFF pour deux jours d’insuccession. La première date tombe un lundi et la deuxième date tombe un dimanche. DATETIME_DIFF avec la partie date WEEK renvoie 0 car cette partie temps utilise des semaines qui commencent le dimanche. DATETIME_DIFF avec la partie dateWEEK(MONDAY) renvoie 1. DATETIME_DIFF avec la partie dateISOWEEK renvoie également 1 car les semaines ISO commencent le lundi.

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

Description

Trouve un objet DATETIME à la granularité de part.

DATETIME_TRUNC supporte les valeurs suivantes pour part:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• WEEK(<WEEKDAY>) : Tronque datetime_expression à la limite de semaine précédente, où les semaines commencent le WEEKDAY. Les valeurs valides pour WEEKDAY sontSUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, etSATURDAY.
• ISOWEEK : Tronque datetime_expression à la limite de semaine précédenteISO 8601. Les ISOWEEKcommencent le lundi. Le premier ISOWEEK de chaque année ISO contient le premier jeudi de l’année civile grégorienne correspondante. Toute date_expressionannée antérieure à celle-ci sera tronquée au lundi précédent.
• MONTH
• QUARTER
• YEAR
• ISOYEAR : Tronque datetime_expression à la limite de l’année précédente de la semaine ISO 8601. La limite de l’année ISO est le lundi de la première semaine dont le jeudi appartient à l’année calendaire grégorienne correspondante.

Retourner le type de données

DATETIME

Exemples

Dans l’exemple suivant, le DATETIME original tombe un dimanche. Comme lepart est WEEK(MONDAY), DATE_TRUNC renvoie le DATETIME pour le lundi précédent.

Dans l’exemple suivant, le datetime_expression original est dans l’année civile grégorienne 2015. Cependant, DATETIME_TRUNC avec la partie date ISOYEAR tronque le 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 le2015-01-01, donc l’année ISO 2015 commence le lundi précédent, 2014-12-29.Par conséquent, la limite de l’année ISO précédant le datetime_expression2015-06-15 00:00:00 est 2014-12-29.

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Description

Formate un objet DATETIME selon le format_string spécifié. VoirEléments de format pris en charge pour DATETIMEpour une liste des éléments de format que cette fonction prend en charge.

Type de données de retour

STRING

Exemples

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

SELECT FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00") AS formatted;+-------------+| formatted |+-------------+| Dec-25-2008 |+-------------+

SELECT FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00") AS formatted;+-------------+| formatted |+-------------+| Dec 2008 |+-------------+

DERNIER JOUR

LAST_DAY(datetime_expression)

Description

Retourne le dernier jour à partir d’une expression datetime qui contient la date.Ceci est couramment utilisé pour retourner le dernier jour du mois.

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

• YEAR
• QUARTER
• MONTH
• WEEK. Equivalent à 7 DAYs.
• WEEK(<WEEKDAY>). <WEEKDAY> représente le jour de la semaine de départ.Les valeurs valides sont SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,FRIDAY, et SATURDAY.
• ISOWEEK. Utilise les limites de la semaine ISO 8601. Les semaines ISO commencent le lundi.
• ISOYEAR. La limite de l’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 de retour

DATE

Exemple

Ceux-ci retournent tous deux le dernier jour du mois:

SELECT LAST_DAY(DATETIME '2008-11-25', MONTH) AS last_day+------------+| last_day |+------------+| 2008-11-30 |+------------+

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

Cela retourne le dernier jour de l’année:

SELECT LAST_DAY(DATETIME '2008-11-25 15:30:00', YEAR) AS last_day+------------+| last_day |+------------+| 2008-12-31 |+------------+

Cela retourne le dernier jour de la semaine pour une semaine qui commence un dimanche :

SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(SUNDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-15 |+------------+

Cela renvoie le dernier jour de la semaine pour une semaine qui commence un lundi:

SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(MONDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-16 |+------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, datetime_string)

Description

Convertit une représentation en chaîne d’un temps de date en un objetDATETIME.

format_string contient les éléments de format qui définissent comment datetime_string est formaté. Chaque élément dansdatetime_string doit avoir un élément correspondant dans format_string. L’emplacement de chaque élément dans format_string doit correspondre à l’emplacement de chaque élément dans datetime_string.

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

PARSE_DATETIME analyse string selon les règles suivantes:

• Champs non spécifiés. Tout champ non spécifié est initialisé à partir de1970-01-01 00:00:00.0. Par exemple, si l’année n’est pas spécifiée, elle est initialisée à 1970.
• Noms insensibles à la casse. Les noms, tels que Monday et February, sont insensibles à 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îneDATETIME. Les espaces blancs de tête et de queue dans la chaîne DATETIME sont toujours autorisés, même s’ils ne sont pas dans la chaîne de format.
• Priorité du format. Lorsque deux éléments de format ou plus ont des informations qui se chevauchent, le dernier prévaut généralement sur les précédents, à quelques exceptions près. Par exemple, %F et %Y affectent tous deux l’année, donc le premier élément prévaut sur le dernier. Voir les descriptions de %s, %C, et %y dansEléments de format supportés pour DATETIME.

Retourner le type de données

DATETIME

Exemples

Les exemples suivants analysent un littéral STRING comme unDATETIME.

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

SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 PM') AS datetime+---------------------+| datetime |+---------------------+| 2018-08-30T14:23:38 |+---------------------+

L’exemple suivant analyse un littéral STRING contenant une date dans un format de langage naturel en tant queDATETIME.

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018') AS datetime;+---------------------+| datetime |+---------------------+| 2018-12-19T00:00:00 |+---------------------+

Éléments de format pris en charge pour DATETIME

Sauf indication contraire, les fonctions DATETIME qui utilisent des chaînes de format prennent en charge les éléments suivants:

.