PostgreSQL™ supporte l'ensemble des types date et heure du SQL. Ces types sont présentés dans le Tableau 8.9, « Types date et heure ». Les opérations disponibles sur ces types de données sont décrites dans la Section 9.9, « Fonctions et opérateurs sur date/heure ».
Tableau 8.9. Types date et heure
| Nom | Taille de stockage | Description | Valeur minimale | Valeur maximale | Résolution |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 octets | date et heure | 4713 avant JC | 5874897 après JC | 1 microseconde / 14 chiffres |
| timestamp [ (p) ] with time zone | 8 octets | date et heure, avec fuseau horaire | 4713 avant JC | 5874897 après JC | 1 microseconde / 14 chiffres |
| interval [ (p) ] | 12 octets | intervalle de temps | -178000000 années | 178000000 années | 1 microseconde / 14 chiffres |
| date | 4 octets | date seule | 4713 avant JC | 5874897 après JC | 1 jour |
| time [ (p) ] [ without time zone ] | 8 octets | heure seule | 00:00:00.00 | 24:00:00 | 1 microseconde / 14 chiffres |
| time [ (p) ] with time zone | 12 octets | heure seule, avec fuseau horaire | 00:00:00+1459 | 24:00:00-1459 | 1 microseconde / 14 chiffres |
![[Note]](images/note.png)
Précédemment à PostgreSQL™ 7.3, n'écrire que timestamp équivalait à timestamp with time zone. Cela a été modifié pour des raisons de compatibilité avec le standard SQL.
time, timestamp, et interval acceptent une précision optionnelle p, qui indique le nombre de décimales pour les secondes. Il n'y a pas, par défaut, de limite explicite à cette précision. Les valeurs acceptées pour p s'étendent de 0 à 6 pour les types timestamp et interval.
Quand les valeurs timestamp sont stockées en tant que nombre à virgule flottante de précision double (par défaut à l'heure actuelle), la limite réelle de la précision peut être inférieure à 6. Les valeurs timestamp sont stockées comme un nombre de secondes avant ou après le 1er janvier 2000 à minuit. La précision à la microseconde est obtenue pour les dates proches du 1er janvier 2000, mais elle se dégrade pour les dates plus éloignées. Quand les valeurs timestamp sont stockées sur des entiers de huit octets (une option de compilation), la précision en microseconde est disponible sur toute l'étendue de valeurs. Néanmoins, les valeurs de type timestamp codées sur des entiers de huit octets ont une échelle de date plus restreinte que celle indiquée ci-dessus : de 4713 avant Jésus-Christ à 294276 après Jésus-Christ. La même option de compilation détermine si les valeurs time et interval sont stockées comme nombres à virgule flottante ou comme entiers sur huit octets. Dans le cas de la virgule flottante, la précision des grandes valeurs interval se dégradent à mesure que l'intervalle croît.
Pour les types time, l'intervalle accepté pour p s'étend de 0 à 6 pour les entiers sur 8 octets et de 0 à 10 pour les nombres à 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é. Il est probable que l'un ou l'autre (voire tous) de ces types internes disparaisse(nt) dans une future version.
La saisie de dates et heures peut se faire dans la plupart des formats raisonnables, dont ISO8601, tout format compatible avec SQL, le format POSTGRES™ traditionnel ou 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. Le paramètre datestyle peut être positionné à MDY pour choisir une interprétation mois-jour-année, à DMY pour jour-mois-année ou à 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 l'Annexe B, Support de date/heure pour connaître les règles exactes de reconnaissance des dates et heures et les formats reconnus pour les champs texte comme les mois, les jours de la semaine et les fuseaux horaires.
Tout libellé de date ou heure saisi doit être placé entre apostrophes, comme les chaînes de caractères. La Section 4.1.2.5, « Constantes d'autres types » peut être consultée pour plus d'information. SQL requiert la syntaxe suivante :
type [ (p) ] 'valeur'
où p, précision optionnelle, est un entier correspondant au nombre de décimales du 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 déclaration de constante, celle de la valeur littérale est utilisée.
Le Tableau 8.10, « Saisie de date » regroupe les formats de date possibles pour la saisie de valeurs de type date.
Tableau 8.10. Saisie de date
| Exemple | Description |
|---|---|
| January 8, 1999 | sans ambiguïté quel que soit le style de date (datestyle) |
| 1999-01-08 | ISO-8601 ; 8 janvier, quel que soit le mode (format recommandé) |
| 1/8/1999 | 8 janvier en mode MDY ; 1er août en mode DMY |
| 1/18/1999 | 18 janvier en mode MDY ; rejeté dans les autres modes |
| 01/02/03 | 2 janvier 2003 en mode MDY ; 1er février 2003 en mode DMY ; 3 février 2001 en mode YMD |
| 1999-Jan-08 | 8 janvier dans tous les modes |
| Jan-08-1999 | 8 janvier dans tous les modes |
| 08-Jan-1999 | 8 janvier dans tous les modes |
| 99-Jan-08 | 8 janvier en mode YMD, erreur sinon |
| 08-Jan-99 | 8 janvier, sauf en mode YMD : erreur |
| Jan-08-99 | 8 janvier, sauf en mode YMD : erreur |
| 19990108 | ISO-8601 ; 8 janvier 1999 dans tous les modes |
| 990108 | ISO-8601 ; 8 janvier 1999 dans tous les modes |
| 1999.008 | Année et jour de l'année |
| J2451187 | Jour du calendrier Julien |
| January 8, 99 BC | année 99 avant Jésus Christ |
Les types heure-du-jour sont time [ (p) ] without time zone et time [ (p) ] with time zone. time est équivalent à time without time zone.
Les saisies valides pour ces types sont constituées d'une heure suivie éventuellement d'un fuseau horaire (voir le Tableau 8.11, « Saisie d'heure » et le Tableau 8.12, « Saisie des fuseaux horaires »). Si un fuseau est précisé pour le type time without time zone, il est ignoré sans message d'erreur. Si une date est indiquée, elle est ignorée sauf si un fuseau horaire impliquant une règle de changement d'heure (heure d'été/heure d'hiver) est précisé, America/New_York par exemple. Dans ce cas, la date est nécessaire pour pouvoir déterminer la règle de calcul de l'heure qui s'applique. Le décalage approprié du fuseau horaire est enregistré dans la valeur de time with time zone.
Tableau 8.11. Saisie d'heure
| Exemple | Description |
|---|---|
| 04:05:06.789 | ISO 8601 |
| 04:05:06 | ISO 8601 |
| 04:05 | ISO 8601 |
| 040506 | ISO 8601 |
| 04:05 AM | Identique à 04:05 ; AM n'affecte pas la valeur |
| 04:05 PM | Identique à 16:05 ; l'heure doit être <= 12 |
| 04:05:06.789-8 | ISO 8601 |
| 04:05:06-08:00 | ISO 8601 |
| 04:05-08:00 | ISO 8601 |
| 040506-08 | ISO 8601 |
| 04:05:06 PST | fuseau horaire abrégé |
| 2003-04-12 04:05:06 America/New_York | fuseau horaire en nom complet |
Tableau 8.12. Saisie des fuseaux horaires
| Exemple | Description |
|---|---|
| PST | Abréviation pour l'heure standard du Pacifique (Pacific Standard Time) |
| America/New_York | Nom complet du fuseau horaire |
| PST8PDT | Nommage POSIX du fuseau horaire |
| -8:00 | Décalage ISO-8601 pour la zone PST |
| -800 | Décalage ISO-8601 pour la zone PST |
| -8 | Décalage ISO-8601 pour la zone PST |
| zulu | Abréviation militaire de GMT |
| z | Version courte de zulu |
L'Section 8.5.3, « Fuseaux horaires » apporte des précisions quant à la façon d'indiquer les fuseaux horaires.
Les saisies valides sont constituées de la concaténation d'une date et d'une heure, éventuellement suivie d'un fuseau horaire et d'un qualificatif AD (après Jésus Christ) ou BC (avant Jésus Christ). (AD/BC peut aussi apparaître avant le fuseau horaire mais ce n'est pas l'ordre préféré.) 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. Le format très courant
January 8 04:05:06 1999 PST
est également supporté.
Le standard SQL différencie les libéllés timestamp without time zone et timestamp with time zone par la présence d'un « + » ou d'un « - ». De ce fait, d'après le standard,
TIMESTAMP '2004-10-19 10:23:54'
est du type timestamp without time zone alors que
TIMESTAMP '2004-10-19 10:23:54+02'
est du type timestamp with time zone. PostgreSQL™ n'examine jamais le contenu d'un libellé avant de déterminer son type. Du coup, il traite les deux ci-dessus comme des valeurs de type timestamp without time zone. Pour s'assurer qu'un littéral est traité comme une valeur de type timestamp with time zone, il faut préciser explicitement le bon type :
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
Dans un libellé de type timestamp without time zone, PostgreSQL™ ignore silencieusement toute indication de fuseau horaire. C'est-à-dire que la valeur résultante est dérivée des champs date/heure de la valeur saisie et n'est pas corrigée par le fuseau horaire.
Pour timestamp with time zone, la valeur stockée en interne est toujours en UTC (Universal Coordinated Time ou Temps Universel Coordonné), aussi connu sous le nom de GMT (Greenwich Mean Time). Les valeurs saisies avec un fuseau horaire explicite sont converties en UTC à l'aide du 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 l'UTC vers le fuseau horaire courant (variable timezone), et affichée comme une heure locale. Pour voir l'heure dans un autre fuseau horaire, il faut, soit changer la valeur de timezone, soit utiliser la construction AT TIME ZONE (voir la Section 9.9.3, « AT TIME ZONE »).
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. Un fuseau différent peut être choisi en utilisant AT TIME ZONE.
Les valeurs de type interval utilisent la syntaxe suivante :
[@] quantité unité [quantité unité...] [direction]
où : quantité est un nombre (éventuellement signé) ; unité est microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, une abbréviation ou un pluriel de ces unités ; direction peut être ago ou vide. L'arobase (@) est optionnel. Les valeurs des différentes unités sont implicitement ajoutées en utilisant le signe approprié.
Les nombres de jours, heures, minutes et secondes peuvent être précisées sans unité explicite. Par exemple '1 12:59:10' est compris de la même façon que '1 day 12 hours 59 min 10 sec' (1 jour, 12 heures, 59 minutes, 10 secondes).
La précision optionnelle, en dessous de la seconde, doit être comprise entre 0 et 6, et prend la précision du libellé saisi comme valeur par défaut.
En interne, les valeurs interval sont enregistrées comme des mois, jours et secondes. Ceci parce que le nombre de jours d'un mois varie et qu'un jour peut avoir 23 ou 25 heures si un changement d'heure s'applique. Comme les intervalles sont habituellement créés à partir de chaînes de caractères constantes ou de soustraction de timestamp, cette méthode de stockage fonctionne bien dans la majorité des cas. Les fonctions justify_days et justify_hours sont disponibles pour ajuster les jours et les heures qui dépassent leurs périodes habituelles.
PostgreSQL™ supporte plusieurs valeurs de dates spéciales, dans un souci de simplification. Ces valeurs sont présentées dans le Tableau 8.13, « Saisie de dates/heures spéciales ». Les valeurs infinity et -infinity ont une représentation spéciale dans le système et sont affichées ainsi ; les autres ne sont que des raccourcies de notation convertis en dates/heures ordinaires lorsqu'ils sont lus. (En particulier, now et les chaînes relatives sont converties en une valeur de temps spécifique à leur lecture). Toutes ces valeurs doivent être écrites entre simples quotes lorsqu'elles sont utilisées comme des constantes dans les commandes SQL.
Tableau 8.13. Saisie de dates/heures spéciales
| Saisie | Types valides | Description |
|---|---|---|
| epoch | date, timestamp | 1970-01-01 00:00:00+00 (date système zéro d'Unix) |
| infinity | timestamp | plus tard que toutes les autres dates |
| -infinity | timestamp | plus tôt que toutes les autres dates |
| now | date, time, timestamp | heure de démarrage de la transaction courante |
| today | date, timestamp | aujourd'hui minuit |
| tomorrow | date, timestamp | demain minuit |
| yesterday | date, timestamp | hier minuit |
| allballs | time | 00:00:00.00 UTC |
Les fonctions suivantes, compatibles avec le standard SQL, peuvent aussi être utilisées pour obtenir l'heure courante pour le type de données correspondant : CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. Les quatre derniers acceptent une indication optionnelle de précision en dessous de la seconde (voir la Section 9.9.4, « Date/Heure courante »). Ce sont là des fonctions SQL qui ne sont pas reconnues comme chaînes de saisie de données.
Le format de sortie des types date/heure peut être positionné à l'un des quatre formats de date suivants : ISO 8601, SQL (Ingres), traditionnel POSTGRES et German (germanique). Cela se fait à l'aide de la commande SET datestyle. Le format par défaut est le format ISO. (Le standard SQL impose l'utilisation du format ISO 8601. Le nom du format d'affichage « SQL » est un accident historique.) Le Tableau 8.14, « Styles d'affichage de date/heure » présente des exemples de chaque format d'affichage. La sortie d'un type date ou time n'est évidemment composée que de la partie date ou heure, comme montré dans les exemples.
Tableau 8.14. Styles d'affichage de date/heure
| Spécification de style | Description | Exemple |
|---|---|---|
| ISO | standard ISO 8601/SQL | 1997-12-17 07:37:16-08 |
| SQL | style traditionnel | 12/17/1997 07:37:16.00 PST |
| POSTGRES | style original | Wed Dec 17 07:37:16 1997 PST |
| German | style régional | 17.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 la Section 8.5.1, « Saisie des dates et heures » pour savoir comment ce paramètre affecte l'interprétation des valeurs en entrée). Le Tableau 8.15, « Convention de présentation des dates » présente un exemple.
Tableau 8.15. Convention de présentation des dates
| Valeur de datestyle (style de date) | Ordre de saisie | Exemple d'affichage |
|---|---|---|
| SQL, DMY | jour/mois/année | 17/12/1997 15:37:16.00 CET |
| SQL, MDY | mois/jour/année | 12/17/1997 07:37:16.00 PST |
| Postgres, DMY | jour/mois/année | Wed 17 Dec 07:37:16 1997 PST |
L'affichage du type interval ressemble au format de saisie, à ceci près 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 à l'aide de la commande SET datestyle, du paramètre datestyle du fichier de configuration postgresql.conf ou par la variable d'environnement PGDATESTYLE sur le serveur ou le client. La fonction de formatage to_char (voir Section 9.8, « Fonctions de formatage des types de données ») permet de formater les affichages de date/heure de manière plus flexible.
Les fuseaux horaires et les conventions liées sont influencées par des décisions politiques, pas uniquement par la géométrie de la terre. Les fuseaux horaires se sont quelque peu standardisés au cours du vingtième siècle mais continuent à être soumis à des changements arbitraires, particulièrement en respect des règles de changement d'heure (heure d'été/heure d'hiver). PostgreSQL™ gère actuellement les règles de changement d'heure pour la période de 1902 à 2038 (qui correspond à l'intégralité de l'échelle du temps système Unix). Les heures en dehors de cette échelle sont prises dans le « temps standard » du fuseau horaire sélectionné, quelque soit la partie de l'année où elles tombent.
PostgreSQL™ se veut compatible avec les définitions standard SQL pour un usage typique. Néanmoins, le standard SQL possède un mélange étrange de types de date/heure et de possibilités. Deux problèmes évidents sont :
bien que le type date n'ait pas de fuseau horaire associé, le type heure peut en avoir un. Les fuseaux horaires, dans le monde réel, ne peuvent avoir de sens qu'associés à une date et à une heure, vu que l'écart peut varier avec l'heure d'été ;
le fuseau horaire par défaut est précisé comme un écart numérique constant avec l'UTC. Il n'est, de ce fait, pas possible de s'adapter à l'heure d'été ou d'hiver lorsque l'on fait des calculs arithmétiques qui passent les limites de l'heure d'été et de l'heure d'hiver.
Pour éviter ces difficultés, il est recommandé d'utiliser des types date/heure qui contiennent à la fois une date et une heure lorsque les fuseaux horaires sont utilisés. Il est également préférable 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 le standard SQL.) PostgreSQL™ utilise le fuseau horaire local pour tous les types qui ne contiennent qu'une date ou une heure.
Toutes les dates et heures liées à un fuseau horaire sont stockées en interne en UTC. Elles sont converties en heure locale dans le fuseau indiqué par le paramètre de configuration timezone avant d'être affiché sur le client.
PostgreSQL™ permet d'indiquer les fuseaux horaires de trois façons différentes :
un nom complet de fuseau horaire, par exemple America/New_York. Les noms reconnus de fuseau horaire sont listés dans la vue pg_timezone_names (voir Section 43.49, « pg_timezone_names »). PostgreSQL™ utilise les données zic pour cela, les mêmes noms sont donc reconnus par de nombreux autres logiciels ;
une abréviation de fuseau horaire, par exemple PST. Une telle indication ne définit qu'un décalage particulier à partir d'UTC, en contraste avec les noms complets de fuseau horaire qui peuvent aussi impliquer un ensemble de dates pour le changement d'heure. Les abréviations reconnues sont listées dans la vue pg_timezone_abbrevs (voir Section 43.48, « pg_timezone_abbrevs »). Le paramètre de configuration timezone ne peut pas être configuré à l'aide d'une abréviation de fuseau horaire, mais ces abréviations peuvent être utilisées dnas les saisies de date/heure et avec l'opérateur AT TIME ZONE ;
une spécification POSIX de fuseau sous la forme STDdécalage ou STDdécalageDST avec STD une abréviation de fuseau et décalage un décalage numérique en nombre d'heures à l'ouest d'UTC et DST une abréviation optionnelle de changement d'heure, à interpréter comme une heure avant le décalage donné. Par exemple si EST5EDT n'est pas déjà reconnu comme fuseau horaire, il est accepté et est fonctionnellement équivalent à l'heure du fuseau de la côte est des USA. Si un nom de changement d'heure est présent, il est interprété selon les règles régissant les changements d'heure utilisées dans l'entrée posixrules de la base de données des fuseaux horaires, zic. Dans une installation PostgreSQL™ standard, posixrules est identique à US/Eastern, pour que les spécifications POSIX des fuseaux horaires correspondent aux règles de changements d'heure aux États-Unis. Ce comportement peut, au besoin, être ajusté en remplaçant le fichier posixrules.
Il y a une différence conceptuelle et pratique entre les abréviations et les noms complets : les abréviations représentent toujours un décalage fixe par rapport à UTC alors que la plupart des noms complets impliquent une règle de changement d'heure et donc deux décalages possibles.
La fonctionnalité des fuseaux horaires POSIX peut accepter silencieusement des saisies erronées car il n'y a pas de vérification des abréviations de fuseaux horaires. Par exemple, SET TIMEZONE TO FOOBAR0 fonctionne mais conduit le système à utiliser en réalité une abréviation très particulière d'UTC.
Dans tous les cas, les noms des fuseaux horaires sont insensibles à la casse. (C'est un changement par rapport aux versions de PostgreSQL™ antérieures à la 8.2 qui étaient sensibles à la casse dans certains cas et pas dans d'autres.)
Ni les noms complets ni les abréviations ne sont codés en dur dans le serveur ; ils sont obtenus à partir des fichiers de configuration stockés sous .../share/timezone/ et .../share/timezonesets/ du répertoire d'installation (voir Section B.3, « Fichiers de configuration date/heure »).
Le paramètre de configuration timezone peut être fixé dans le fichier postgresql.conf ou par tout autre moyen standard décrit dans le Chapitre 17, Configuration du serveur. Il existe aussi quelques manières spéciales de le configurer :
si timezone n'est précisé ni dans postgresql.conf ni comme une option en ligne de commande du serveur, le serveur tente d'utiliser la valeur de la variable d'environnement TZ comme fuseau horaire par défaut. Si TZ n'est pas définie ou ne fait pas partie des noms de fuseau horaire connus par PostgreSQL™, le serveur tente de déterminer le fuseau horaire par défaut du système d'exploitation en vérifiant le comportement de la fonction localtime() de la bibliothèque C. Le fuseau horaire par défaut est sélectionné comme la correspondance la plus proche parmi les fuseaux horaires connus par PostgreSQL™ ;
la commande SQL SET TIME ZONE configure le fuseau horaire pour une session. C'est une autre façon d'indiquer SET TIMEZONE TO avec une syntaxe plus compatible avec les spécifications SQL ;
la variable d'environnement PGTZ, si le client la positionne, est utilisée par les applications fondées sur libpq pour envoyer une commande SET TIME ZONE au serveur lors de la connexion.
PostgreSQL™ utilise les dates du calendrier Julien pour tous les calculs de date/heure. Elles ont la propriété intéressante de permettre le calcul de toute date depuis 4713 avant Jésus Christ jusque loin dans le futur, avec pour seule hypothèse que l'année dure 365,2425 jours.
Les conventions pour les dates antérieures au 19ème siècle sont d'une lecture intéressante mais ne sont pas assez cohérentes pour être codées dans un gestionnaire de dates.
Types de données binaires