8.5. Types date/heure

PostgreSQL supporte l'ensemble des types date et heure de SQL, montrés dans Tableau 8-9.

Tableau 8-9. Types date et heure

NomTaille de stockageDescriptionValeur minimaleValeur maximaleRésolution
timestamp [ (p) ] [ without time zone ]8 octetsdate et heure4713 avant JC5874897 après JC1 microseconde / 14 chiffres
timestamp [ (p) ] with time zone8 octetsdate et heure, avec fuseau horaire4713 avant JC5874897 après JC1 microseconde / 14 chiffres
interval [ (p) ]12 octetsintervals de temps-178000000 années178000000 années1 microseconde
date4 octetsdates seulement4713 avant JC32767 après JC1 jour
time [ (p) ] [ without time zone ]8 octetsheures seulement00:00:00.0023:59:59.991 microseconde
time [ (p) ] with time zone12 octetsheures seulement, avec fuseau horaire00:00:00.00+1223:59:59.99-121 microseconde

Note : Avant PostgreSQL 7.3, écrire seulement timestamp était équivalent à timestamp with time zone. Ceci a été changé pour une meilleure compatibilité avec le standard SQL.

time, timestamp, et interval acceptent une précision optionnelle p, qui précise le nombre de chiffres après la virgule pour les secondes. Par défaut, il n'y a pas de limite explicite à la précision. Les valeurs acceptées pour p vont de 0 à 6 pour les types timestamp et interval.

Note : When timestamp values are stored as double precision floating-point numbers (currently the default), the effective limit of precision may be less than 6. timestamp values are stored as seconds before or after midnight 2000-01-01. Microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. When timestamp values are stored as eight-byte integers (a compile-time option), microsecond precision is available over the full range of values. However eight-byte integer timestamps have a more limited range of dates than shown above: from 4713 BC up to 294276 AD.

Note : Lorsque les valeurs de type timestamp sont stockées comme des nombres à virgule flottante de précision double (ce qui est le cas par défaut), la limite de précision effective peut être inférieure à 6. Les valeurs de timestamp sont stockées comme des secondes depuis le avant ou après minuit le 1er janvier 2001. La précision de la milliseconde est atteinte pour quelques années autour de cette date mais la précision se dégrade si on s'en éloigne. Lorsque les valeurs de timestamp sont stockés comme des entiers sur 8 octets, ce qui est une option à la compilation, la précision de la microseconde est disponible sur tout l'intervalle des valeurs. Néanmoins, cet intervalle est encore plus limité :: de 4713 avant Jésus Christ à 294276 après Jésus Christ.

Note : Avant PostgreSQL 7.3, indiquer juste timestamp était équivalent à timestamp with time zone. Ce comportement a été changé en respect du standard SQL.

Pour les types time, l'intervalle accepté pour p est de 0 à 6 lorsque les entiers sur 8 octets sont utilisés, ou de 0 à 10 lorsque le stockage se fait sous forme de nombre à virgule flottante.

Le type time with time zone est défini dans le standard SQL, mais sa définition lui prête des propriétés qui font douter de son utilité. Dans la plupart des cas, une combinaison de date, time, timestamp without time zone, et timestamp with time zone devrait permettre de résoudre toutes les fonctionnalités de date et heure nécessaires à une application.

Les types abstime et reltime sont des types de précision moindre, utilisés en interne. Il n'est pas recommandé de les utiliser dans de nouvelles applications. Au contraire, il est souhaitable de migrer l'existant vers un autre type approprié. Ces types internes pourraient disparaître dans une future version.

8.5.1. Entrée des dates et heures

La saisie de dates et heures et possible dans la plupart des formats raisonnables, dont ISO8601, compatible SQL, traditionnel POSTGRES, et d'autres. Pour certains formats, l'ordre des jours, mois et années en entrée est ambigu. Il est alors possible de préciser l'ordre attendu pour ces champs. Réglez le paramètre datestyle à MDY pour choisir une interprétation mois-jour-année, à DMY pour jour-mois-année, à YMD pour année-mois-jour.

PostgreSQL est plus flexible que la norme SQL ne l'exige pour la manipulation des dates et des heures. Voir Annexe B pour connaître les règles exactes de reconnaissance des dates et heures, ainsi que les formats de champs texte comme les mois, les jours de la semaine et les fuseaux horaires.

Rappelez vous que chaque littéral date ou heure à entrer doit être mis entre apostrophes, comme les chaînes de caractères. Référez vous à Section 4.1.2.4 pour plus d'information. SQL requière la syntaxe suivante:

type [ (p) ] 'value'

p dans la spécification optionnelle de précision est un entier correspondant au nombre de chiffres après la virgule dans le champ secondes. La précision peut être précisée pour les types time, timestamp, et interval. Les valeurs admissibles sont mentionnées plus haut. Si aucune précision n'est indiquée dans une spécification de constante, elle prend la précision de la valeur littérale.

8.5.1.1. Dates

Tableau 8-10 montre les formats de date possibles pour les entrées de type date.

Tableau 8-10. Saisie de date

ExempleDescription
January 8, 1999sans ambiguïté quel que soit le style de date (datestyle)
1999-01-08ISO-8601; 8 janvier, quel que soit le mode (format recommandé)
1/8/19998 janvier en mode MDYMDY; 1er Août en mode DMY
1/18/199918 janvier en mode MDY; rejeté dans les autres modes
01/02/032 janvier 2003 en mode MDY; 1er février 2003 en mode DMY; 3 février 2003 en mode YMD
1999-Jan-088 janvier dans tous les modes
Jan-08-19998 janvier dans tous les modes
08-Jan-19998 janvier dans tous les modes
99-Jan-088 janvier en mode YMD, erreur sinon
08-Jan-998 janvier, sauf en mode YMD: erreur
Jan-08-998 janvier, sauf en mode YMD: erreur
19990108ISO-8601; 8 janvier 1999 dans tous les modes
990108ISO-8601; 8 janvier 1999 dans tous les modes
1999.008Année et jour dans l'année
J2451187Jour du calendrier Julien
January 8, 99 BCannée 99 avant Jésus Christ

8.5.1.2. Heures

Les types heure-du-jour sont time [ (p) ] without time zone et time [ (p) ] with time zone. Écrire juste time est équivalent à time without time zone

Les valeurs d'entrée valides pour ces types sont constituées d'une heure du jour suivi d'un fuseau horaire optionnel. (voir Tableau 8-11 et Tableau 8-12.) Si un fuseau est précisé pour le type time without time zone, il est ignoré sans message d'erreur.

Tableau 8-11. Saisie d'heure

ExempleDescription
04:05:06.789ISO 8601
04:05:06ISO 8601
04:05ISO 8601
040506ISO 8601
04:05 AMIdentique à 04:05; AM n'affecte pas la valeur
04:05 PMIdentique à 16:05; l'heure doit être <= 12
04:05:06.789-8ISO 8601
04:05:06-08:00ISO 8601
04:05-08:00ISO 8601
040506-08ISO 8601
04:05:06 PSTfuseau horaire précisé en lettres

Tableau 8-12. Saisie des zones de date

ExempleDescription
PSTHeure standard du Pacifique (Pacific Standard Time)
-8:00Décalage ISO-8601 pour la zone PST
-800Décalage ISO-8601 pour la zone PST
-8Décalage ISO-8601 pour la zone PST
zuluAbréviation des militaires pour GMT
zVersion courte de zulu

8.5.1.3. Horodatages (time stamps)

Les valeurs d'entrée valides sont constituées par la concaténation d'une date, d'une heure, d'un qualificatif optionnel AD (avant Jésus Christ) ou BC (après Jésus Christ), et d'un fuseau horaire optionnel. Ainsi:

1999-01-08 04:05:06

et

1999-01-08 04:05:06 -8:00

sont des valeurs valides, qui suivent le standard ISO 8601. De plus, le format

January 8 04:05:06 1999 PST

très courant, est supporté.

Pour timestamp [without time zone], un fuseau horaire explicite est ignoré sans message. C'est à dire que la date/heure résultante n'est pas ajustée pour le fuseau horaire.

Pour timestamp with time zone, la valeur stockée en interne est toujours en UTC (Temps Universel Coordonné), aussi connu sous le nom de GMT. Les valeurs d'entrée qui ont un fuseau horaire explicite sont converties en UTC en utilisant le décalage approprié. Si aucun fuseau horaire n'est précisé, alors le système considère que la date est dans le fuseau horaire indiqué par le paramètre système timezone, et la convertit en UTC en utilisant le décalage de la zone timezone.

Quand une valeur timestamp with time zone est affichée, elle est toujours convertie de UTC vers le fuseau horaire courant (variable timezone), et affichée comme une heure locale de cette zone. Pour voir l'heure dans un autre fuseau horaire, il faut soit changer la valeur de timezone ou utiliser la construction AT TIME ZONE (voir Section 9.8.3).

Les conversions entre timestamp without time zone et timestamp with time zone considèrent normalement que la valeur timestamp without time zone utilise le fuseau horaire timezone. Une zone différente peut être choisie en utilisant AT TIME ZONE.

8.5.1.4. Intervalles

Les valeurs de type interval utilisent la syntaxe suivante:

[@] quantité unité [quantité unité...] [direction]

Où: quantité est un nombre (éventuellement signé); unité est second, minute, hour, day, week, month, year, decade, century, millennium, ou des abréviations ou des pluriels de ces unités; direction peut être ago ou vide. L'arobase (@) est du bruit optionnel. Les valeurs des différentes unités sont implicitement ajoutées en utilisant le signe approprié.

Les quantités de jours, heures, minutes et secondes peuvent être précisées sans unité explicite. Par exemple '1 12:59:10' est lu comme '1 day 12 hours 59 min 10 sec' (1 jour, 12 heures, 59 minutes, 10 secondes).

La précision optionnelle doit être entre 0 et 6, et prend la précision du littéral comme valeur par défaut.

8.5.1.5. Valeurs spéciales

Les fonctions suivantes, compatibles SQL, sont utilisables comme des valeurs de date ou d'heure: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. Les 4 dernières acceptent une spécification de précision optionnelle. (voir aussi Section 9.8.4.)

PostgreSQL supporte aussi plusieurs valeurs de dates spéciales, par simplicité, comme montré dans Tableau 8-13. Les valeurs infinity et -infinity ont une représentation spéciale dans le système et seront affichées de la même façon. Les autres sont simplement des facilités de notation qui seront converties en dates/heures ordinaires lorsqu'elles seront lues. Toutes ces valeurs sont traitées comme des constantes normales, et doivent être écrites entre apostrophes.

Tableau 8-13. Saisie de dates/heures spéciales

Chaînes entréesTypes validesDescription
epochdate, timestamp1970-01-01 00:00:00+00 (date système zéro d'Unix)
infinitytimestampplus tard que toutes les autres dates
-infinitytimestampplus tôt que toutes les autres dates
nowdate, time, timestampheure de début de la transaction courante
todaydate, timestampminuit aujourd'hui
tomorrowdate, timestampminuit demain
yesterdaydate, timestampminuit hier
allballs 
allballstime00:00:00.00 UTC

8.5.2. Affichage des Date/Heure

Le format de sortie des types date/heure peut être choisi parmi un des quatre formats de date suivants: ISO 8601, SQL (Ingres), Traditionnel POSTGRES, et Allemand, en utilisant la commande SET datestyle. Le format par défaut est le format ISO, comme demandé par le standard SQL. Le nom du format d'affichage << SQL >> est un accident historique. Tableau 8-14 montre des exemples de chaque format d'affichage. Le format d'un type date ou time est bien sur celui de la partie date ou heure, comme montré dans les exemples.

Tableau 8-14. Styles d'affichage de date/heure

Spécification de styleDescriptionExemple
ISOstandard ISO 8601/SQL1997-12-17 07:37:16-08
SQLstyle traditionnel12/17/1997 07:37:16.00 PST
POSTGRESstyle originalWed Dec 17 07:37:16 1997 PST
Germanstyle régional17.12.1997 07:37:16.00 PST

Dans les styles SQL et POSTGRES, les jours apparaissent avant le mois si l'ordre des champs DMY a été précisé, sinon les mois apparaissent avant les jours. (Voir Section 8.5.1 pour savoir comment ce paramètre affecte l'interprétation des valeurs entrées.) Tableau 8-15 montre un exemple.

Tableau 8-15. Convention d'ordre des dates

Réglage de >datestyle (style de date)Ordre d'entréeExemple d'affichage
SQL, DMYjour/mois/année17/12/1997 15:37:16.00 CET
SQL, MDYmois/jour/année12/17/1997 07:37:16.00 PST
Postgres, DMYjour/mois/annéeWed 17 Dec 07:37:16 1997 PST

L'affichage du type interval ressemble au format de saisie, sauf que les unités comme century ou week sont converties en années et jours, et que ago est converti en un signe approprié. En mode ISO, l'affichage ressemble à:

[ quantité unité [ ... ] ] [ jours ] [ heures:minutes:secondes ]

Les styles de date/heure peuvent être sélectionnés soit en utilisant la commande SET datestyle, soit en utilisant le paramètre datestyle du fichier de configuration postgresql.conf, soit avec la variable d'environnement PGDATESTYLE sur le serveur ou le client. La fonction de formatage to_char (voir Section 9.7) permet aussi, de manière plus flexible, pour formater les affichages de date/heure.

8.5.3. Fuseaux horaires

Les fuseaux horaires et les conventions d'heures sont influencées par des décisions politiques, pas seulement par la géométrie de la terre. Les fuseaux horaires se sont un peu standardisés au cours du vingtième siècle, mais continuent d'être soumis à des changements arbitraires. PostgreSQL utilise les fonctionnalités de votre système d'exploitation pour proposer le support des fuseaux horaires. Souvent, ces systèmes ne contiennent les informations que pour la période qui va de 1902 à 2038 (ce qui correspond à l'étendue complète des dates système conventionnelles d'Unix). timestamp with time zone et time with time zone n'utilisent les informations de fuseaux horaires que dans cette période, et utilisent l'heure UTC pour les autres dates. Mais comme le support des fuseaux horaires est issu de celui du système d'exploitation, il peut supporter les heures d'été et autres comportements particuliers.

PostgreSQL se veut compatible avec les définitions standard SQL pour un usage typique. Néanmoins, le standard SQL possède un mélange bizarre de types de date/heure et de possibilités. Deux problèmes sont évidents:

Pour ne pas avoir ces difficultés, nous recommandons d'utiliser des types de date/heure qui contiennent à la fois une date et une heure lorsque vous utilisez les fuseaux horaires. Nous recommandons de ne pas utiliser le type time with time zone. Ce type est néanmoins proposé par PostgreSQL pour les applications existantes et pour assurer la compatibilité avec les autres bases de données compatibles SQL. PostgreSQL utilise votre fuseau horaire pour tous les types qui ne contiennent qu'une date ou une heure.

Toutes les dates et heures sont stockées en interne en UTC. Les heures sont converties en heure locale sur le serveur de bases de données avant d'être envoyées au client, et sont donc par défaut dans le fuseau horaire du serveur.

Il y a plusieurs façons de choisir le fuseau horaire utilisé par le serveur:

Note : Si un fuseau horaire invalide est indiqué, le fuseau horaire devient UTC (sur la plupart des systèmes).

Voir Annexe B pour avoir une liste des fuseaux horaires disponibles.

8.5.4. Types internes

PostgreSQL utilise les dates Juliennes pour tous les calculs de date/heure. Elles ont la propriété intéressante de permettre le calcul de toute date entre 4713 avant Jésus Christ et loin dans le futur, si on utilise le fait que l'année dure 365,2425 jours.

Les conventions de date antérieures au 19ème siècle offrent une lecture intéressante, mais ne sont pas assez consistantes pour être codées dans un gestionnaire de dates.