Prenez le temps de bien gérer le temps

      PostgreSQL propose 4 grands types de données pour stocker les informations de temps : DATE, TIMESTAMP, TIME, INTERVAL.
      Le type DATE est précis à la journée, ce qui le rend très différent du type DATE proposé par Oracle précis à la seconde.
      Pour une précision plus grande il faut de toute façon passer pour la plupart des SGBDR au type TIMESTAMP avec ou sans la notion de TIME ZONE (fuseau horaire). Ce type de données est précis à la microsedonde. Attention avec SQL Server, le type TIMESTAMP est un synonyme de ROWVERSION utilisé en tant que "mécanisme d’affectation de version aux lignes de table". Il est d’ailleurs déconseillé d’utiliser le type TIMESTAMP avec ce SGBD pour de nouveaux développements, il n’existe sous sa forme actuelle que pour compatibilité ascendante.
      Le type TIME avec ou sans TIME ZONE permet de stocker des temps à la microseconde sans stocker la journée.
      Enfin INTERVAL, type standard SQL, désigne un intervalle entre 2 temps, la précision maximale avec PostgreSQL étant logiquement aussi la microseconde.
      Au niveau des fonctions la différence entre les SGBD est également importante. Les utilisateurs et applications Oracle utilisent massivement les fonctions propriétaires SYSDATE et SYSTIMESTAMP qui n’existent pas avec PostgreSQL. De plus la fonction CURRENT_TIMESTAMP fait partie de la norme SQL mais ne se comporte pas de la même façon entre les 2 SGBD ce qui peut amener des problèmes même si vous avez fait un effort de standardisation au niveau de votre code SQL.
      Heureusement PostgreSQL décrit très précisément le comportement de ses différentes fonctions temporelles et vous obtiendrez exactement le comportement désiré au niveau de votre code pour peu que vous y passiez un peu de temps.

      Tout d’abord comment se comportent les fonctions SYSDATE, CURRENT_DATE, SYSTIMESTAMP et CURRENT_TIMESTAMP avec Oracle ?

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> create table t1(c1 timestamp); Table created. SQL> INSERT INTO t1 SELECT current_timestamp FROM dual; 1 row created. SQL> INSERT INTO t1 SELECT current_timestamp FROM dual; 1 row created. SQL> select * from t1; C1 --------------------------------------------------------------------------- 26-DEC-15 12.10.31.072140 PM 26-DEC-15 12.10.38.160200 PM SQL> rollback; Rollback complete. SQL> INSERT INTO t1 SELECT systimestamp FROM dual; 1 row created. SQL> INSERT INTO t1 SELECT systimestamp FROM dual; 1 row created. SQL> select * from t1; C1 --------------------------------------------------------------------------- 26-DEC-15 12.11.04.247560 PM 26-DEC-15 12.11.08.381185 PM SQL> rollback; Rollback complete. SQL> INSERT INTO t1 SELECT CURRENT_DATE FROM dual; 1 row created. SQL> INSERT INTO t1 SELECT CURRENT_DATE FROM dual; 1 row created. SQL> select * from t1; C1 --------------------------------------------------------------------------- 26-DEC-15 12.12.17.000000 PM 26-DEC-15 12.12.24.000000 PM SQL> rollback; Rollback complete. SQL> INSERT INTO t1 SELECT SYSDATE FROM DUAL; 1 row created. SQL> INSERT INTO t1 SELECT SYSDATE FROM DUAL; 1 row created. SQL> select * from t1; C1 --------------------------------------------------------------------------- 26-DEC-15 12.13.08.000000 PM 26-DEC-15 12.13.16.000000 PM SQL> rollback; Rollback complete. SQL> select systimestamp from dual union all select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 26-DEC-15 12.14.48.886688 PM +01:00 26-DEC-15 12.14.48.886688 PM +01:00 SQL> select current_timestamp from dual union all select systimestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 26-DEC-15 12.15.06.975436 PM +01:00 26-DEC-15 12.15.06.975431 PM +01:00 SQL> select current_timestamp from dual union all select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 26-DEC-15 02.18.30.104437 PM +01:00 26-DEC-15 02.18.30.104437 PM +01:00

      Que peut-on tirer de ces quelques commandes ? La précision est conforme à ce qui était attendu : seconde pour DATE (fonctions CURRENT_DATE et SYSDATE) et microseconde pour les TIMESTAMP.
      Avec Oracle toutes les fonctions temporelles (SYSTIMESTAMP, CURRENT_TIMESTAMP etc.) se comportent de la même façon dans le sens où elles donnent le temps système. Ce temps évolue au sein d’une transaction. Au niveau d’une instruction si une fonction est rappelée elle donne le même résultat. Cependant des appels à des fonctions différentes dans la même instruction donnent des résultats différents.
      Enfin ne soyez pas surpris en reproduisant le test sous Windows. Avec ce système d’exploitation la précision des fonction Oracle CURRENT_TIMESTAMP et SYSTIMESTAMP est la milliseconde.
      Avec Oracle sur Windows :

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> select current_timestamp from dual union all select systimestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 26/12/15 13:00:31,833000 +01:00 26/12/15 13:00:31,833000 +01:00 SQL> select systimestamp from dual union all select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 26/12/15 13:00:36,234000 +01:00 26/12/15 13:00:36,234000 +01:00

      Passons à présent à PostgreSQL (psql est configuré par défaut donc j’ouvre explicitement des transactions avec begin;) :

psql (9.4.5) postgres=# create table t1(c1 timestamp); CREATE TABLE postgres=# begin; BEGIN postgres=# INSERT INTO t1 SELECT current_timestamp; INSERT 0 1 postgres=# INSERT INTO t1 SELECT current_timestamp; INSERT 0 1 postgres=# select * from t1; c1 ---------------------------- 2015-12-26 13:31:23.207154 2015-12-26 13:31:23.207154 (2 lignes) postgres=# rollback; ROLLBACK postgres=# begin; BEGIN postgres=# INSERT INTO t1 SELECT statement_timestamp(); INSERT 0 1 postgres=# INSERT INTO t1 SELECT statement_timestamp(); INSERT 0 1 postgres=# select * from t1; c1 ---------------------------- 2015-12-26 13:34:49.527654 2015-12-26 13:34:52.798989 (2 lignes) postgres=# rollback; ROLLBACK postgres=# begin; BEGIN postgres=# INSERT INTO t1 SELECT clock_timestamp(); INSERT 0 1 postgres=# INSERT INTO t1 SELECT clock_timestamp(); INSERT 0 1 postgres=# select * from t1; c1 ---------------------------- 2015-12-26 13:35:34.899791 2015-12-26 13:35:37.739448 (2 lignes) postgres=# rollback; ROLLBACK postgres=# begin; BEGIN postgres=# INSERT INTO t1 SELECT CURRENT_DATE; INSERT 0 1 postgres=# INSERT INTO t1 SELECT CURRENT_DATE; INSERT 0 1 postgres=# select * from t1; c1 --------------------- 2015-12-26 00:00:00 2015-12-26 00:00:00 (2 lignes) postgres=# rollback; ROLLBACK postgres=# begin; BEGIN postgres=# select now() union all select now(); now ------------------------------- 2015-12-26 13:38:53.203258+01 2015-12-26 13:38:53.203258+01 (2 lignes) postgres=# select statement_timestamp() union all select statement_timestamp(); statement_timestamp ------------------------------- 2015-12-26 13:41:01.515751+01 2015-12-26 13:41:01.515751+01 (2 lignes) postgres=# select clock_timestamp() union all select clock_timestamp(); clock_timestamp ------------------------------- 2015-12-26 13:41:10.507262+01 2015-12-26 13:41:10.507277+01 (2 lignes) postgres=# rollback; ROLLBACK postgres=# begin; BEGIN postgres=# select clock_timestamp(); clock_timestamp ------------------------------- 2015-12-26 13:46:45.020155+01 (1 ligne) postgres=# select transaction_timestamp(); transaction_timestamp ------------------------------- 2015-12-26 13:46:36.447377+01 (1 ligne) postgres=# rollback; ROLLBACK

      La précision de DATE est bien la journée avec PostgreSQL.
      Par ailleurs, comme vous pouvez le constater, STATEMENT_TIMESTAMP() est la fonction PostgreSQL ayant en pratique le comportement le plus proche des fonctions Oracle CURRENT_TIMESTAMP et SYSTIMESTAMP.
      Il est à noter qu’Oracle ne propose pas de fonction permettant de reproduire le comportement de la fonction PostgreSQL CLOCK_TIMESTAMP() tel que représenté dans l’exemple.
      Pas non plus d’équivalent Oracle aux fonctions PostgreSQL CURRENT_TIMESTAMP, NOW() et TRANSACTION_TIMESTAMP() qui donnent le temps de début de transaction de manière constante jusqu’à sa fermeture. Si vous voulez connaître le temps de début de transaction avec Oracle il vous faudra développer une solution maison.
      Si la fonction standard SQL CURRENT_TIMESTAMP répond à tous vos besoins utilisez-la. Cependant si elle ne suffit pas utilisez STATEMENT_TIMESTAMP(), TRANSACTION_TIMESTAMP() et CLOCK_TIMESTAMP() pour indiquer explicitement ce que vous souhaitez. Utiliser uniquement des fonctions standard est préférable dans la mesure du possible mais respecter la logique transactionnelle est encore plus important. Ne validez pas une transaction alors que ça n’a pas de sens fonctionnel simplement pour faire avancer CURRENT_TIMESTAMP avec PostgreSQL !

Mise à jour : 26/12/2015