PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Fonctions et opérateurs » Fonctions et opérateurs sur date/heure

9.9. Fonctions et opérateurs sur date/heure

Le Tableau 9.31 affiche les fonctions disponibles pour le traitement des valeurs date et heure. Les détails sont présentés dans les sous-sections qui suivent. Le Tableau 9.30 illustre les comportements des opérateurs arithmétiques basiques (+, *, etc.). Pour les fonctions de formatage, on peut se référer à la Section 9.8. Il est important d'être familier avec les informations de base concernant les types de données date/heure de la 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 opérateurs décrits ci-dessous qui acceptent une entrée de type time ou timestamp acceptent deux variantes : une avec time with time zone ou timestamp with time zone et une autre avec time without time zone ou timestamp without time zone. Ces variantes ne sont pas affichées séparément. De plus, les opérateurs + et * sont commutatifs (par exemple, date + integer et integer + date) ; une seule possibilité est présentée ici.

Tableau 9.30. 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: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' (jours)
- 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.31. Fonctions date/heure

FonctionCode de retourDescriptionExempleRésultat
age(timestamp, timestamp) intervalSoustrait les arguments, ce qui produit un résultat « symbolique » en années, mois, plutôt qu'en joursage(timestamp '2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
age(timestamp)intervalSoustrait à la date courante (current_date à minuit)age(timestamp '1957-06-13')43 years 8 mons 3 days
clock_timestamp() timestamp with time zoneDate et heure courantes (change pendant l'exécution de l'instruction) ; voir la Section 9.9.4   
current_date dateDate courante ; voir la Section 9.9.4  1
current_time time with time zoneHeure courante ; voir la Section 9.9.4   
current_timestamp timestamp with time zoneDate et heure courantes (début de la transaction en cours) ; voir la Section 9.9.4   
date_part(text, timestamp) double precisionObtenir un sous-champ (équivalent à extract) ; voir la 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 la Section 9.9.1 date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp) timestampTronquer à la précision indiquée ; voir la 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 zoneTronque à une précision indiquée dans le fuseau horaire précisé ; 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)intervalTronque à la précision demandée ; voir Section 9.9.2 date_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00
extract(champ from timestamp) double precisionObtenir un sous-champ ; voir la Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40')20
extract(champ from interval)double precisionObtenir un sous-champ ; voir la Section 9.9.1 extract(month from interval '2 years 3 months')3
isfinite(date) booleanTeste si la date est finie (donc différente de +/-infinity)isfinite(date '2001-02-16')true
isfinite(timestamp) booleanTeste si l'estampille temporelle est finie (donc différente de +/-infinity)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)booleanTeste si l'intervalle est finiisfinite(interval '4 hours')true
justify_days(interval) intervalAjuste l'intervalle pour que les périodes de 30 jours soient représentées comme des moisjustify_days(interval '35 days')1 mon 5 days
justify_hours(interval) intervalAjuste l'intervalle pour que les périodes de 24 heures soient représentées comme des joursjustify_hours( interval '27 hours')1 day 03:00:00
justify_interval(interval) intervalAjuste l'intervalle en utilisant justify_days et justify_hours, avec des signes supplémentaires d'ajustementjustify_interval(interval '1 mon -1 hour')29 days 23:00:00
localtime timeHeure du jour courante ; voir la Section 9.9.4   
localtimestamp timestampDate et heure courantes (début de la transaction) ; voir la Section 9.9.4   
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 DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) interval Crée un intervalle à partir des champs année, mois, semaine, jour, heure, minute et seconde 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 horodatage à 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 horodatage avec fuseau horaire à partir des champs année, mois, jour, heure, minute et secondes. Si le fuseau horaire timezone n'est pas indiqué, le fuseau horaire actuel est utilisé. make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5+01
now() timestamp with time zoneDate et heure courantes (début de la transaction) ; voir la Section 9.9.4   
statement_timestamp() timestamp with time zoneDate et heure courantes (début de l'instruction en cours) ; voir Section 9.9.4   
timeofday() textDate et heure courantes (comme clock_timestamp mais avec une chaîne de type text) ; voir la Section 9.9.4   
transaction_timestamp() timestamp with time zoneDate et heure courantes (début de la transaction en cours) ; voir Section 9.9.4   
to_timestamp(double precision) timestamp with time zoneConvertit l'epoch Unix (secondes depuis le 1er janvier 1970 00:00:00+00) en timestampto_timestamp(1284352323)2010-09-13 04:32:03+00

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 leurs extrémités) se chevauchent, et faux dans le cas contraire. Les limites peuvent être indiquées comme des paires de dates, d'heures ou de timestamps ; ou comme une date, une heure ou un timestamp suivi d'un intervalle. Quand une paire de valeurs est fournie, soit le début soit la fin doit être écrit en premier ; OVERLAPS prend automatiquement la valeur la plus ancienne dans la paire comme valeur de départ. Chaque période de temps est considérée comme représentant l'intervalle à moitié ouvert début1 <= longueur1 < fin2, sauf si début1 et fin2 sont identiques, auquel cas ils représentent un instant précis. Cela signifie en fait que deux périodes de temps avec seulement un 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 de type interval à une valeur de type timestamp with time zone, le composant jours incrémente (ou décrémente) la date du timestamp with time zone du nombre de jours indiqué, en conservant la même heure. Avec les modifications occasionnées par les changements d'heure (avec un fuseau horaire de session qui reconnaît DST), cela signifie qu'un interval '1 day' n'est pas forcément égal à un interval '24 hours'. Par exemple, avec le fuseau horaire de la session configuré à America/Denver :

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

Ceci survient parce qu'une heure a été ignorée à cause d'une modification dans les changements d'heure sur 2005-04-03 02:00:00 dans le fuseau horaire America/Denver.

Il peut y avoir une ambiguïté dans le nombre de months retournés par age, car les mois n'ont pas tous le même nombre de jours. 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 ramener 1 mon 1 day, alors qu'utiliser mai aurait ramener 1 mon 2 days, car mai a 31 jours alors qu'avril n'en a que 30.

La soustraction de données de type date et timestamp peut aussi être complexe. Une façon simple conceptuellement de réaliser cette soustraction revient à convertir chaque valeur en un nombre de secondes en utilisant EXTRACT(EPOCH FROM ...), puis en soustrayant les résultats ; ceci produit le nombre de secondes entre les deux valeurs. Un ajustement aura lieu pour le nombre de jours sur chaque mois, les changements de fuseau horaire, et les décalages horaires. La soustraction de données de type date ou timestamp avec l'opérateur « - » renvoie le nombre de jours (sur 24 heures) et les heures/minutes/secondes entre les valeurs, réalisant les mêmes ajustements. La fonction age renvoie les années, mois, jours et heures/minutes/secondes, réalisant une soustraction champ par champ, puis en ajustant les valeurs négatives des champs. Les requêtes suivantes illustrent les différences parmi ces approches. Les résultats en exemple ont été réalisés avec la configuration 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 (champ FROM source)

La fonction extract récupère des sous-champs de valeurs date/heure, tels que l'année ou l'heure. source est une expression de valeur de type timestamp, time ou interval. (Les expressions de type date sont converties en timestamp et peuvent aussi être utilisées.) champ est un identifiant ou une chaîne qui sélectionne le champ à extraire de la valeur source. La fonction extract renvoie des valeurs de type double precision. La liste qui suit présente les noms de champs valides :

century

Le siècle.

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

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

day

Pour les valeurs de type timestamp, le champ du jour (du mois), donc entre 1 et 31 ; pour les valeurs de type 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

Cette numérotation du jour de la semaine est différente de celle de la fonction to_char(..., 'D').

doy

Le jour de l'année (de 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 (exactement depuis le 1970-01-01 00:00:00 UTC). Ce nombre est négatif pour les timestamps avant cette valeur. Pour les valeurs de type date et timestamp, le nombre nominal de secondes depuis le 1er janvier 1970 00h00, sans regard au fuseau horaire ou aux règles de changement d'heure. Pour les valeurs de type interval, il s'agit du nombre total de secondes dans l'intervalle.

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.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 vers un timestamp with time zone avec 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).

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 le dimanche. Cela correspond à la numérotation du jour de la semaine suivant le format ISO 8601.

isoyear

L'année ISO dans laquelle se trouve la date (non applicable aux intervalles).

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 ISO commence avec le lundi de la semaine contenant le 4 janvier, donc soit début janvier, soit fin décembre. L'année ISO peut être différente de l'année grégorienne. Voir le champ week pour plus d'informations.

Ce champ n'est disponible que depuis la version 8.3 de PostgreSQL.

julian

La Date Julien correspondant à la date ou à l'horodatage (non applicable aux intervalles). 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 secondes, incluant la partie décimale, multiplié par 1 000 000. Cela inclut l'intégralité des secondes.

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

Le millénaire.

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

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

milliseconds

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

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

Le champ minutes (0 - 59).

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

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

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

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

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

Le trimestre (1 - 4) dont le jour fait partie.

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

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

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. (Techniquement, PostgreSQL n'utilise pas UTC, car les secondes intercalaires 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 dans l'année ISO, à laquelle appartient le jour. Par définition ISO, les semaines commencent le lundi et la première semaine d'une année contient le 4 janvier de cette année. Autrement dit, le premier jeudi d'une année se trouve dans la première semaine de cette année.

Dans la définition ISO, il est possible que les premiers jours de janvier fassent partie de la semaine 52 ou 53 de l'année précédente. Il est aussi possible que les derniers jours de décembre fassent partie de la première semaine de l'année suivante. Par exemple, 2005-01-01 fait partie de la semaine 53 de l'année 2004 et 2006-01-01 fait partie de la semaine 52 de l'année 2005, alors que 2012-12-31 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. Il n'y a pas de 0 AD, la soustraction d'années BC aux années AD nécessite donc une attention particulière.

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

Note

Quand la valeur en entrée est +/-Infinity, extract renvoie +/-Infinity pour les champs incrémentés de façon monotonique (epoch, julian, year, isoyear, decade, century et millennium). Pour les autres champs, NULL est renvoyé. Les versions de PostgreSQL antérieures à la 9.6 renvoyaient 0 pour tous les cas de saisie infinie.

La fonction extract a pour but principal l'exécution de calculs. Pour le formatage des valeurs date/heure en vue de leur affichage, voir la Section 9.8.

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

date_part('champ', source)

Le paramètre champ est obligatoirement une valeur de type chaîne et non pas un nom. Les noms de champ valide pour date_part sont les mêmes que pour extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Résultat : 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Résultat : 4

9.9.2. date_trunc

La fonction date_trunc est conceptuellement similaire à la fonction trunc pour les nombres.

date_trunc('champ', source [, time_zone ])

source est une expression de type timestamp, timestamp with time zone ou interval. (Les valeurs de type date et time sont converties automatiquement en, respectivement, timestamp ou interval). champ indique la précision avec laquelle tronquer la valeur en entrée. La valeur de retour est logiquement de type timestamp, timestamp with time zone ou interval et il a tous les champs moins significatifs que celui sélectionné positionnés à zéro (ou un pour la date et le mois).

Les valeurs valides pour champ sont :

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

Quand la valeur en entrée est de type timestamp with time zone, le troncage est réalisé en respectant un fuseau horaire particulier ; par exemple, le troncage à la journée (day) produit une valeur qui est à minuit dans cette zone. Par défaut, le troncage est fait en respectant la configuration du 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é par différents moyens, comme précisé dans Section 8.5.3.

Un fuseau horaire ne peut pas être indiqué lors du traitement d'une entrée timestamp without time zone ou interval. Elles sont toujours prises à la valeur nominale.

Quelques exemples (en supposant que le fuseau horaire actuel 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');
Result: 2001-02-16 00:00:00-05

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05

SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00
     

9.9.3. AT TIME ZONE

La syntaxe AT TIME ZONE convertit la date/heure sans fuseau horaire en date/heure avec fuseau horaire, et les valeurs time en différents fuseaux horaires. Tableau 9.32 montre ses variantes.

Tableau 9.32. Variantes AT TIME ZONE

ExpressionType de retourDescription
timestamp without time zone AT TIME ZONE zone timestamp with time zoneTraite l'estampille donnée without time zone (sans fuseau), comme située dans le fuseau horaire indiqué.
timestamp with time zone AT TIME ZONE zone timestamp without time zoneConvertit l'estampille donnée with time zone (avec fuseau) dans le nouveau fuseau horaire, sans désignation du fuseau.
time with time zone AT TIME ZONE zone time with time zoneConvertit l'heure donnée with time zone (avec fuseau) dans le nouveau fuseau horaire.

Dans ces expressions, le fuseau horaire désiré zone peut être indiqué comme une chaîne texte (par exemple, 'America/Los_Angeles') ou comme un intervalle (c'est-à-dire INTERVAL '-08:00'). Dans le cas textuel, un nom de fuseau peut être indiqué de toute façon décrite dans Section 8.5.3.

Exemples (en supposant que le fuseau horaire local soit 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-05' 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ètre TimeZone à sa valeur actuelle. Le deuxième exemple décale la date/heure avec fuseau horaire vers le fuseau horaire indiqué, et renvoie la valeur sans fuseau horaire. Cela permet un stockage et un affichage de valeurs différentes de la valeur actuelle du paramètre TimeZone. Le troisième exemple convertit l'heure de Tokyo en heure de Chicago. Convertir des valeurs time vers d'autres fuseaux horaires utilise les règles du fuseau horaire actif, car aucune date n'est fournie.

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

9.9.4. Date/Heure courante

PostgreSQL fournit diverses fonctions qui renvoient des valeurs relatives aux date et heure courantes. Ces fonctions du standard SQL renvoient toutes des valeurs fondées sur l'heure de début de la transaction en cours :

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

CURRENT_TIME et CURRENT_TIMESTAMP délivrent les valeurs avec indication du fuseau horaire ; LOCALTIME et LOCALTIMESTAMP délivrent les valeurs sans indication du fuseau horaire.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, et LOCALTIMESTAMP acceptent un paramètre optionnel de précision. Celui-ci permet d'arrondir le résultat au nombre de chiffres indiqués pour la partie fractionnelle des secondes. Sans ce paramètre de précision, le résultat est donné avec toute la précision disponible.

Quelques exemples :

SELECT CURRENT_TIME;
Résultat :
14:39:53.662522-05

SELECT CURRENT_DATE;
Résultat :
2001-12-23

SELECT CURRENT_TIMESTAMP;
Résultat : 2001-12-23
14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Résultat : 2001-12-23
14:39:53.66-05

SELECT LOCALTIMESTAMP;
Résultat : 2001-12-23
14:39:53.662522

Comme ces fonctions renvoient l'heure du début de la transaction en cours, leurs valeurs ne changent pas au cours de la transaction. Il s'agit d'une fonctionnalité : le but est de permettre à une même transaction de disposer d'une notion cohérente de l'heure « courante ». Les multiples modifications au sein d'une même transaction portent ainsi toutes la même heure.

Note

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

PostgreSQL fournit aussi des fonctions qui renvoient l'heure de début de l'instruction en cours, voire l'heure de l'appel de la fonction. La liste complète des fonctions ne faisant pas partie du standard SQL est :

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

transaction_timestamp() est un peu l'équivalent de CURRENT_TIMESTAMP, mais est nommé ainsi pour expliciter l'information retournée. statement_timestamp() renvoie l'heure de début de l'instruction en cours (plus exactement, l'heure de réception du dernier message de la commande en provenance du client). statement_timestamp() et transaction_timestamp() renvoient la même valeur pendant la première commande d'une transaction, mais leurs résultats peuvent différer pour les commandes suivantes. clock_timestamp() renvoie l'heure courante, et, de ce fait, sa valeur change même à l'intérieur d'une commande SQL unique. timeofday() est une fonction historique de PostgreSQL. Comme clock_timestamp(), elle renvoie l'heure courante, mais celle-ci est alors formatée comme une chaîne text et non comme une valeur de type timestamp with time zone. now() est l'équivalent traditionnel PostgreSQL de CURRENT_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 courantes (interprétés comme l'heure de début de la transaction). De ce fait, les trois instructions suivantes renvoient le même résultat :

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- 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 suivantes permettent de retarder l'exécution du processus serveur :

pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)
     

pg_sleep endort le processus de la session courante pendant seconds secondes. seconds est une valeur de type double precision, ce qui autorise les délais en fraction de seconde. pg_sleep_for est une fonction d'aide permettant d'indiquer des durées plus longues d'endormissement, à spécifier sous la forme d'une donnée de type interval. pg_sleep_until est une fonction permettant de préciser une heure de réveil plutôt qu'un intervalle. 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 est spécifique à la plateforme ; 0,01 seconde est une valeur habituelle. Le délai dure au minimum celui précisé. Il peut toutefois être plus long du fait de certains facteurs tels que la charge serveur. En particulier, pg_sleep_until ne garantit pas un réveil à l'heure exacte spécifiée. Par contre, il ne se réveillera pas avant cette heure.

Avertissement

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



[7] 60 si les secondes d'ajustement (leap second) sont implantées par le système d'exploitation.