postgrsql sql

CREATE TABLE tests(timestamp bigint, val int);

INSERT INTO tests(timestamp, val) VALUES
(1514768400, 2),
(1514768401,4),
(1514769299,6),
(1514769300,8),
(1514769301,10),
(1514770199,12),
(1514770200,14),
(1514770201,16),
(1514771099,18),
(1514771100,18),
(1514771101,20),
(1514771999,22);

select distinct on (qwe) qwe,val1
        from (
        select (date_trunc('seconds', (to_timestamp(timestamp) - timestamptz 'epoch') / extract(epoch from interval '600 sec')) *
        extract(epoch from interval '600 sec') +
        timestamptz 'epoch') as qwe
, val as val1
        from tests
        order by qwe asc
        ) as tests2
        order by qwe
        ;
   
------------------
select
       avg(b.val) diff
              --,
              --TO_CHAR(TO_TIMESTAMP(b.timestamp), 'DD/MM/YYYY HH24:MI:SS')

from tests b
group by (DATE_PART('second', to_timestamp(b.timestamp)) )
order by diff

select 

      

              TO_CHAR(TO_TIMESTAMP(b.timestamp), 'DD/MM/YYYY HH24:MI:SS')

, DATE_PART('second', to_timestamp(b.timestamp))

from tests b

--group by (DATE_PART('second', to_timestamp(b.timestamp)) )

--order by diff

------------------------------

select distinct on (alignedMeasuredAt) alignedMeasuredAt, avgHeight, avgDiff

from (

       select (date_trunc('seconds',

                          (to_timestamp(time) - timestamptz 'epoch') / extract(epoch from interval '600 sec')) *

               extract(epoch from interval '600 sec') +

               timestamptz 'epoch') as alignedMeasuredAt

            , height                   as avgHeight

            , difficulty                   as avgDiff

       from blocks b

       where coin_id=29

       order by avgHeight

     ) as b2

order by alignedMeasuredAt

src: https://stackoverflow.com/questions/28085468/postgresql-extract-last-row-for-each-id


-----------------
https://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres
SELECT round( CAST(float8 '3.1415927' as numeric), 2);
https://stackoverflow.com/questions/13113096/how-to-round-an-average-to-2-decimal-places-in-postgresql