🌐 9.9. Fonctions et opérateurs pour date/heure

» » »

Précédent	Niveau supérieur	Suivant
9.8. Fonctions de formatage de type de données	Sommaire	9.10. Fonctions de support d'enum

9.9. Fonctions et opérateurs pour date/heure

9.9.1. EXTRACT, date_part
9.9.2. date_trunc
9.9.3. AT TIME ZONE
9.9.4. Date/Heure actuelle
9.9.5. Retarder l'exécution

Tableau 9.32 affiche les fonctions disponibles pour le traitement de valeurs date/heure, avec des détails dans les sections suivantes. Tableau 9.31 illustre le comportement des opérateurs arithmétiques de base (+, *, etc.). Pour les fonctions de formatage, référez-vous à Section 9.8. Vous devez être déjà familier avec les types de données date/heure (voir Section 8.5).

De plus, les opérateurs de comparaison habituels affichés dans Tableau 9.1 sont disponibles pour les types date/heure. Les dates et timestamps (avec ou sans fuseau horaire) sont tous comparables, alors que les heures (avec et sans fuseau horaire) et les intervalles peuvent seulement être comparés aux autres valeurs du même type de données. Lors de la comparaison d'un timestamp sans fuseau horaire à un timestamp avec fuseau horaire, la première valeur est supposée être donnée dans le fuseau horaire indiqué par le paramètre de configuration TimeZone, et est transformée en UTC our comparaison avec la deuxième valeur (qui est déjà en UTC). De façon similaire, une valeur date est supposée représenter minuit dans la zone TimeZone lors de la comparaison avec un timestamp.

Toutes les fonctions et tous les opérateurs décrits ci-dessous, prenant en entrée des arguments de type time ou timestamp viennent en deux variantes : une qui prend le type time with time zone ou timestamp with time zone, et une qui prend le type time without time zone ou timestamp without time zone. Pour ne pas trop grossir la documentation, ces variantes ne sont pas affichées séparément. De plus, les opérateurs + et * viennent sous la forme de paires commutatives (par exemple, à la fois date + integer et integer + date) ; nous ne présentons qu'une seule des deux.

Tableau 9.31. Opérateurs Date/Heure

Opérateur Description Exemple(s)
`date` `+` `integer` → `date` Ajoute un nombre de jours à une date `date '2001-09-28' + 7` → `2001-10-05`
`date` `+` `interval` → `timestamp` Ajouter un interval à une date `date '2001-09-28' + interval '1 hour'` → `2001-09-28 01:00:00`
`date` `+` `time` → `timestamp` Ajoute une heure du jour à une date `date '2001-09-28' + time '03:00'` → `2001-09-28 03:00:00`
`interval` `+` `interval` → `interval` Ajoute des intervalles `interval '1 day' + interval '1 hour'` → `1 day 01:00:00`
`timestamp` `+` `interval` → `timestamp` Ajoute un interval à un timestamp `timestamp '2001-09-28 01:00' + interval '23 hours'` → `2001-09-29 00:00:00`
`time` `+` `interval` → `time` Ajoute un interval à une heure `time '01:00' + interval '3 hours'` → `04:00:00`
`-` `interval` → `interval` Passe un interval en négatif `- interval '23 hours'` → `-23:00:00`
`date` `-` `date` → `integer` Soustrait des dates, produisant le nombre de jours passés `date '2001-10-01' - date '2001-09-28'` → `3`
`date` `-` `integer` → `date` Soustrait un nombre de jours d'une date `date '2001-10-01' - 7` → `2001-09-24`
`date` `-` `interval` → `timestamp` Soustrait un interval d'une date `date '2001-09-28' - interval '1 hour'` → `2001-09-27 23:00:00`
`time` `-` `time` → `interval` Soustrait des heures `time '05:00' - time '03:00'` → `02:00:00`
`time` `-` `interval` → `time` Soustrait un interval d'une heure `time '05:00' - interval '2 hours'` → `03:00:00`
`timestamp` `-` `interval` → `timestamp` Soustrait un interval d'un timestamp `timestamp '2001-09-28 23:00' - interval '23 hours'` → `2001-09-28 00:00:00`
`interval` `-` `interval` → `interval` Soustrait des intervalles `interval '1 day' - interval '1 hour'` → `1 day -01:00:00`
`timestamp` `-` `timestamp` → `interval` Soustrait des timestamps (convertissant des intervalles sur 24 heures en jours, de façon similaire à `justify_hours()`) `timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'` → `1 day 15:00:00`
`interval` `` `double precision` → `interval` Multiplie un interval à une valeur scalaire `interval '1 second' 900` → `00:15:00` `interval '1 day' * 21` → `21 days` `interval '1 hour' * 3.5` → `03:30:00`
`interval` `/` `double precision` → `interval` Divise un interval par une valeur scalaire `interval '1 hour' / 1.5` → `00:40:00`

Opérateur

Description

Exemple(s)

date + integer → date

Ajoute un nombre de jours à une date

date '2001-09-28' + 7 → 2001-10-05

date + interval → timestamp

Ajouter un interval à une date

date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00

date + time → timestamp

Ajoute une heure du jour à une date

date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00

interval + interval → interval

Ajoute des intervalles

interval '1 day' + interval '1 hour' → 1 day 01:00:00

timestamp + interval → timestamp

Ajoute un interval à un timestamp

timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00

time + interval → time

Ajoute un interval à une heure

time '01:00' + interval '3 hours' → 04:00:00

- interval → interval

Passe un interval en négatif

- interval '23 hours' → -23:00:00

date - date → integer

Soustrait des dates, produisant le nombre de jours passés

date '2001-10-01' - date '2001-09-28' → 3

date - integer → date

Soustrait un nombre de jours d'une date

date '2001-10-01' - 7 → 2001-09-24

date - interval → timestamp

Soustrait un interval d'une date

date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00

time - time → interval

Soustrait des heures

time '05:00' - time '03:00' → 02:00:00

time - interval → time

Soustrait un interval d'une heure

time '05:00' - interval '2 hours' → 03:00:00

timestamp - interval → timestamp

Soustrait un interval d'un timestamp

timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00

interval - interval → interval

Soustrait des intervalles

interval '1 day' - interval '1 hour' → 1 day -01:00:00

timestamp - timestamp → interval

Soustrait des timestamps (convertissant des intervalles sur 24 heures en jours, de façon similaire à justify_hours())

timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' → 1 day 15:00:00

interval * double precision → interval

Multiplie un interval à une valeur scalaire

interval '1 second' * 900 → 00:15:00

interval '1 day' * 21 → 21 days

interval '1 hour' * 3.5 → 03:30:00

interval / double precision → interval

Divise un interval par une valeur scalaire

interval '1 hour' / 1.5 → 00:40:00

Tableau 9.32. Fonctions Date/Heure

Fonction Description Exemple(s)
`age` ( `xid` ) → `integer` Renvoie le nombre de transactions entre l'identifiant de transaction fourni et le compteur actuel de transaction.
`mxid_age` ( `xid` ) → `integer` Renvoie le nombre d'identifiant MultiXact entre l'identifiant MultiXact fourni et le compteur actuel de MultiXact.
`age` ( `timestamp`, `timestamp` ) → `interval` Soustrait les arguments, produisant un résultat « symbolique » qui utilise des années et des mois, plutôt que simplement des jours `age(timestamp '2001-04-10', timestamp '1957-06-13')` → `43 years 9 mons 27 days`
`age` ( `timestamp` ) → `interval` Soustrait l'argument de `current_date` (à minuit) `age(timestamp '1957-06-13')` → `62 years 6 mons 10 days`
`clock_timestamp` ( ) → `timestamp with time zone` Date et heure actuelles (change lors de l'exécution d'une requête) ; voir Section 9.9.4 `clock_timestamp()` → `2019-12-23 14:39:53.662522-05`
`current_date` → `date` Date actuelle ; voir Section 9.9.4 `current_date` → `2019-12-23`
`current_time` → `time with time zone` Heure actuelle du jour ; voir Section 9.9.4 `current_time` → `14:39:53.662522-05`
`current_time` ( `integer` ) → `time with time zone` Haure actuelle du jour avec une précision limitée ; voir Section 9.9.4 `current_time(2)` → `14:39:53.66-05`
`current_timestamp` → `timestamp with time zone` Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4 `current_timestamp` → `2019-12-23 14:39:53.662522-05`
`current_timestamp` ( `integer` ) → `timestamp with time zone` Date et heure actuelles (début de la transaction en cours) avec une précision limitée ; voir Section 9.9.4 `current_timestamp(0)` → `2019-12-23 14:39:53-05`
`date_part` ( `text`, `timestamp` ) → `double precision` Obtenir un champ du timestamp (équivalent à `extract`) ; voir Section 9.9.1 `date_part('hour', timestamp '2001-02-16 20:38:40')` → `20`
`date_part` ( `text`, `interval` ) → `double precision` Obtenir un champ interval (équivalent à `extract`) ; voir Section 9.9.1 `date_part('month', interval '2 years 3 months')` → `3`
`date_trunc` ( `text`, `timestamp` ) → `timestamp` Tronque à la précision spécifiée ; voir Section 9.9.2 `date_trunc('hour', timestamp '2001-02-16 20:38:40')` → `2001-02-16 20:00:00`
`date_trunc` ( `text`, `timestamp with time zone`, `text` ) → `timestamp with time zone` Tronque à une précision spécifiée dans le fuseau horaire indiqué ; voir Section 9.9.2 `date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')` → `2001-02-16 13:00:00+00`
`date_trunc` ( `text`, `interval` ) → `interval` Tronque à la précision spécifiée ; voir Section 9.9.2 `date_trunc('hour', interval '2 days 3 hours 40 minutes')` → `2 days 03:00:00`
`extract` ( `field` `from` `timestamp` ) → `double precision` Obtenir un champ du timestamp ; voir Section 9.9.1 `extract(hour from timestamp '2001-02-16 20:38:40')` → `20`
`extract` ( `field` `from` `interval` ) → `double precision` Obtenir un champ interval ; voir Section 9.9.1 `extract(month from interval '2 years 3 months')` → `3`
`isfinite` ( `date` ) → `boolean` Test pour une date finie (différent de +/-infinity) `isfinite(date '2001-02-16')` → `true`
`isfinite` ( `timestamp` ) → `boolean` Test pour un timestamp fini (différent de +/-infinity) `isfinite(timestamp 'infinity')` → `false`
`isfinite` ( `interval` ) → `boolean` Test pour un interval fini (actuellement toujours vrai) `isfinite(interval '4 hours')` → `true`
`justify_days` ( `interval` ) → `interval` Ajuste un interval en convertissant les périodes de 30 jours en mois `justify_days(interval '1 year 65 days')` → `1 year 2 mons 5 days`
`justify_hours` ( `interval` ) → `interval` Ajuste un interval en convertissant les périodes de 24 heures en jour `justify_hours(interval '50 hours 10 minutes')` → `2 days 02:10:00`
`justify_interval` ( `interval` ) → `interval` Adjuste un interval en utilisant `justify_days` et `justify_hours`, avec des ajustements de signe supplémentaire `justify_interval(interval '1 mon -1 hour')` → `29 days 23:00:00`
`localtime` → `time` Heure actuelle du jour ; voir Section 9.9.4 `localtime` → `14:39:53.662522`
`localtime` ( `integer` ) → `time` Heure actuelle du jour avec une précision limitée ; voir Section 9.9.4 `localtime(0)` → `14:39:53`
`localtimestamp` → `timestamp` Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4 `localtimestamp` → `2019-12-23 14:39:53.662522`
`localtimestamp` ( `integer` ) → `timestamp` Date et heure actuelles (début de la transaction en cours), avec une précision limitée ; voir Section 9.9.4 `localtimestamp(2)` → `2019-12-23 14:39:53.66`
`make_date` ( `year` `int`, `month` `int`, `day` `int` ) → `date` Crée une date à partir des champs année, mois et jour `make_date(2013, 7, 15)` → `2013-07-15`
`make_interval` ( [ `years` `int` [, `months` `int` [, `weeks` `int` [, `days` `int` [, `hours` `int` [, `mins` `int` [, `secs` `double precision` ]]]]]]] ) → `interval` Crée un interval à partir des champs année, mois, semaine, jour, heure, minute et seconde, chacun ayant zéro comme valeur par défaut `make_interval(days => 10)` → `10 days`
`make_time` ( `hour` `int`, `min` `int`, `sec` `double precision` ) → `time` Crée une heure à partir des champs heure, minute et seconde `make_time(8, 15, 23.5)` → `08:15:23.5`
`make_timestamp` ( `year` `int`, `month` `int`, `day` `int`, `hour` `int`, `min` `int`, `sec` `double precision` ) → `timestamp` Crée un timestamp à partir des champs année, mois, jour, heure, minute et seconde `make_timestamp(2013, 7, 15, 8, 15, 23.5)` → `2013-07-15 08:15:23.5`
`make_timestamptz` ( `year` `int`, `month` `int`, `day` `int`, `hour` `int`, `min` `int`, `sec` `double precision` [, `timezone` `text` ] ) → `timestamp with time zone` Crée un timestamp avec fuseau horaire à partir des champs année, mois, jour, heure, minute et seconde ; si `timezone` n'est pas spécifié, le fuseau horaire actuel est utilisé `make_timestamptz(2013, 7, 15, 8, 15, 23.5)` → `2013-07-15 08:15:23.5+01`
`now` ( ) → `timestamp with time zone` Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4 `now()` → `2019-12-23 14:39:53.662522-05`
`statement_timestamp` ( ) → `timestamp with time zone` Date et heure actuelles (début de la requête en cours) ; voir Section 9.9.4 `statement_timestamp()` → `2019-12-23 14:39:53.662522-05`
`timeofday` ( ) → `text` Date et heure actuelles (comme `clock_timestamp`, mais sous la forme d'une chaîne de type `text`) ; voir Section 9.9.4 `timeofday()` → `Mon Dec 23 14:39:53.662522 2019 EST`
`transaction_timestamp` ( ) → `timestamp with time zone` Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4 `transaction_timestamp()` → `2019-12-23 14:39:53.662522-05`
`to_timestamp` ( `double precision` ) → `timestamp with time zone` Convertit un epoch Unix (secondes depuis 1970-01-01 00:00:00+00) en timestamp avec fuseau horaire `to_timestamp(1284352323)` → `2010-09-13 04:32:03+00`

Fonction

Description

Exemple(s)

age ( xid ) → integer

Renvoie le nombre de transactions entre l'identifiant de transaction fourni et le compteur actuel de transaction.

mxid_age ( xid ) → integer

Renvoie le nombre d'identifiant MultiXact entre l'identifiant MultiXact fourni et le compteur actuel de MultiXact.

age ( timestamp, timestamp ) → interval

Soustrait les arguments, produisant un résultat « symbolique » qui utilise des années et des mois, plutôt que simplement des jours

age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27 days

age ( timestamp ) → interval

Soustrait l'argument de current_date (à minuit)

age(timestamp '1957-06-13') → 62 years 6 mons 10 days

clock_timestamp ( ) → timestamp with time zone

Date et heure actuelles (change lors de l'exécution d'une requête) ; voir Section 9.9.4

clock_timestamp() → 2019-12-23 14:39:53.662522-05

current_date → date

Date actuelle ; voir Section 9.9.4

current_date → 2019-12-23

current_time → time with time zone

Heure actuelle du jour ; voir Section 9.9.4

current_time → 14:39:53.662522-05

current_time ( integer ) → time with time zone

Haure actuelle du jour avec une précision limitée ; voir Section 9.9.4

current_time(2) → 14:39:53.66-05

current_timestamp → timestamp with time zone

Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4

current_timestamp → 2019-12-23 14:39:53.662522-05

current_timestamp ( integer ) → timestamp with time zone

Date et heure actuelles (début de la transaction en cours) avec une précision limitée ; voir Section 9.9.4

current_timestamp(0) → 2019-12-23 14:39:53-05

date_part ( text, timestamp ) → double precision

Obtenir un champ du timestamp (équivalent à extract) ; voir Section 9.9.1

date_part('hour', timestamp '2001-02-16 20:38:40') → 20

date_part ( text, interval ) → double precision

Obtenir un champ interval (équivalent à extract) ; voir Section 9.9.1

date_part('month', interval '2 years 3 months') → 3

date_trunc ( text, timestamp ) → timestamp

Tronque à la précision spécifiée ; voir Section 9.9.2

date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00

date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone

Tronque à une précision spécifiée dans le fuseau horaire indiqué ; voir Section 9.9.2

date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') → 2001-02-16 13:00:00+00

date_trunc ( text, interval ) → interval

Tronque à la précision spécifiée ; voir Section 9.9.2

date_trunc('hour', interval '2 days 3 hours 40 minutes') → 2 days 03:00:00

extract ( field from timestamp ) → double precision

Obtenir un champ du timestamp ; voir Section 9.9.1

extract(hour from timestamp '2001-02-16 20:38:40') → 20

extract ( field from interval ) → double precision

Obtenir un champ interval ; voir Section 9.9.1

extract(month from interval '2 years 3 months') → 3

isfinite ( date ) → boolean

Test pour une date finie (différent de +/-infinity)

isfinite(date '2001-02-16') → true

isfinite ( timestamp ) → boolean

Test pour un timestamp fini (différent de +/-infinity)

isfinite(timestamp 'infinity') → false

isfinite ( interval ) → boolean

Test pour un interval fini (actuellement toujours vrai)

isfinite(interval '4 hours') → true

justify_days ( interval ) → interval

Ajuste un interval en convertissant les périodes de 30 jours en mois

justify_days(interval '1 year 65 days') → 1 year 2 mons 5 days

justify_hours ( interval ) → interval

Ajuste un interval en convertissant les périodes de 24 heures en jour

justify_hours(interval '50 hours 10 minutes') → 2 days 02:10:00

justify_interval ( interval ) → interval

Adjuste un interval en utilisant justify_days et justify_hours, avec des ajustements de signe supplémentaire

justify_interval(interval '1 mon -1 hour') → 29 days 23:00:00

localtime → time

Heure actuelle du jour ; voir Section 9.9.4

localtime → 14:39:53.662522

localtime ( integer ) → time

Heure actuelle du jour avec une précision limitée ; voir Section 9.9.4

localtime(0) → 14:39:53

localtimestamp → timestamp

Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4

localtimestamp → 2019-12-23 14:39:53.662522

localtimestamp ( integer ) → timestamp

Date et heure actuelles (début de la transaction en cours), avec une précision limitée ; voir Section 9.9.4

localtimestamp(2) → 2019-12-23 14:39:53.66

make_date ( year int, month int, day int ) → date

Crée une date à partir des champs année, mois et jour

make_date(2013, 7, 15) → 2013-07-15

make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval

Crée un interval à partir des champs année, mois, semaine, jour, heure, minute et seconde, chacun ayant zéro comme valeur par défaut

make_interval(days => 10) → 10 days

make_time ( hour int, min int, sec double precision ) → time

Crée une heure à partir des champs heure, minute et seconde

make_time(8, 15, 23.5) → 08:15:23.5

make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp

Crée un timestamp à partir des champs année, mois, jour, heure, minute et seconde

make_timestamp(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5

make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) → timestamp with time zone

Crée un timestamp avec fuseau horaire à partir des champs année, mois, jour, heure, minute et seconde ; si timezone n'est pas spécifié, le fuseau horaire actuel est utilisé

make_timestamptz(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5+01

now ( ) → timestamp with time zone

Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4

now() → 2019-12-23 14:39:53.662522-05

statement_timestamp ( ) → timestamp with time zone

Date et heure actuelles (début de la requête en cours) ; voir Section 9.9.4

statement_timestamp() → 2019-12-23 14:39:53.662522-05

timeofday ( ) → text

Date et heure actuelles (comme clock_timestamp, mais sous la forme d'une chaîne de type text) ; voir Section 9.9.4

timeofday() → Mon Dec 23 14:39:53.662522 2019 EST

transaction_timestamp ( ) → timestamp with time zone

Date et heure actuelles (début de la transaction en cours) ; voir Section 9.9.4

transaction_timestamp() → 2019-12-23 14:39:53.662522-05

to_timestamp ( double precision ) → timestamp with time zone

Convertit un epoch Unix (secondes depuis 1970-01-01 00:00:00+00) en timestamp avec fuseau horaire

to_timestamp(1284352323) → 2010-09-13 04:32:03+00

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

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

Cette expression renvoie true quand deux périodes de temps (définies par leur point final) se chevauchent et false dans le cas contraire. Les points finaux peuvent être indiqués sous la forme d'une paires de dates, heures ou dates et heures, ou à une date, heure ou date et heure suivie par un intervalle. Quand une paire de valeurs est fournie, soit le début soit la fin peuvent être écrit en premier ; OVERLAPS prend automatiquement la valeur la plus ancienne de la paire comme valeur de départ. Chaque période de temps est considérée comme représentant l'intervalle à moitié ouvert start <= time < end, sauf si start et end sont égaux, auquel cas elle représente uniquement cet instant. Ceci signifie que deux périodes de temps avec seulement le point final en commun ne se surchargent pas.

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
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Résultat : false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
       (DATE '2001-10-30', DATE '2001-10-31');
Résultat : true

Lors de l'ajout (ou de la soustraction) d'une valeur interval à une valeur timestamp with time zone, le nombre de jours augmente ou décrémente la date de timestamp with time zone par le nombre de jours indiqué, conservant à l'identique l'heure du jour. Si cela intervient à une période de changement d'heure pour le fuseau horaire utilisé, cela signifie que interval '1 day' n'est pas forcément équivalent à interval '24 hours'. Par exemple, avec le fuseau horaire America/Denver :

SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
Résultat : 2005-04-03 12:00:00-06
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
Résultat : 2005-04-03 13:00:00-06

Ceci survient parce qu'une heure a été ignorée à cause d'un changement d'heure à 2005-04-03 02:00:00 pour le fuseau horaire America/Denver.

Notez qu'il peut avoir une ambiguïté dans le champ months renvoyé par la fonction age parce que des mois différents ont des numéros de jour différents. L'approche de PostgreSQL utilise le mois de la date la plus ancienne lors du calcul de mois partiels. Par exemple, age('2004-06-01', '2004-04-30') utilise avril pour renvoyer 1 mon 1 day, alors que mai renverrait 1 mon 2 days parce que mai a 31 jours alors qu'avril n'en a que 30.

La soustraction de dates et de timestamps peut aussi être complexe. Une façon simple conceptuellement de réaliser une soustraction est de convertir chaque valeur en un nombre de secondes en utilisant EXTRACT(EPOCH FROM ...), puis de soustraire les résultats ; ceci donne un nombre de secondes entre les deux valeurs. Ceci provoquera un ajustement suivant le nombre de jours pour chaque mois, les changements de fuseaux horaires et les ajustements pour les changements d'heure. La soustraction de valeurs date ou timestamp avec l'opérateur « - » renvoie le nombre de jours (24 heures) et les heures/minutes/secondes entre les valeurs, en faisant les mêmes ajustements. La fonction age renvoie les années, mois, jours et heures/minutes/secondes, soustrayant champ par champ, puis ajustant les valeurs négatives. Les requêtes suivantes illustrent les différences dans ces approches. Les résultats correspondent au fuseau horaire timezone = 'US/Eastern' ; il y a un changement d'heure entre les deux dates utilisées :

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Résultat : 10537200
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
        / 60 / 60 / 24;
Résultat : 121.958333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Résultat : 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Résultat : 4 mons

9.9.1. `EXTRACT`, `date_part`

EXTRACT(field FROM source)

La fonction extract récupère les champs tels que l'année ou l'heure de valeurs date/heure. source doit être l'expression d'une valeur de type timestamp, date, time ou interval. (Les données de type timestamp ou time peuvent être avec ou sans fuseau horaire.) field est un identifiant ou une chaîne qui sélectionne le champ à extraire à partir de la valeur source. Les champs ne sont pas tous valides pour chaque type de donnée en entrée ; par exemple, les champs plus petit qu'un jour ne peuvent pas être extraits à partir d'une donnée de type date, alors que les champs d'un jour ou plus ne peuvent pas être extraits d'un type time. La fonction extract renvoie des valeurs de type double precision. Voici les noms de champ valides :

century

Le siècle ; pour les valeurs de type interval, le champ year divisé par 100

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
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
Result: 1
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
Result: -1
SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
Result: 20

day

Le jour du mois (1–31) ; pour les valeurs interval, le nombre de jours

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

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 du dimanche (0) au samedi (6)

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

Notez que la numérotation du jour de la semaine d'après extract diffère de celle de la fonction to_char(..., 'D').

doy

Le jour de l'année (1–365/366)

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

epoch

Pour les valeurs de type timestamp with time zone, le nombre de secondes depuis le 1er janvier 1970 à minuit UTC. Ce nombre est négatif pour les timestamps avant cette valeur. Pour les valeurs de type date et timestamp, le nombre de secondes depuis le 1er janvier 1970 à minuit, heure locale, sans regard au fuseau horaire ou aux règles de changement d'heure. 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.12-08');
Résultat : 982384720.12
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
Résultat : 982355920.12
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Résultat : 442800

Vous pouvez convertir une valeur epoch en une valeur de type timestamp with time zone avec la fonction to_timestamp :

SELECT to_timestamp(982384720.12);
Résultat : 2001-02-17 04:38:40.12+00

Attention que l'application de to_timestamp à un epoch extrait d'une valeur date ou timestamp pourrait produire un résultat trompeur : le résultat supposera en fait que la valeur originale a été donnée en UTC, ce qui pourrait ne pas être le cas.

hour

Le champ heure (0–23 pour les données de type timestamp, sans contrainte pour les intervals)

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

isodow

Le jour de la semaine du lundi (1) au dimanche (7)

SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
Résultat : 7

Ceci est identique à dow sauf pour les dimanches. Ceci correspond à la numérotation du jour de la semaine d'après ISO 8601.

isoyear

L'année suivant la numérotation semaine ISO 8601 dans laquelle la date tombe

SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
Résultat : 2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
Résultat : 2006

Chaque année suivant la numérotation semaine ISO 8601 commence le lundi de la semaine contenant le 4 janvier, donc un janvier débutant tôt ou un décembre finissant tard pourrait faire que l'année ISO soit différente de l'année grégorienne. Voir le champ week pour plus d'informations.

julian

La Date Julien correspondant à la date ou à l'horodatage. Les horodatages qui ne sont pas à minuit heure locale résultent en une valeur fractionnelle. Voir Section B.7 pour plus d'informations.

SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
Result: 2453737
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
Result: 2453737.5

microseconds

Le champ des secondes, incluant la partie fractionnelle, multiplié par 1 000 000 ; notez que ceci inclut des secondes complètes

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

millennium

Le millénaire ; pour les valeurs de type interval, le champ year divisé par 1000

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

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

milliseconds

Le champ des secondes, incluant la partie fractionnelle, multipliée par 1000. Notez que ceci inclut des secondes complètes.

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

minute

Le champ des minutes (0–59)

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

month

Le numéro du mois dans l'année (1–12) ; pour les valeurs interval, le numéro du 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 de l'année (1–4) dans laquelle la date se trouve

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

second

Le champs secondes, incluant toutes secondes fractionnelles

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, à partir d'UTC, mesuré en secondes. Les valeurs positives correspondent aux fuseaux horaires à l'est d'UTC, les valeurs négatives aux zones à l'ouest d'UTC. (Techniquement, PostgreSQL n'utilise pas UTC parce que les secondes perdues ne sont pas gérées.)

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 de cette année d'après la numérotation de semaine de l'ISO 8601. Par définition, les semaines ISO commencent les lundis et la première semaine de l'année contient le 4 janvier de l'année. Autrement dit, le premier jeudi d'une année est dans la semaine 1 de cette année.

Dans le système ISO de numérotation des semaines, il est possible que les dates de début janvier soient dans la semaine 52 ou 53 de l'année précédente, et pour les dates de fin décembre de faire partie de la première semaine de l'année suivante. Par exemple, le 1er janvier 2005 fait partie de la semaine 53 de l'année 2004, et le 1er janvier 2006 fait partie de la semaine 52 de l'année 2005, alors que le 31 décembre 2012 fait partie de la première semaine de 2013. Il est recommandé d'utiliser le champ isoyear avec week pour obtenir des résultats cohérents.

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'existe pas d'année 0 AD, donc soustraire BC années de AD années doit se faire avec prudence.

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

Lors du traitement d'une valeur interval, la fonction extract produit des valeurs de champ qui correspondent à l'interprétation utilisée par la fonction interval en sortie. Ceci peut produire des résultats surprenants si l'une commence avec une représentation d'interval non normalisé. Par exemple :

SELECT INTERVAL '80 minutes';
Résultat : 01:20:00
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
Résultat : 20

Note

Quand la valeur en entrée est +/-Infinity, extract renvoie +/-Infinity pour les champs à augmentation monotonique (epoch, julian, year, isoyear, decade, century et millennium). Pour les autres champs, NULL est renvoyé. Les versions de PostgreSQL avant la 9.6 renvoyaient zéro pour tous les cas si l'entrée est infinie.

La fonction extract a principalement pour intérêt un traitement calculé. Pour le formatage de valeurs date/heure en vue d'un affichage, voir Section 9.8.

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

date_part('field', source)

Notez qu'ici le paramètre field doit être une chaîne, et non pas un nom. Les noms de champs valides 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(field, source [, time_zone ])

source est une expression de valeur de type timestamp, timestamp with time zone ou interval. (Les valeurs de type date et time sont converties automatiquement, et respectivement, en timestamp ou interval) field sélectionne la précision pour le troncage de la valeur en entrée. La valeur de retour est de type timestamp, timestamp with time zone ou interval, et elle contient tous les champs qui sont moins significatifs que le champ sélectionné, qui est initialisé à zéro (ou un pour le jour et le mois).

Les valeurs valides pour field sont :

microseconds

milliseconds

second

minute

hour

day

week

month

quarter

year

decade

century

millennium

Quand la valeur en entrée est de type timestamp with time zone, la troncature est réalisée en respectant un fuseau horaire particulier ; par exemple, la troncature à day crée une valeur à minuit de ce fuseau. Par défaut, une troncature se fait en respectant le paramètre TimeZone mais l'argument optionnel time_zone peut être fourni pour indiquer un fuseau horaire différent. Le nom du fuseau horaire peut être indiqué parmi toutes les façons décrites dans Section 8.5.3.

Un fuseau horaire ne peut pas être indiqué lors du traitement de données timestamp without time zone ou interval. Ce sont toujours des valeurs prises directement, sans interprétation.

Exemples (en supposant que le fuseau horaire local est America/New_York) :

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
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Résultat : 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Résultat : 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Résultat : 3 days 02:00:00

9.9.3. `AT TIME ZONE`

L'opérateur AT TIME ZONE convertit l'horodatage sans sans fuseau horaire en horodatage avec fuseau horaire, et des valeurs time with time zone dans différents fuseaux horaires. Tableau 9.33 montre les variantes.

Tableau 9.33. Variantes AT TIME ZONE

Opérateur Description Exemple(s)
`timestamp without time zone` `AT TIME ZONE` `zone` → `timestamp with time zone` Convertit l'horodatage donné sans fuseau horaire en horodatage avec fuseau horaire en supposant que la valeur indiquée est dans le fuseau horaire nommé. `timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'` → `2001-02-17 03:38:40+00`
`timestamp with time zone` `AT TIME ZONE` `zone` → `timestamp without time zone` Convertit l'horodatage donné avec fuseau horaire en horodatage sans sans fuseau horaire, comme si l'heure apparaissait dans ce fuseau. `timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'` → `2001-02-16 18:38:40`
`time with time zone` `AT TIME ZONE` `zone` → `time with time zone` Convertit l'heure donnée avec fuseau horaire dans un nouveau fuseau horaire. Comme aucune date n'est fournie, cela utilise le décalage UTC actuellement actif pour le fuseau horaire de destination. `time with time zone '05:34:17-05' at time zone 'UTC'` → `10:34:17+00`

Opérateur

Description

Exemple(s)

timestamp without time zone AT TIME ZONE zone → timestamp with time zone

Convertit l'horodatage donné sans fuseau horaire en horodatage avec fuseau horaire en supposant que la valeur indiquée est dans le fuseau horaire nommé.

timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00

timestamp with time zone AT TIME ZONE zone → timestamp without time zone

Convertit l'horodatage donné avec fuseau horaire en horodatage sans sans fuseau horaire, comme si l'heure apparaissait dans ce fuseau.

timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver' → 2001-02-16 18:38:40

time with time zone AT TIME ZONE zone → time with time zone

Convertit l'heure donnée avec fuseau horaire dans un nouveau fuseau horaire. Comme aucune date n'est fournie, cela utilise le décalage UTC actuellement actif pour le fuseau horaire de destination.

time with time zone '05:34:17-05' at time zone 'UTC' → 10:34:17+00

Dans ces expressions, le fuseau horaire désiré zone peut être spécifié soit sous la forme d'une valeur de type texte (par exemple 'America/Los_Angeles') soit comme un intervalle (par exemple INTERVAL '-08:00'). Dans le cas du texte, le nom du fuseau horaire peut être indiqué d'une des façons décrites dans Section 8.5.3. Dans le cas de l'intervalle, il est uniquement utile pour les fuseaux qui ont des décalages fixes d'UTC, donc ce n'est pas très courant.

Exemples (en supposant que le paramètre TimeZone a comme valeur actuelle 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' 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 le paramétrage actuel de TimeZone. Le deuxième exemple décale l'horodatage avec fuseau horaire dans le fuseau horaire indiqué, et envoie la valeur sans fuseau horaire. Ceci permet le stockage et l'affichage de valeurs différentes de la configuration actuelle de TimeZone. Le troisième exemple convertit l'heure de Tokyo en heure de Chicago.

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

9.9.4. Date/Heure actuelle

PostgreSQL fournit un certain nombre de fonctions qui renvoient des valeurs relatives à la date et l'heure actuelles. Ces fonctions SQL renvoient des valeurs basées sur l'heure de début de la transaction :

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

CURRENT_TIME et CURRENT_TIMESTAMP renvoient des valeurs avec fuseau horaire ; LOCALTIME et LOCALTIMESTAMP renvoient des valeurs sans fuseau horaire.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME et LOCALTIMESTAMP peuvent prendre en option un paramètre de précision qui impose que le résultat soit arrondi à ce nombre de chiffres dans le champ des secondes. Sans 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 : 2019-12-23
SELECT CURRENT_TIMESTAMP;
Résultat : 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
Résultat : 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
Résultat : 2019-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 lors de la transaction courante. Ceci est considéré comme une fonctionnalité : le but est de permettre à une même transaction d'avoir une notion cohérente de l'heure « actuelle », pour que plusieurs modifications au sein de la même transactions arrivent au même moment grâce à un même horodatage.

Note

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

PostgreSQL fournit aussi des fonctions qui renvoient l'heure de début de la requête en cours, ainsi que l'heure actuelle au moment où la fonction est appelée. La liste complète des fonctions d'heure, ne faisant pas parti du standard SQL, est la suivante :

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

transaction_timestamp() est équivalent à CURRENT_TIMESTAMP, mais est nommée ainsi pour refléter clairement ce qu'il renvoie. statement_timestamp() renvoie l'heure de début de la requête en cours (plus spécifiquement, l'heure de réception du dernier message de commande du client). statement_timestamp() et transaction_timestamp() renvoient la même valeur lors de la première requête d'une transaction, mais pourrait différer lors des requêtes suivantes. clock_timestamp() renvoie l'heure actuelle, et de ce fait, sa valeur changera y compris au sein d'une même requête SQL. timeofday() est une fonction historique de PostgreSQL. Tout comme clock_timestamp(), elle renvoie l'heure actuelle, mais formatée sous la forme d'une chaîne de type text plutôt que sous la forme d'une valeur de type timestamp with time zone. now() est un équivalent historique de PostgreSQL pour transaction_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 actuelle (encore une fois, interprété comme l'heure de début de la transaction). De ce fait, les trois suivants renvoient tous 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.)

9.9.5. Retarder l'exécution

Les fonctions suivants sont disponibles pour retarder l'exécution du traitement du serveur :

pg_sleep ( double precision )
pg_sleep_for ( interval )
pg_sleep_until ( timestamp with time zone )

pg_sleep cause la pause du processus de la session en cours pour le nombre indiqué de secondes. Un délai peut être spécifié avec une partie fractionnelle pour les secondes pg_sleep_for est une fonction pratique pour indiquer le temps de pause sous la forme d'un interval. pg_sleep_until est une autre fonction pratique pour indiquer une heure de réveil, à la place d'une durée de pause. 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 de pause est spécifique à la plateforme ; 0,01 secondes est une valeur assez générale. La durée de pause sera au moins aussi longue que celle indiquée. Elle pourra être plus longue suivant des facteurs comme la charge du serveur. En particulier, pg_sleep_until ne garantie pas de se réveiller précisément à l'heure indiquée, mais elle ne se réveillera pas avant.

Avertissement

Assurez-vous que votre session ne détient pas plus de verrous que nécessaire lors de l'appel de pg_sleep ou une de ses variantes. Sinon, d'autres sessions pourraient avoir à attendre le processus en attente, ralentissant le système entier.

9.9. Fonctions et opérateurs pour date/heure

9.9.1. EXTRACT, date_part

Note

9.9.2. date_trunc

9.9.3. AT TIME ZONE

9.9.4. Date/Heure actuelle

Note

Astuce

9.9.5. Retarder l'exécution

Note

Avertissement

9.9.1. `EXTRACT`, `date_part`

9.9.2. `date_trunc`

9.9.3. `AT TIME ZONE`