| Documentation PostgreSQL 8.0.25 | ||||
|---|---|---|---|---|
| Précédent | Arrière rapide | Chapitre 8. Types de données | Avance rapide | Suivant |
PostgreSQL supporte l'ensemble des types date et heure de SQL, montrés dans Tableau 8-9. Les opérations disponibles sur ces types de données sont décrit dans Section 9.9.
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 | intervals de temps | -178000000 années | 178000000 années | 1 microseconde / 14 chiffres |
| date | 4 octets | dates seulement | 4713 avant JC | 5874897 après JC | 1 jour |
| time [ (p) ] [ without time zone ] | 8 octets | heures seulement | 00:00:00.00 | 23:59:59.99 | 1 microseconde / 14 chiffres |
| time [ (p) ] with time zone | 12 octets | heures seulement, avec fuseau horaire | 00:00:00.00+12 | 23:59:59.99-12 | 1 microseconde / 14 chiffres |
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 : Quand les valeurs timestamp sont stockées en tant que nombre à virgule flottante (actuellement le défaut), la limite réelle de la précision pourrait être inférieure à 6. Les valeurs timestamp sont stockées en tant que nombre de secondes avant ou après le 1er janvier 2000 à minuit. La précision par microseconde est obtenue pour les dates proches du 1er janvier 2000 mais la précision se dégrade pour les dates suivantes. Quand les valeurs timestamp sont stockées en tant qu'entier sur huit octets (une option au moment de la compilation), la précision en microseconde est disponible pour toute l'étendue des valeurs. Néanmoins, les valeurs de type timestamp codées avec des entiers sur huit octets ont une échelle de date plus limitées 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 en tant que nombre à virgule flottante ou en tant qu'octet sur huit chiffres. Dans le cas de la virgule flottante, les valeurs interval larges voient leur précision se dégrader au fur et à mesure que l'intervalle croît.
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.
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.5 pour plus d'information. SQL requière la syntaxe suivante:
type [ (p) ] 'value'
où 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.
Tableau 8-10 montre les formats de date possibles pour les entrées 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 MDYMDY; 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 2003 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 dans 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. É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
| 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 précisé en lettres |
Tableau 8-12. Saisie des zones de date
| Exemple | Description |
|---|---|
| PST | Heure standard du Pacifique (Pacific Standard Time) |
| -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 des militaires pour GMT |
| z | Version courte de zulu |
Référez-vous à Annexe B pour une liste de noms de fuseaux horaire reconnus en entrée.
Les valeurs d'entrée valides sont constituées par la concaténation d'une date, d'une heure, suivi d'un fuseau horaire optionnel, d'un qualificatif optionnel AD (avant Jésus Christ) ou BC (après Jésus Christ). (Autrement, AD/BC peut 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. De plus, le format
January 8 04:05:06 1999 PST
très courant, est supporté.
Le standard SQL différencie les littéraux timestamp without time zone et timestamp with time zone par l'existence d'un << + >> ; ou << - >>. Du coup, suivant 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 diffère du standard en réclamant que les littéraux timestamp with time zone soient explicitement typés :
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
Si un littéral n'est pas spécifiquement indiqué comme étant de type timestamp with time zone, PostgreSQL ignorera silencieusement toute indication de fuseau horaire dans la valeur littérale. C'est-à-dire que la valeur résultante date/time est dérivée des champs date/time dans la valeur en entrée et 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.9.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.
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.
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. (En particulier, now et les chaînes relatives sont converties en une valeur temps spécifique dès qu'elles sont 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ées | 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ébut de la transaction courante |
| today | date, timestamp | minuit aujourd'hui |
| tomorrow | date, timestamp | minuit demain |
| yesterday | date, timestamp | minuit hier |
| allballs | ||
| 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 spécification de la précision en option. (Voir Section 9.9.4.) Notez néanmoins que ce sont des fonctions SQL et qu'elles ne sont pas reconnues en tant que chaînes en entrée de la donnée.
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 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 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ée | 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, 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.8) permet aussi, de manière plus flexible,
pour formater les affichages de date/heure.
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, particulièrement en respect des règles de changement d'heure. PostgreSQL supporte actuellement les règles de changement d'heure pour la période de 1902 à 2038 (correspondant à l'échelle intégrale du temps système Unix). Les périodes en dehors de cette échelle sont prises en tant que << temps standard >> pour le 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 bizarre de types de date/heure et de possibilités. Deux problèmes sont évidents:
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 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 l'heure d'hiver.
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 le standard SQL. PostgreSQL utilise votre fuseau horaire pour tous les types qui ne contiennent qu'une date ou une heure.
Toutes les dates et heures, conscientes du fuseau horaire, sont stockées en interne en UTC. Ils sont convertis en heure locale dans la zone spécifiée par le paramètre de configuration timezone avant d'être affiché au client.
Le paramètre de configuration timezone peut être initialisé dans le fichier postgresql.conf ou de tous les autres moyens standards décrit dans Section 16.4. Il existe aussi quelques moyens spéciaux pour le configurer :
Si timezone n'est spécifié ni dans postgresql.conf ni comme option en ligne de commande pour postmaster, 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 cette 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 elle est mise à jour par le client, est utilisée par les applications basées sur libpq pour envoyer une commande SET TIME ZONE au serveur lors de la connexion.
Voir Annexe B pour avoir une liste des fuseaux horaires disponibles.
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.
| Précédent | Sommaire | Suivant |
| Types de données binaires | Niveau supérieur | Type Boolean |