9.9. Fonctions et opérateurs pour date/heure

Tableau 9-26 affiche les fonctions disponibles pour le traitement des valeurs date et heure avec des détails apparaissant dans les sous-sections suivantes. Tableau 9-25 illustre les comportements des opérateurs d'arithmétique élémentaire (+, *, etc.). Pour les fonctions de formatage, référez-vous à Section 9.8. Vous devriez être familier avec les informations de base sur les types de données date/heure d'après Section 8.5.

Toutes les fonctions et opérateurs décrits ci-dessous acceptant une entrée de type time ou timestamp viennent en deux variantes : une prenant time with time zone ou timestamp with time zone et une autre prenant time without time zone ou timestamp without time zone. Pour faire bref, ces variantes ne sont pas affichées séparément. De plus, les opérateurs + et * viennent en paires commutatives (par exemple, à la fois date + integer et integer + date) ; nous en montrons seulement une pour chacune des paires.

Tableau 9-25. Opérateurs date/heure

OpérateurExempleRésultat
+ date '2001-09-28' + integer '7'date '2001-10-05'
+ date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00'
+ date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00'
+ interval '1 day' + interval '1 hour'interval '1 day 01:00'
+ timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00'
+ time '01:00' + interval '3 hours'time '04:00'
- - interval '23 hours'interval '-23: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'
- time '05:00' - time '03:00'interval '02:00'
- time '05:00' - interval '2 hours'time '03:00'
- timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00'
- interval '1 day' - interval '1 hour'interval '23:00'
- timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00'
* interval '1 hour' * double precision '3.5'interval '03:30'
/ interval '1 hour' / double precision '1.5'interval '00:40'

Tableau 9-26. Fonctions date/heure

FonctionCode de retourDescriptionExempleRésultat
age(timestamp, timestamp)intervalSoustrait les arguments, produisant un résultat << symbolique >> qui utilise les années et les moisage(timestamp '2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
age(timestamp)intervalSoustrait à partir de la date courante (current_date)age(timestamp '1957-06-13')43 years 8 mons 3 days
current_datedateDate d'aujourd'hui ; voir Section 9.9.4   
current_timetime with time zoneHeure du jour ; voir Section 9.9.4   
current_timestamptimestamp with time zoneDate et heure du jour ; voir Section 9.9.4   
date_part(text, timestamp)double precisionObtenir un sous-champ (équivalent à extract) ; voir Section 9.9.1 date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precisionObtenir un sous-champ (équivalent à extract) ; voir Section 9.9.1 date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestampTronquer jusqu'à la précision spécifiée ; voir aussi Section 9.9.2 date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00
extract(field from timestamp)double precisionObtenir un sous-champ ; voir Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precisionObtenir un sous-champ ; voir Section 9.9.1 extract(month from interval '2 years 3 months')3
isfinite(timestamp)booleanTest d'un type time stamp fini (différent de l'infini)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)booleanTest d'un intervalle finiisfinite(interval '4 hours')true
localtimetimeHeure du jour ; voir Section 9.9.4   
localtimestamptimestampDate et heure ; voir Section 9.9.4   
now()timestamp with time zoneDate et heure courante (équivalent à current_timestamp) ; voir Section 9.9.4   
timeofday()textDate et heure courante ; voir Section 9.9.4   

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 surchargent, et faux dans le cas contraire. Les points finaux peuvent être spécifiés comme des pairs 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

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 seront 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. Ce qui suit est une liste de 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 ayant un calendrier Grégorien. Le siècle 0 n'existe pas. Vous allez de -1 à 1. Si vous n'êtes pas d'accord, adressez votre plainte à : 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ée 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

Notez que la numérotation du jour de la semaine est différent de celui 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 (peut être négatif) ; pour les valeurs de type interval, le 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

Voici comment convertir une valeur epoch en une 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. Notez que ceci inclut les secondes complètes.

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 précédant 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. Notez que ceci inclut les secondes complètes.

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[1])

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 de la zone horaire

timezone_minute

Le composant minute du décalage de la zone horaire

week

Le numéro de la semaine dans l'année, auquel 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 avec l' ISO-8601 commence un lundi.) Autrement dit, 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)

À cause de ceci, il est possible pour les dates de début janvier de 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. Gardez en tête qu'il n'y a pas de 0 AD, donc soustraire BC années de AD années devra se faire avec attention.

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 calcul. Pour le formatage des valeurs date/heure en affichage, voir Section 9.8.

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

date_part('champ', source)

Notez, ici, que le paramètre champ doit être 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 une expression de valeur 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 pour tronquer la valeur en entrée. La valeur de retour est de type timestamp ou interval avec tous les champs qui sont moins signifiants que l'ensemble sélectionné de zéro (ou 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 du type << time stamps >> vers les différents fuseaux horaires. Tableau 9-27 affiche ses variantes.

Tableau 9-27. Variantes AT TIME ZONE

ExpressionType de retourDescription
timestamp without time zone AT TIME ZONE zone timestamp with time zoneConvertit l'heure locale avec une zone horaire donnée vers l'UTC
timestamp with time zone AT TIME ZONE zone timestamp without time zoneConvertit la zone horaire UTC vers l'heure avec la zone horaire donnée
time with time zone AT TIME ZONE zone time with time zoneConvertit l'heure locale pour les différentes zones horaires

Dans ces expressions, le fuseau horaire désiré zone peut être spécifié soit comme une chaîne texte (par exemple, 'PST') ou comme un intervalle (c'est-à-dire INTERVAL '-08:00'). Dans le cas textuel, les noms de fuseaux sont ceux affichés dans Tableau B-4. (Il pourrait être utile de supporter les noms plus généraux affichés dans Tableau B-6 mais ceci n'est pas encore implémenté.)

Exemples (supposant que la zone horaire locale est 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 un << zone-less time stamp >> et l'interprète comme heure MST (UTC-7) pour produire un << time stamp >> UTC, qui effectue une rotation vers PST (UTC-8) pour l'affichage. Le deuxième exemple prend un type << time stamp >> spécifié en EST (UTC-5) et le convertit en l'heure locale en MST (UTC-7).

La fonction timezone(zone, timestamp) est équivalente pour la structure conforme au SQL timestamp AT TIME ZONE zone.

9.9.4. Date/Heure courante

Les fonctions suivantes sont disponibles pour obtenir la date courante et/ou l'heure :

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 avec indication du fuseau horaire.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, et LOCALTIMESTAMP peuvent se voir attribuer de façon optionnelle le paramètre de précision, qui cause l'arrondissement du résultat pour le nombre de chiffres de fraction dans le champ des secondes. Sans le paramètre de précision, le résultat est donné avec la précision complète.

Note : Avant PostgreSQL 7.2, les paramètres de précision n'existaient pas et le résultat était toujours donné en secondes entières.

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

La fonction now() est l'équivalent traditionnel PostgreSQL de CURRENT_TIMESTAMP.

Il existe aussi la fonction timeofday() qui, pour des raisons historiques, renvoie une chaîne de type text plutôt qu'une valeur de type timestamp :

SELECT timeofday();
Résultat : Sat Feb 17
19:07:32.000126 2001 EST

Il est important de savoir que CURRENT_TIMESTAMP et les fonctions relatives renvoient l'heure de début de la transaction courante ; leurs valeurs ne changent pas lors de la transaction. Ceci est considérée comme une fonctionnalité : le but est de permettre à une transaction seule d'avoir une notion consistante de l'heure << courante >>, donc les modifications multiples à l'intérieur de la même transaction partagent la même heure. timeofday() renvoie l'heure de l'horloge et change lors des transactions.

Note : D'autres systèmes de bases de données pourraient mettre à jour ces valeurs plus fréquemment.

Tous les types de données date/heure acceptent aussi la valeur littérale spéciale now pour spécifier la date et l'heure actuelle. Du coup, les trois suivants renvoient aussi le même résultat :

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

Astuce : Vous ne voulez pas utiliser la troisième forme lors de la spécification de la clause DEFAULT pour la création d'une table. Le système convertira now vers une valeur de type timestamp dès que la constante est analysée, de façon à ce que la valeur par défaut soit nécessaire, l'heure de la création de la table serait utilisée ! Les deux premières formes ne seront pas évaluées jusqu'à ce que la valeur par défaut soit utilisée car ce sont des appels de fonctions. Donc, ils donneront le comportement désiré d'avoir la valeur par défaut au moment de l'insertion de la ligne.

Notes

[1]

60 si les secondes «leap» sont implémentées par le système d'exploitation