9.9. Fonctions et opérateurs sur date/heure

EXTRACT (champ FROM source)

La fonction extract récupère des sous-champs de valeurs date/heure, tels que l'année ou l'heure. source est une expression de valeur de type timestamp, time ou interval. (Les expressions de type date sont converties en timestamp et peuvent aussi être utilisées.) champ est un identifiant ou une chaîne qui sélectionne le champ à extraire de la valeur source. La fonction extract renvoie des valeurs de type double precision. La liste qui suit présente les noms de champs valides :

Note

Quand la valeur en entrée est +/-Infinity, extract renvoie +/-Infinity pour les champs incrémentés de façon monotonique (epoch, julian, year, isoyear, decade, century et millennium). Pour les autres champs, NULL est renvoyé. Les versions de PostgreSQL™ antérieures à la 9.6 renvoyaient 0 pour tous les cas de saisie infinie.

La fonction extract a pour but principal l'exécution de calculs. Pour le formatage des valeurs date/heure en vue de leur affichage, voir la Section 9.8, « Fonctions de formatage des types de données ».

La fonction date_part est modelée sur l'équivalent traditionnel Ingres™ de la fonction extract du standard SQL :

date_part('champ', source)

Le paramètre champ est obligatoirement une valeur de type chaîne et non pas un nom. Les noms de champ valide pour date_part sont les mêmes que pour extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Résultat : 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Résultat : 4

La fonction date_trunc est conceptuellement similaire à la fonction trunc pour les nombres.

date_trunc('champ', source)

source est une expression de type timestamp ou interval. (Les valeurs de type date et time sont converties automatiquement en, respectivement, timestamp ou interval). champ indique la précision avec laquelle tronquer la valeur en entrée. La valeur de retour est de type timestamp ou interval avec tous les champs moins significatifs que celui sélectionné positionnés à zéro (ou un pour la date et le mois).

Les valeurs valides pour champ sont :

Exemples :

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Résultat : 2001-02-16
20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Résultat : 2001-01-01
00:00:00

La construction AT TIME ZONE convertit l'horodatage without time zone vers ou à partir d'un horodatage with time zone, et des valeurs time de différents fuseaux horaires. Tableau 9.31, « Variantes AT TIME ZONE » affiche ses variantes.

Dans ces expressions, le fuseau horaire désiré zone peut être indiqué comme une chaîne texte (par exemple, 'Amerci/Los_Angeles') ou comme un intervalle (c'est-à-dire INTERVAL '-08:00'). Dans le cas textuel, un nom de fuseau peut être indiqué de toute façon décrite dans Section 8.5.3, « Fuseaux horaires ».

Exemples (en supposant que le fuseau horaire local soit America/Los_Angeles) :

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Résultat : 2001-02-16
19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Résultat : 2001-02-16
18:38:40

SELECT TIMESTAMP '2001-02-16 20:38:40-05' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Résultat : 2001-02-16 05:38:40
     

Le premier exemple ajoute un fuseau horaire à une valeur qui en manque, et affiche la valeur en utilisant la configuration de TimeZone. Le deuxième exemple décale l'horodatage avec fuseau horaire vers le fuseau horaire spécifié, et renvoie la valeur sans fuseau horaire. Ceci permet un stockage et un affichage de valeurs différentes de la configuration TimeZone actuelle. Le troisième exemple convertit l'heure de Tokyo en heure de Chicago. Convertir des valeurs de type time vers d'autres fuseaux horaires utilise les règles du fuseau horaire actuel si aucune date n'est fournie.

La fonction timezone(zone, timestamp) est équivalente à la construction conforme au standard SQL, timestamp AT TIME ZONE zone.

PostgreSQL™ fournit diverses fonctions qui renvoient des valeurs relatives aux date et heure courantes. Ces fonctions du standard SQL renvoient toutes des valeurs fondées sur l'heure de début de la transaction en cours :

CURRENT_DATE ;
CURRENT_TIME ;
CURRENT_TIMESTAMP ;
CURRENT_TIME(precision) ;
CURRENT_TIMESTAMP(precision) ;
LOCALTIME ;
LOCALTIMESTAMP ;
LOCALTIME(precision) ;
LOCALTIMESTAMP(precision).
     

CURRENT_TIME et CURRENT_TIMESTAMP délivrent les valeurs avec indication du fuseau horaire ; LOCALTIME et LOCALTIMESTAMP délivrent les valeurs sans indication du fuseau horaire.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, et LOCALTIMESTAMP acceptent un paramètre optionnel de précision. Celui-ci permet d'arrondir le résultat au nombre de chiffres indiqués pour la partie fractionnelle des secondes. Sans ce paramètre de précision, le résultat est donné avec toute la précision disponible.

Quelques exemples :

SELECT CURRENT_TIME;
Résultat :
14:39:53.662522-05

SELECT CURRENT_DATE;
Résultat :
2001-12-23

SELECT CURRENT_TIMESTAMP;
Résultat : 2001-12-23
14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Résultat : 2001-12-23
14:39:53.66-05

SELECT LOCALTIMESTAMP;
Résultat : 2001-12-23
14:39:53.662522

Comme ces fonctions renvoient l'heure du début de la transaction en cours, leurs valeurs ne changent pas au cours de la transaction. Il s'agit d'une fonctionnalité : le but est de permettre à une même transaction de disposer d'une notion cohérente de l'heure « courante ». Les multiples modifications au sein d'une même transaction portent ainsi toutes la même heure.

Note

D'autres systèmes de bases de données actualisent ces valeurs plus fréquemment.

PostgreSQL™ fournit aussi des fonctions qui renvoient l'heure de début de l'instruction en cours, voire l'heure de l'appel de la fonction. La liste complète des fonctions ne faisant pas partie du standard SQL est :

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()
     

transaction_timestamp() est un peu l'équivalent de CURRENT_TIMESTAMP mais est nommé ainsi pour expliciter l'information retournée. statement_timestamp() renvoie l'heure de début de l'instruction en cours (plus exactement, l'heure de réception du dernier message de la commande en provenance du client). statement_timestamp() et transaction_timestamp() renvoient la même valeur pendant la première commande d'une transaction, mais leurs résultats peuvent différer pour les commandes suivantes. clock_timestamp() renvoie l'heure courante, et, de ce fait, sa valeur change même à l'intérieur d'une commande SQL unique. timeofday() est une fonction historique de PostgreSQL™. Comme clock_timestamp(), elle renvoie l'heure courante, mais celle-ci est alors formatée comme une chaîne text et non comme une valeur de type timestamp with time zone. now() est l'équivalent traditionnel PostgreSQL™ de CURRENT_TIMESTAMP.

Tous les types de données date/heure acceptent aussi la valeur littérale spéciale now pour indiquer la date et l'heure courantes (interprétés comme l'heure de début de la transaction). De ce fait, les trois instructions suivantes renvoient le même résultat :

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- mais voir l'astuce ci-dessous

Astuce

Ne pas utiliser la troisième forme lors de la spécification d'une valeur à évaluer ultérieurement, par exemple dans une clause DEFAULT de la colonne d'une table. Le système convertirait now en valeur de type timestamp dès l'analyse de la constante. À chaque fois que la valeur par défaut est nécessaire, c'est l'heure de création de la table qui est alors utilisée. Les deux premières formes ne sont pas évaluées avant l'utilisation de la valeur par défaut, il s'agit d'appels de fonctions. C'est donc bien le comportement attendu, l'heure d'insertion comme valeur par défaut, qui est obtenu. (Voir aussi Section 8.5.1.4, « Valeurs spéciales ».)

Les fonctions suivantes permettent de retarder l'exécution du processus serveur :

pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)
     

pg_sleep endort le processus de la session courante pendant seconds secondes. seconds est une valeur de type double precision, ce qui autorise les délais en fraction de secondes. pg_sleep_for est une fonction d'aide permettant d'indiquer des durées plus longues d'endormissement, à spécifier sous la forme d'une donnée de type interval. pg_sleep_until est une fonction permettant de préciser une heure de réveil plutôt qu'un intervalle. Par exemple :

SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');

Note

La résolution réelle de l'intervalle est spécifique à la plateforme ; 0,01 seconde est une valeur habituelle. Le délai dure au minimum celui précisé. Il peut toutefois être plus long du fait de certains facteurs tels que la charge serveur. En particulier, pg_sleep_until ne garantit pas un réveil à l'heure exacte spécifiée. Par contre, il ne se réveillera pas avant cette heure.

Avertissement

Il convient de s'assurer que la session courante ne détient pas plus de verrous que nécessaires lors de l'appel à pg_sleep ou ses variantes. Dans le cas contraire, d'autres sessions peuvent être amenées à attendre que le processus de retard courant se termine, ralentissant ainsi tout le système.