9.9. Fonctions et opérateurs sur date/heure

Le Tableau 9.26, « Fonctions date/heure » affiche les fonctions disponibles pour le traitement des valeurs date et heure, les détails apparaissant dans les sous-sections qui suivent. Le Tableau 9.25, « Opérateurs date/heure » illustre les comportements des opérateurs arithmétiques basiques (+, *, etc.). Pour les fonctions de formatage, on peut se référer à la Section 9.8, « Fonctions de formatage des types de données ». Il est important d'être familier avec les informations de base concernant les types de données date/heure de la Section 8.5, « Types date/heure ».

Toutes les fonctions et opérateurs décrits ci-dessous qui acceptent une entrée de type time ou timestamp acceptent deux variantes : une avec time with time zone ou timestamp with time zone et une autre avec time without time zone ou timestamp without time zone. Ces variantes ne sont pas affichées séparément. De plus, les opérateurs + et * sont commutatifs (par exemple, date + integer et integer + date) ; seule une possibilité est présentée ici.

Tableau 9.25. Opérateurs date/heure

Opérateur Exemple Résultat
+ date '2001-09-28' + integer '7' date '2001-10-05'
+ date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00:00'
+ date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00:00'
+ interval '1 day' + interval '1 hour' interval '1 day 01:00:00'
+ timestamp '2001-09-28 01:00' + interval '23 hours' timestamp '2001-09-29 00:00:00'
+ time '01:00' + interval '3 hours' time '04:00:00'
- - interval '23 hours' interval '-23:00:00'
- date '2001-10-01' - date '2001-09-28' integer '3'
- date '2001-10-01' - integer '7' date '2001-09-24'
- date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00:00'
- time '05:00' - time '03:00' interval '02:00:00'
- time '05:00' - interval '2 hours' time '03:00:00'
- timestamp '2001-09-28 23:00' - interval '23 hours' timestamp '2001-09-28 00:00:00'
- interval '1 day' - interval '1 hour' interval '1 day -01:00:00'
- timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' interval '1 day 15:00:00'
* 900 * interval '1 second' interval '00:15:00'
* 21 * interval '1 day' interval '21 days'
* double precision '3.5' * interval '1 hour' interval '03:30:00'
/ interval '1 hour' / double precision '1.5' interval '00:40:00'

Tableau 9.26. Fonctions date/heure

Fonction Code de retour Description Exemple Résultat
age(timestamp, timestamp) interval Soustrait les arguments, ce qui produit un résultat « symbolique » en années, mois, jours age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days
age(timestamp) interval Soustrait à la date courante (current_date) age(timestamp '1957-06-13') 43 years 8 mons 3 days
clock_timestamp() timestamp with time zone Date et heure courantes (change pendant l'exécution de l'instruction) ; voir la Section 9.9.4, « Date/Heure courante »    
current_date date Date courante ; voir la Section 9.9.4, « Date/Heure courante »    
current_time time with time zone Heure courante ; voir la Section 9.9.4, « Date/Heure courante »    
current_timestamp timestamp with time zone Date et heure courantes (début de la transaction en cours) ; voir la Section 9.9.4, « Date/Heure courante »    
date_part(text, timestamp) double precision Obtenir un sous-champ (équivalent à extract) ; voir la Section 9.9.1, « EXTRACT, date_part » date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part(text, interval) double precision Obtenir un sous-champ (équivalent à extract) ; voir la Section 9.9.1, « EXTRACT, date_part » date_part('month', interval '2 years 3 months') 3
date_trunc(text, timestamp) timestamp Tronquer à la précision indiquée ; voir aussi la Section 9.9.2, « date_trunc » date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00
extract(field from timestamp) double precision Obtenir un sous-champ ; voir la Section 9.9.1, « EXTRACT, date_part » extract(hour from timestamp '2001-02-16 20:38:40') 20
extract(field from interval) double precision Obtenir un sous-champ ; voir la Section 9.9.1, « EXTRACT, date_part » extract(month from interval '2 years 3 months') 3
isfinite(timestamp) boolean Teste si l'estampille temporelle est finie (non infinie) isfinite(timestamp '2001-02-16 21:28:30') true
isfinite(interval) boolean Teste si l'intervalle est fini isfinite(interval '4 hours') true
justify_days(interval) interval Ajuste l'intervalle pour que les périodes de 30 jours soient représentées comme des mois justify_days(interval '30 days') 1 month
justify_hours(interval) interval Ajuste l'intervalle pour que les périodes de 24 heures soient représentées comme des jours justify_hours( interval '24 hours') 1 day
justify_interval(interval) interval Ajuste l'intervalle en utilisant justify_days et justify_hours, avec des signes supplémentaires d'ajustement justify_interval(interval '1 mon -1 hour') 29 days 23:00:00
localtime time Heure du jour courante ; voir la Section 9.9.4, « Date/Heure courante »    
localtimestamp timestamp Date et heure courante (début de la transaction) ; voir la Section 9.9.4, « Date/Heure courante »    
now() timestamp with time zone Date et heure courantes (début de la transaction) ; voir la Section 9.9.4, « Date/Heure courante »    
statement_timestamp() timestamp with time zone Date et heure courantes (début de l'instruction en cours) ; voir Section 9.9.4, « Date/Heure courante »    
timeofday() text Date et heure courantes (comme clock_timestamp mais avec une chaîne de type text) ; voir la Section 9.9.4, « Date/Heure courante »    
transaction_timestamp() timestamp with time zone Date et heure courantes (début de la transaction en cours) ; voir Section 9.9.4, « Date/Heure courante »    

En plus de ces fonctions, l'opérateur SQL OVERLAPS est supporté :

( début1, fin1 ) OVERLAPS ( début2, fin2 )
( début1, longueur1 ) OVERLAPS ( début2, longueur2 )

Cette expression renvoie vrai (true) lorsque les deux périodes de temps (définies par leur point final) se chevauchent, et faux dans le cas contraire. Les limites peuvent être indiquées comme des paires de dates, d'heures ou de timestamps ; ou comme une date, une heure ou un timestamp suivi d'un intervalle.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Résultat :
true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Résultat :
false

Lors de l'ajout ou de la soustraction d'une valeur de type interval avec une valeur de type timestamp with time zone, le composant jours incrémente ou décremente la date du timestamp with time zone par le nombre de jours indiqués. Avec les modifications occasionnées par les changements d'heure (avec un fuseau horaire de session qui reconnaît DST), cela signifie qu'un interval '1 day' n'est pas forcément égal à un interval '24 hours'. Par exemple, avec un fuseau horaire configuré à CST7CDT, timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' produit un timestamp with time zone '2005-04-03 12:00-06' alors qu'ajouter interval '24 hours' au même timestamp with time zone initial produit un timestamp with time zone '2005-04-03 13:00-06' parce qu'il y a un changement d'heure le 2005-04-03 02:00 pour le fuseau horaire CST7CDT.

9.9.1. EXTRACT, date_part

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 :

century

Le siècle.

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
Résultat : 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 21

Le premier siècle commence le 1er janvier de l'an 1 (0001-01-01 00:00:00 AD) bien qu'ils ne le savaient pas à cette époque. Cette définition s'applique à tous les pays qui utilisent le calendrier Grégorien. Le siècle 0 n'existe pas. On passe de -1 à 1. En cas de désaccord, adresser une plainte à : Sa Sainteté le Pape, Cathédrale Saint-Pierre de Rome, Vatican.

Les versions de PostgreSQL™ antérieures à la 8.0 ne suivaient pas la numérotation conventionnelle des siècles mais renvoyaient uniquement le champ année divisée par 100.

day

Le champ jour (du mois) : de 1 à 31.

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 16
decade

Le champ année divisé par 10.

SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 200
dow

Le jour de la semaine (de 0 à 6 ; dimanche étant le 0) (uniquement pour les valeurs de type timestamp).

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 5

Cette numérotation du jour de la semaine est différente de celle de la fonction to_char.

doy

Le jour de l'année (de 1 à 365/366) (uniquement pour les valeurs timestamp).

SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 47
epoch

Pour les valeurs de type date et timestamp, le nombre de secondes depuis le 1er janvier 1970 (exactement depuis le 1970-01-01 00:00:00-00). Ce nombre peut être négatif. Pour les valeurs de type interval, il s'agit du nombre total de secondes dans l'intervalle.

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
Résultat :
982384720

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Résultat :
442800

Convertir une valeur epoch en valeur de type date/heure :

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
hour

Le champ heure (0 - 23).

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 20
microseconds

Le champ secondes, incluant la partie décimale, multiplié par 1 000 000. Cela inclut l'intégralité des secondes.

SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Résultat :
28500000
millennium

Le millénaire.

SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 3

Les années 1900 sont dans le second millénaire. Le troisième millénaire commence le 1er janvier 2001.

Les versions de PostgreSQL™ antérieures à la 8.0 ne suivaient pas les conventions de numérotation des millénaires mais renvoyaient seulement le champ année divisé par 1000.

milliseconds

Le champ secondes, incluant la partie décimale, multiplié par 1000. Cela inclut l'intégralité des secondes.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Résultat :
28500
minute

Le champ minutes (0 - 59).

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 38
month

Pour les valeurs de type timestamp, le numéro du mois dans l'année (de 1 à 12) ; pour les valeurs de type interval, le nombre de mois, modulo 12 (0 - 11).

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 2

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
Résultat : 3

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
Résultat : 1
quarter

Le trimestre (1 - 4) dont le jour fait partie (uniquement pour les valeurs de type timestamp).

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 1
second

Le champs secondes, incluant la partie décimale (0 - 59[5]).

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 40

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Résultat :
28.5
timezone

Le décalage du fuseau horaire depuis UTC, mesuré en secondes. Les valeurs positives correspondent aux fuseaux horaires à l'est d'UTC, les valeurs négatives à l'ouest d'UTC.

timezone_hour

Le composant heure du décalage du fuseau horaire.

timezone_minute

Le composant minute du décalage du fuseau horaire.

week

Le numéro de la semaine dans l'année, à laquelle appartient le jour. Par définition (ISO 8601), la première semaine d'une année contient le 4 janvier de cette année. (La semaine ISO-8601 commence un lundi.) Dit autrement, le premier jeudi d'une année se trouve dans la première semaine de cette année. (Uniquement pour les valeurs de type timestamp.)

De ce fait, les dates de début janvier peuvent faire partie de la 52è ou 53è semaine de l'année précédente. Par exemple, 2005-01-01 fait partie de la 53è semaine de 2004 et 2006-01-01 fait partie de la 52è semaine de l'année 2005.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 7
year

Le champ année. Il n'y a pas de 0 AD, la soustraction d'années BC aux années AD nécessite donc une attention particulière.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat :
2001

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

9.9.2. date_trunc

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 :

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

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

9.9.3. AT TIME ZONE

La construction AT TIME ZONE permet les conversions d'« estampilles temporelles » (time stamps) dans les différents fuseaux horaires. Le Tableau 9.27, « Variantes AT TIME ZONE » affiche ses variantes.

Tableau 9.27. Variantes AT TIME ZONE

Expression Type de retour Description
timestamp without time zone AT TIME ZONE zone timestamp with time zone Traite l'estampille donnée without time zone (sans fuseau), comme située dans le fuseau horaire indiqué.
timestamp with time zone AT TIME ZONE zone timestamp without time zone Convertit l'estampille donnée with time zone (avec fuseau) dans le nouveau fuseau horaire.
time with time zone AT TIME ZONE zone time with time zone Convertit l'heure donnée with time zone (avec fuseau) dans le nouveau fuseau horaire.

Dans ces expressions, le fuseau horaire désiré zone peut être indiqué comme une chaîne texte (par exemple, 'PST') 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 PST8PDT) :

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

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

Le premier exemple prend une « estampille temporelle sans fuseau » et l'interprète comme une date MST (UTC-7), qui est ensuite convertie en PST (UTC-8) pour l'affichage. Le second exemple prend une estampille indiquée en EST (UTC-5) et la convertit en heure locale, c'est-à-dire en MST (UTC-7).

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

9.9.4. Date/Heure courante

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]

Note

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

PostgreSQL™ fournit aussi des fonctions qui renvoie 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 :

now() ;
transaction_timestamp() ;
statement_timestamp() ;
clock_timestamp() ;
timeofday().

now() est l'équivalent traditionnel PostgreSQL™ de CURRENT_TIMESTAMP. 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 formatée en tant que chaîne text plutôt qu'en valeur de type timestamp with time zone.

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'; -- incorrect en utilisation avec DEFAULT
[Astuce]

Astuce

La troisième forme ne doit pas être utilisée pour la spécification de la clause DEFAULT à la création d'une table. Le système convertirait now en valeur de type timestamp dès l'analyse de la constante. À chque fois que la valeur par défaut serait nécessaire, c'est l'heure de création de la table qui sera utilisée. Les deux premières formes ne sont pas évaluées avant l'utilisation de la valeur par défaut car ce sont des appels de fonctions. C'est donc bien le comportement attendu d'heure d'insertion comme valeur par défaut qui est obtenu.

9.9.5. Retarder l'exécution

La fonction suivante permet de retarder l'exécution du processus serveur :

pg_sleep(seconds)

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. Par exemple :

SELECT pg_sleep(1.5);
[Note]

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 la charge serveur.

[Avertissement]

Avertissement

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



[5] 60 si les secondes « leap » sont implantées par le système d'exploitation.