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

9.9. Fonctions et opérateurs pour date/heure

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

Toutes les fonctions et tous les descripteurs 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 + integerdate

Ajoute un nombre de jours à une date

date '2001-09-28' + 72001-10-05

date + intervaltimestamp

Ajouter un interval à une date

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

date + timetimestamp

Ajoute une heure du jour à une date

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

interval + intervalinterval

Ajoute des intervalles

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

timestamp + intervaltimestamp

Ajoute un interval à un timestamp

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

time + intervaltime

Ajoute un interval à une heure

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

- intervalinterval

Passe un interval en négatif

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

date - dateinteger

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

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

date - integerdate

Soustrait un nombre de jours d'une date

date '2001-10-01' - 72001-09-24

date - intervaltimestamp

Soustrait un interval d'une date

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

time - timeinterval

Soustrait des heures

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

time - intervaltime

Soustrait un interval d'une heure

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

timestamp - intervaltimestamp

Soustrait un interval d'un timestamp

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

interval - intervalinterval

Soustrait des intervalles

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

timestamp - timestampinterval

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 precisioninterval

Multiplie un interval à une valeur scalaire

interval '1 second' * 90000:15:00

interval '1 day' * 2121 days

interval '1 hour' * 3.503:30:00

interval / double precisioninterval

Divise un interval par une valeur scalaire

interval '1 hour' / 1.500:40:00


Tableau 9.32. Fonctions Date/Heure

Fonction

Description

Exemple(s)

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_datedate

Date actuelle ; voir Section 9.9.4

current_date2019-12-23

current_timetime with time zone

Heure actuelle du jour ; voir Section 9.9.4

current_time14: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_timestamptimestamp with time zone

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

current_timestamp2019-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 pour que les périodes de temps de 30 jours soient représentées comme des mois

justify_days(interval '35 days')1 mon 5 days

justify_hours ( interval ) → interval

Ajuste un interval pour que les périodes de temps de 24 heures soient représentées comme des jours

justify_hours(interval '27 hours')1 day 03:00: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

localtimetime

Heure actuelle du jour ; voir Section 9.9.4

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

localtimestamptimestamp

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

localtimestamp2019-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 ( [ year int [, month int [, week int [, day int [, hour int [, min int [, sec 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, time ou interval. (Les expressions de type date sont converties en timestamp et peuvent de ce fait être aussi utilisées.) field est un identifiant ou une chaîne qui sélectionne le champ à extraire à partir de la valeur source. La fonction extract renvoie des valeurs de type double precision. Voici les noms de champ valides :

century

The century

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 première siècle commence le 1er janvier après JC à minuit, bien qu'ils ne le savaient pas à ce moment-là. Cette définition s'applique à tous les pays utilisant le calendrier Grégorien. Il n'existe pas de siècle 0, nous passons du siècle -1 au siècle 1. Si vous n'êtes pas d'accord avec ça, merci de vous plaindre au pape, Cathédrale Saint Pierre de Rome, Vatican.

day

Pour les valeurs timestamp, le champ 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 (peut être négatif) ; pour les valeurs de type date et timestamp, le nombre de secondes depuis le 1er janvier 1970 à minuit, heure locale ; 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 INTERVAL '5 days 3 hours');
Résultat : 442800

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

SELECT to_timestamp(982384720.12);
Résultat : 2001-02-17 04:38:40.12+00
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 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 (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 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.

Ce champ n'est pas disponible dans les versions de PostgreSQL antérieures à la 8.3.

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

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

Pour les valeurs timestamp, 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 semaie 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

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 tronquage 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 zonetimestamp 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 zonetimestamp 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 zonetime 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 commande d'une transaction, mais pourrait différer lors des commandes suivantes. clock_timestamp() renvoie l'heure actuelle, et de ce fait, sa valeur changera y compris au sein d'une même commande 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';  -- incorrect pour une utilisation avec DEFAULT

Astuce

Vous ne devez pas utiliser la troisième forme pour indiquer une clause DEFAULT lors de la création d'une table. Le système convertirait now en une valeur timestamp dès que la constante est analysée, de telle façon que, dès que la valeur par défaut est requise, l'heure de la création de la table serait utilisée ! Les deux premières formes ne seront évaluées que lorsque la valeur par défaut est utilisée, parce que ce sont des appels de fonction. De ce fait, ils donneront le comportement désiré correspondant à l'heure d'insertion de la ligne.

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.