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

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

Tableau 9.33 affiche les fonctions disponibles pour le traitement de valeurs date/heure, avec des détails dans les sections suivantes. Tableau 9.32 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 pour 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.32. 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.33. 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.5

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

current_datedate

Date actuelle ; voir Section 9.9.5

current_date2019-12-23

current_timetime with time zone

Heure actuelle du jour ; voir Section 9.9.5

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

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

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

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

date_add ( timestamp with time zone, interval [, text ] ) → timestamp with time zone

Ajoute un interval à un timestamp with time zone, en calculant l'heure du jour et avec ajustement pour les changements d'heure suivant le fuseau horaire nommé dans le troisième argument ou avec la configuration de TimeZone si cet argument est omis. La syntaxe avec deux arguments est équivalent à l'opérateur timestamp with time zone + interval.

date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')2021-10-31 23:00:00+00

date_bin ( interval, timestamp, timestamp ) → timestamp

Place une date dans l'intervalle spécifié, alignée avec une origine donnée ; voir Section 9.9.3

date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')2001-02-16 20:35:00

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_subtract ( timestamp with time zone, interval [, text ] ) → timestamp with time zone

Soustrait un interval à un timestamp with time zone, en calculant l'heure du jour et avec ajustement pour les changements d'heure suivant le fuseau horaire nommé dans le troisième argument ou avec la configuration de TimeZone si cet argument est omis. La syntaxe avec deux arguments est équivalent à l'opérateur timestamp with time zone + interval.

date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')2021-10-30 22:00:00+00

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 ) → numeric

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 ) → numeric

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

localtime14:39:53.662522

localtime ( integer ) → time

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

localtime(0)14:39:53

localtimestamptimestamp

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

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

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 (negative years signify BC)

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 (negative years signify BC)

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 (negative years signify BC). Si timezone n'est pas spécifié, le fuseau horaire actuel est utilisé; les exemples supposent que le fuseau horaire de la session est Europe/London

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

make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')2013-07-15 13:15:23.5+01

now ( ) → timestamp with time zone

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

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

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

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

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 ou timestamp with time zone, les champs mois, jour et microseconde de la valeur interval sont gérés chacun leur tour. Tout d'abord, un champ mois différent de zéro avance ou recule la date par le nombre indiqué de mois, conservant identique le jour du mois sauf s'il dépasse la fin du nouveau mois, auquel cas le dernier jour de ce mois est utilisé. (Par exemple, le 31 mars plus 1 mois devient le 30 avril, mais le 31 mars plus deux mois devient le 31 mai.) Puis le champ jour avance ou recule du nombre de jours indiqués. Dans les deux cas, l'heure locale du jour reste la même. Enfin, si le champ microsecondes est différent de zéro, il est simplement ajouté ou soustrait. Lors d'un calcul sur une valeur timestamp with time zone dans un fuseau horaire comportant des changements d'heure, cela signifie qu'ajouter ou supprimer, par exemple, interval '1 day' ne donnera pas forcément le même résultat qu'ajouter ou supprimer 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.000000
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.9583333333333333
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 numeric. 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. 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.120000

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
Résultat : 982355920.120000

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Résultat : 4428000000
      

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)

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.

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.50000000000000000000
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.000
      
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.000000

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Résultat : 28.500000
      
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
      

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. Pour des raisons historiques, la fonction date_part retourne des valeurs de type double precision. Cela peut entraîner une perte de précision dans certains cas. Il est recommandé d'utiliser extract à la place.

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

La fonction date_bin place l'instant en entrée au sein de l'intervalle spécifié (le pas), aligné avec une origine donnée.

date_bin(pas, source, origine)

source est une expression de type timestamp ou timestamp with time zone. (Les valeurs de type date sont automatiquement converties en timestamp.) pas est une expression de type interval. La valeur retournée est aussi de type timestamp ou timestamp with time zone, et marque le début de l'intervalle dans lequel source est placée.

Exemples :

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30

Dans le cas d'unités rondes (1 minute, 1 heure, etc.), la fonction retourne le même résultat que l'analogue date_trunc, la différence étant que date_bin est capable de tronquer selon un intervalle arbitraire.

L'intervalle pas doit être plus grand que zéro et ne peut contenir ni des mois, ni des unités plus grandes.

9.9.4. 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.34 montre les variantes.

Tableau 9.34. 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.5. 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'; -- 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.6. 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.