Comment obtenir la différence de jours/mois/années (datediff) entre deux dates ?

Question

Je cherche un moyen de mettre en œuvre la Fonction SQLServer datée dans PostgreSQL. En d'autres termes, cette fonction renvoie le nombre (sous la forme d'un nombre entier signé) de limites de parties de date spécifiées franchies entre la date de début et la date de fin spécifiées.

datediff(dd, '2010-04-01', '2012-03-05') = 704 // 704 changes of day in this interval
datediff(mm, '2010-04-01', '2012-03-05') = 23  // 23 changes of month
datediff(yy, '2010-04-01', '2012-03-05') = 2   // 2 changes of year

Je sais que je pourrais faire 'dd' en utilisant simplement la soustraction, mais une idée pour les deux autres ?

Answer 1

La réponse de @WebWanderer est très proche de DateDiff en utilisant le serveur SQL, mais inexacte. Cela est dû à l'utilisation de la fonction age().

Par exemple, les jours compris entre le 29 juillet 2019 et le 25 juin 2020 devraient donner 332, mais en utilisant la fonction age(), on obtient 327. En effet, la fonction age() renvoie "10 mois 27 jours" et traite chaque mois comme 30 jours, ce qui est incorrect.

Vous devez utiliser l'horodatage pour obtenir un résultat précis. Par exemple.

ceil((select extract(epoch from (current_date::timestamp - <your_date>::timestamp)) / 86400))

Answer 2

Cette question est pleine de malentendus. Tout d'abord, il faut bien comprendre la question. Le demandeur veut obtenir le même résultat que lors de l'exécution de la fonction Fonction MS SQL Server DATEDIFF ( datepart , startdate , enddate ) où datepart prend dd , mm ou yy .

Cette fonction est définie par :

Cette fonction renvoie le nombre (sous la forme d'un nombre entier signé) de limites de parties de date spécifiées franchies entre la date de début et la date de fin spécifiées.

Cela signifie combien de limites de jours, de mois ou d'années sont franchies. Pas le nombre de jours, de mois ou d'années qui les séparent. C'est pourquoi datediff(yy, '2010-04-01', '2012-03-05') est 2, et non 1. Il y a moins de deux ans entre ces dates, ce qui signifie qu'une seule année entière s'est écoulée, mais que deux années se sont écoulées, de 2010 à 2011 et de 2011 à 2012.

Voici mes meilleurs efforts pour reproduire correctement la logique.

-- datediff(dd`, '2010-04-01', '2012-03-05') = 704 // 704 changes of day in this interval
select ('2012-03-05'::date - '2010-04-01'::date );
-- 704 changes of day

-- datediff(mm, '2010-04-01', '2012-03-05') = 23  // 23 changes of month
select (date_part('year', '2012-03-05'::date) - date_part('year', '2010-04-01'::date)) * 12 + date_part('month', '2012-03-05'::date) - date_part('month', '2010-04-01'::date)
-- 23 changes of month

-- datediff(yy, '2010-04-01', '2012-03-05') = 2   // 2 changes of year
select date_part('year', '2012-03-05'::date) - date_part('year', '2010-04-01'::date);
-- 2 changes of year

Answer 3

J'aimerais développer la réponse de Riki_tiki_tavi et diffuser les données. J'ai créé une fonction datediff qui fait presque tout ce que fait sql server. De cette façon, nous pouvons prendre en compte n'importe quelle unité.

create function datediff(units character varying, start_t timestamp without time zone, end_t timestamp without time zone) returns integer
language plpgsql
 as
 $$
DECLARE
 diff_interval INTERVAL; 
 diff INT = 0;
 years_diff INT = 0;
BEGIN
 IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
   years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);

   IF units IN ('yy', 'yyyy', 'year') THEN
     -- SQL Server does not count full years passed (only difference between year parts)
     RETURN years_diff;
   ELSE
     -- If end month is less than start month it will subtracted
     RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t)); 
   END IF;
 END IF;

 -- Minus operator returns interval 'DDD days HH:MI:SS'  
 diff_interval = end_t - start_t;

 diff = diff + DATE_PART('day', diff_interval);

 IF units IN ('wk', 'ww', 'week') THEN
   diff = diff/7;
   RETURN diff;
 END IF;

 IF units IN ('dd', 'd', 'day') THEN
   RETURN diff;
 END IF;

 diff = diff * 24 + DATE_PART('hour', diff_interval); 

 IF units IN ('hh', 'hour') THEN
    RETURN diff;
 END IF;

 diff = diff * 60 + DATE_PART('minute', diff_interval);

 IF units IN ('mi', 'n', 'minute') THEN
    RETURN diff;
 END IF;

 diff = diff * 60 + DATE_PART('second', diff_interval);

 RETURN diff;
END;
$$;

Answer 4

Voici un exemple complet avec le résultat. psql (10.1, serveur 9.5.10).

Vous obtenez 58, pas une valeur inférieure à 30.
Suppression de la fonction age(), a résolu le problème mentionné dans le post précédent.

drop table t;
create table t(
    d1 date
);

insert into t values(current_date - interval '58 day');

select d1
, current_timestamp - d1::timestamp date_diff
, date_part('day', current_timestamp - d1::timestamp)
from t;

     d1     |        date_diff        | date_part
------------+-------------------------+-----------
 2018-05-21 | 58 days 21:41:07.992731 |        58

Answer 5

Une autre solution, version pour la différence d'années :

SELECT count(*) - 1 FROM (SELECT distinct(date_trunc('year', generate_series('2010-04-01'::timestamp, '2012-03-05', '1 week')))) x

---

    2

(1 rang)

Et le même truc pour les mois :

SELECT count(*) - 1 FROM (SELECT distinct(date_trunc('month', generate_series('2010-04-01'::timestamp, '2012-03-05', '1 week')))) x

---

   23

(1 rang)

Dans une requête réelle, il peut y avoir des séquences d'horodatage groupées par heure/jour/semaine/etc au lieu de generate_series.

Ce site 'count(distinct(date_trunc('month', ts)))' peut être utilisé directement dans la partie "gauche" de la sélection :

SELECT sum(a - b)/count(distinct(date_trunc('month', c))) FROM d

J'ai utilisé generate_series() ici juste pour la brièveté.