Le Tableau 9.26, « Fonctions date/heure » affiche les fonctions disponibles pour le traitement des valeurs date et heure avec des détails apparaissant dans les sous-sections suivantes. Le Tableau 9.25, « Opérateurs date/heure » illustre les comportements des opérateurs d'arithmétique basique (+, *, etc.). Pour les fonctions de formatage, référez-vous à la Section 9.8, « Fonctions de formatage des types de données ». Vous devez être familier avec les informations de base sur les types de données date/heure d'après la Section 8.5, « Types date/heure ».
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é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, produisant un résultat « symbolique » qui utilise les années et les mois | age(timestamp '2001-04-10', timestamp '1957-06-13') | 43 years 9 mons 27 days |
| age(timestamp) | interval | Soustrait à partir de la date courante (current_date) | age(timestamp '1957-06-13') | 43 years 8 mons 3 days |
| current_date | date | Date d'aujourd'hui ; voir la Section 9.9.4, « Date/Heure courante » | ||
| current_time | time with time zone | Heure du jour ; voir la Section 9.9.4, « Date/Heure courante » | ||
| current_timestamp | timestamp with time zone | Date et heure du jour ; 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 jusqu'à la précision spécifié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 | Test d'un type time stamp fini (différent de l'infini) | isfinite(timestamp '2001-02-16 21:28:30') | true |
| isfinite(interval) | boolean | Test d'un intervalle fini | isfinite(interval '4 hours') | true |
| 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_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 |
| localtime | time | Heure du jour ; voir la Section 9.9.4, « Date/Heure courante » | ||
| localtimestamp | timestamp | Date et heure ; voir la Section 9.9.4, « Date/Heure courante » | ||
| now() | timestamp with time zone | Date et heure courante (équivalent à current_timestamp) ; voir la Section 9.9.4, « Date/Heure courante » | ||
| timeofday() | text | Date et heure courante ; voir la Section 9.9.4, « Date/Heure courante » |
Si vous utilisez à la fois justify_hours et justify_days, il est préférable d'utiliser justify_hours pour que les jours supplémentaires soient incluent dans le traitement réalisé par justify_days.
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.
Fonctions de formatage
des types de données
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 augmente 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 pour la session reconnaissant DST), cela signifie qu'un interval '1 day' ne sera 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' produira 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' car il y a un changement d'heure le 2005-04-03 02:00 pour le fuseau horaire CST7CDT.
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 :
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.
Le champ jour (du mois) : de 1 à 31
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat: 16
Le champ année divisée par 10
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 200
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.
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
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 valeur de type date/heure :
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
Le champ heure (0 - 23)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 20
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
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.
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
Le champ minutes (0 - 59)
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Résultat : 38
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
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
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
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.
Le composant heure du décalage du fuseau horaire
Le composant minute du décalage du fuseau horaire
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, 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
Le champ année. Gardez en tête qu'il n'y a pas de 0 AD, donc soustraire des années BC d'années AD 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 la Section 9.8, « Fonctions de formatage des types de données ».
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
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 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
La construction AT TIME ZONE permet les conversions du type « time stamps » vers 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 le type donné, timestamp without time zone, comme étant situé dans le fuseau horaire spécifié |
| timestamp with time zone AT TIME ZONE zone | timestamp without time zone | Convertit le type donné, timestamp with time zone, dans le nouveau fuseau horaire |
| time with time zone AT TIME ZONE zone | time with time zone | Convertit le type donné, time with time zone, dans le nouveau fuseau horaire |
Dans ces expressions, le fuseau horaire désiré zone peut être spécifié soit comme une chaîne texte (par exemple, 'PST') soit comme un intervalle (c'est-à-dire INTERVAL '-08:00'). Dans le cas textuel, les noms de fuseaux sont ceux affichés soit dans le Tableau B.6, « Noms des zones de fuseaux horaires pour la configuration de timezone » soit dans le Tableau B.4, « Abréviations de fuseaux horaires en entrée ».
Exemples (en supposant que le fuseau horaire local 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 type « timestamp without time zone » et l'interprète comme une date MST (UTC-7) qui est ensuite converti en PST (UTC-8) pour l'affichage. Le second exemple prend un type timestamp spécifié en EST (UTC-5) et le 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.
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]](images/note.png)
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 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é comme une fonctionnalité : le but est de permettre à une transaction seule d'avoir une notion cohérente de l'heure « courante », donc les modifications multiples à l'intérieur de la même transaction partagent la même heure.
D'autres systèmes de bases de données pourraient mettre à jour ces valeurs plus fréquemment.
Il existe aussi la fonction timeofday() qui renvoie l'heure de l'horloge et avance pendant les transactions. Pour des raisons historiques, timeofday() renvoie une chaîne de type text plutôt qu'une valeur de type timestamp :
SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST
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'; -- incorrect dans son utilisation par défaut
![[Astuce]](images/tip.png)
Vous ne voudrez 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.