9.8. 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.7. 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.

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'
+ time '03:00' + date '2001-09-28'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 '3 hours' + time '01:00'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'
- interval '2 hours' - time '05:00'time '03:00'
- timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00'
* double precision '3.5' * interval '1 hour'interval '03:30'
* 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)intervalSoustraction � partir d'aujourd'huiage(timestamp '1957-06-13')43 years 8 mons 3 days
age(timestamp, timestamp)intervalArguments de soustractionage('2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
current_datedateDate d'aujourd'hui ; voir Section 9.8.4   
current_timetime with time zoneHeure du jour ; voir Section 9.8.4   
current_timestamptimestamp with time zoneDate et heure du jour ; voir Section 9.8.4   
date_part(text, timestamp)double precisionObtenir un sous-champ (�quivalent � extract) ; voir Section 9.8.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.8.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.8.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.8.1 extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from interval)double precisionObtenir un sous-champ ; voir Section 9.8.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.8.4   
localtimestamptimestampDate et heure ; voir Section 9.8.4   
now()timestamp with time zoneDate et heure courante (�quivalent � current_timestamp) ; voir Section 9.8.4   
timeofday()textDate et heure courante ; voir Section 9.8.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.8.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 qui �value vers le type timestamp ou interval. (Les expressions de type date ou time 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

L'ann�e divis�e par 100

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

Notez que le r�sultat du champ century (si�cle) est simplement le champ year (ann�e) divis� par 100 et non pas la d�finition conventionnelle qui place la plupart des ann�es 1900 dans le 20� si�cle.

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
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
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

L'ann�e divis�e par 1000

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

Notez que le r�sultat du champ millennium est simplement le champ year divis� par 1000 et non pas la d�finition conventionelle qui place les ann�es 1900 dans le second mill�naire.

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)

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

Le champ ann�e

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.7.

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.8.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
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.8.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').

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.8.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';

Note�: 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