history_query.sql

--
-- data.pheno.fr - Phenological data portal of TEMPO
-- Copyright © 2019 TEMPO (contact-tempo@inrae.fr)
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program. If not, see <http://www.gnu.org/licenses/>.
--

select * from cache c join synchronization s on c.synchroref=s.synchroref where s.datasource=1
select count(*) from cache

	
ALTER TABLE synchronization RENAME COLUMN synchroref TO synchroref;


select distinct(t.rank) from cache c join taxon t on c.taxon=t.id 


select distinct(t.rank) from  taxon t  


alter table taxon drop column category

SELECT COUNT(*) AS nb, genre FROM
        (SELECT name_of_taxon_parent(taxon, 80) AS genre FROM
                (SELECT DISTINCT taxon FROM cache) AS t1) AS t2
GROUP BY t2.genre





  SELECT COUNT(*) AS nb FROM    (  (SELECT name_of_taxon_parent(taxon, 80) AS kingdom FROM
                (SELECT DISTINCT taxon FROM cache) AS t1)) as dd





 SELECT t.rank, COUNT(distinct(t.id)) FROM taxon AS t
                JOIN cache ch ON (t.id=ch.taxon)
                JOIN synchronization AS s ON ch.synchroref=s.synchroref
        WHERE s.datasource = 2 AND t.id not in (select t.parent from taxon)
      
        GROUP BY t.rank




      
        
        
  SELECT distinct(kingdom) AS nb FROM ((SELECT name_of_taxon_parent(taxon, 80) AS kingdom FROM
                (
 SELECT distinct(taxon) FROM taxon AS t
                JOIN cache ch ON (t.id=ch.taxon)
                JOIN synchronization AS s ON ch.synchroref=s.synchroref
        WHERE s.datasource = 2 ) AS t1)) as dd


        
        
        SELECT s FROM dataSource s JOIN synchronization sc ON sc.dataSource=s.id WHERE sc.synchroRef IN ('2019-11-27T14:05:33.642')


SELECT DISTINCT EXTRACT(year from datetime) AS year 
FROM cache AS c join synchronization sc 
on sc.synchroref=c.synchroref 
where  sc.datasource IN (15) 
ORDER BY year


SELECT DISTINCT YEAR(datetime) AS year 
FROM fr.soeretempo.gwt.server.model.Cache AS c , fr.soeretempo.gwt.server.model.Synchronization sc 
WHERE sc.synchroRef=c.synchroRef 
AND  sc.datasource IN (15) 
ORDER BY year



SELECT count(c) FROM cache c join synchronization s on c.synchroref=s.synchroref where s.tablename='cache' and s.datasource=15

SELECT count(c) FROM site c join synchronization s on c.synchronization=s.id where s.datasource=2

SELECT count(c) FROM stage c join synchronization s on c.synchronization=s.id where s.datasource=15

SELECT count(c) FROM taxon c join synchronization s on c.synchronization=s.id where s.datasource=2

SELECT count(c) FROM year c join synchronization s on c.synchronization=s.id where s.datasource=15




--select count(id) from cache where license is null

select latitude, longitude, name from site where name in('coccinelle', 'jardin', '', 'Collège Arthur Rimbaud', 'grand-duché')

--update site set latitude=1, longitude=1 where name='NEUILH'

--select min(latitude) , max(latitude) , min(longitude), max(latitude) from site

--select distinct synchroref from cache

--select count(site) from cache where synchroref not in('2019-12-19 12:21:16.972','2019-12-19 12:39:24.604','2019-11-29 11:15:32.748')

select distinct c.synchroref from cache c join synchronization s on c.synchroref=s.synchroref






select * from query where (select DATEDIFF(mm, NOW()::timestamp, datetime::timestamp)) =3

select DATEDIFF('month', NOW()::timestamp, datetime::timestamp) from query

SELECT DATEDIFF('MONTH', cast('2013-01-01' as timestamp), cast('2013-02-02' as timestamp))

select DATE_PART('month',NOW()::timestamp - "datetime"::timestamp ) - DATE_PART('month', NOW()::timestamp - "datetime"::timestamp  ) AS approved from query






select * from query where (select NOW()::date - datetime::date) >120
--ou
select * from query where (select CAST (NOW() AS date) - CAST (datetime AS date)) >120