Un chapitre de notre livre de recettes entièrement consacré à la manipulation de dates.
La requête suivante permet de connaître pour chaque mois d'une période donnée (ici de l'année courante) son nombre de jours.
select to_char(month, 'FMMonth YYYY') as month,
date_part('day',month + '1 month - 1 day'::interval) as lastday
from (
select (date_part('year', current_date) || '-' || m || '-01')::date as month
from generate_series(1, 12) m) months;
On obtient le résultat suivant :
month | lastday
----------------+---------
January 2006 | 31
February 2006 | 28
March 2006 | 31
April 2006 | 30
May 2006 | 31
June 2006 | 30
July 2006 | 31
August 2006 | 31
September 2006 | 30
October 2006 | 31
November 2006 | 30
December 2006 | 31
(12 rows)
Cela se fait avec les deux fontions suivantes :
create function extract_months(interval) returns integer language sql
as $f$
select 12 * extract(year from $1)::integer
+ extract(month from $1)::integer
$f$;
create function months_between(date,date) returns integer language sql
as $f$
select extract_months(age($2,$1))
$f$;
Cette fois-ci il s'agit donc de trouver le nombre de semaines entre deux dates quelconques. Ce qui rend la solution un poil compliquée, c'est que toutes les années n'ont pas le même nombre de semaines (52*7 ça donne que 364 jours).
On va donc faire ça en deux temps, d'abord trouver le nombre de semaines de chaque année faisant partie de l'intervalle, ensuite c'est facile.
Déjà, trouver les années comprises entre deux dates, ainsi que le premier janvier de l'année suivante :
dim# select year, to_date(year+1, 'YYYY-01-01') as d
from generate_series(extract(year from (current_date - interval '4 years')::date)::int,
extract(year from current_date)::int) as year;
year | d
------+------------
2003 | 01-01-2004
2004 | 01-01-2005
2005 | 01-01-2006
2006 | 01-01-2007
2007 | 01-01-2008
(5 lignes)
Maintenant, première ruse : si le premier janvier de l'année suivante est semaine 1, alors l'année donnée contient 52 semaines, sinon elle contient le nombre de semaine correspondant au numéro de semaine du premier janvier de l'année suivante :
dim=# select extract(year from d) - 1 as year,
dim-# case when extract(week from d) = 1
dim-# then extract(week from d - interval '1 week')
dim-# else extract(week from d) end as nb_weeks
dim-# from (
dim(# select year, to_date(year+1, 'YYYY-01-01') as d
dim(# from generate_series(extract(year from (current_date - interval '4 years')::date)::int,
dim(# extract(year from current_date)::int) as year) as x;
year | nb_weeks
------+----------
2003 | 52
2004 | 53
2005 | 52
2006 | 52
2007 | 52
(5 lignes)
Il reste à additionner toutes les semaines des années intermédiaires et à ajouter le numéro de semaine de l'année courante pour avoir le nombre de semaines depuis le premier janvier de l'année de la date de début. On enlèvera donc de cette somme le numéro de semaine de la date de début pour avoir notre résultat :
create or replace function weeks(timestamp, timestamp) returns int language sql
as $$
select extract(week from $2)::int
+
coalesce(
sum( case when extract(week from d) = 1
then extract(week from d - interval '1 week')
else extract(week from d) end ),
0)::int
-
extract(week from $1)::int
from (
select year, to_date(year+1, 'YYYY-01-01') as d
from generate_series(extract(year from $1)::int,
extract(year from $2)::int - 1) as year
) as x;
$$;
dim=# select weeks(current_date - interval '18 months', current_date);
weeks
-------
79
(1 ligne)
dim=# select weeks(current_date - interval '4 years', current_date);
weeks
-------
209
(1 ligne)
Et voilà :)
Note : on utilise COALESCE() sur le SUM() afin de savoir compter le nombre de semaines entre deux dates de la même année aussi !
Pour convertir d'un timestamp PostgreSQL vers un timestamp Unix:
select extract(epoch from now())::bigint;
date_part
------------
1162822549
(1 ligne)
Pour convertir d'un timestamp Unix vers un timestamp PostgreSQL:
select to_timestamp(1162822549);
to_timestamp
------------------------
2006-11-06 15:15:49+01
(1 ligne)
Conditions préalables
Imaginons une table contenant plusieurs colonnes dont une est une estampille. Par exemple la table mesures :
CREATE TABLE mesures (
estampille TIMESTAMP WITH TIMEZONE PRIMARY KEY,
valeur DOUBLE PRECISION);
Nous pouvons imaginer que les données sont temporellement réparties uniformément dans le temps (une donnée toutes les minutes par exemple)
Cas d'utilisation
On souhaite avoir les mesures entre deux dates en échantillonnant les données présentes dans la table mesures avec un intervalle régulier supérieur à la répartition initiale de la mesure (par exemple toutes les 5 minutes).
Requête
Nous allons utiliser la fonction EXTRACT avec comme argument EPOCH pour extraire le nombre de secondes écoulées depuis le 1er Janvier 1970 à minuit GMT sur l'estampille de chaque ligne. Nous pourrons ensuite utiliser la fonction modulo (%) pour fixer déterminer si la ligne est sélectionnable ou non.SELECT * FROM mesures WHERE estampille BETWEEN CURRENT_TIMESTAMP - '1 day'::INTERVAL AND CURRENT_TIMESTAMP AND ( (EXTRACT(EPOCH FROM estampille)::INTEGER ) % (5 * 60) ) = 0;Ainsi chaque fois que la date EPOCH sera un multiple de 5*60 secondes (5 minutes) le modulo sera égal à 0 et notre ligne sera sélectionnée.