8.5. Types date/heure

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

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 / 14 chiffres
date4 octetsdates seulement4713 avant JC5874897 après JC1 jour
time [ (p) ] [ without time zone ]8 octetsheures seulement00:00:00.0023:59:59.991 microseconde / 14 chiffres
time [ (p) ] with time zone12 octetsheures seulement, avec fuseau horaire00:00:00.00+1223:59:59.99-121 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.

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

Référez-vous à Annexe B pour une liste de noms de fuseaux horaire reconnus en entrée.

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

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

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

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.

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.8) 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, 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:

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 :

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.