group by date and show the values of X(column) which depends on min time of that date and max time of that date in 1 row

I have table like

id  u_id     date       time      x
---|-----|------------|--------|-----
1  | 1   | 20200806   | 0900   | 60 
2  | 2   | 20200806   | 0905   | 60 
3  | 3   | 20200806   | 0910   | 61 
4  | 1   | 20200806   | 1015   | 62 
5  | 1   | 20200806   | 1830   | 61 
6  | 3   | 20200807   | 0915   | 61 
7  | 1   | 20200807   | 0920   | 62 
8  | 2   | 20200807   | 1820   | 63 
9  | 1   | 20200807   | 1835   | 59 

I want to group by date which user has the id of u_id =1 and get first time and value of x and get the last time and value of x in same row

it should be like

   date       firstTime  firstX   lastTime   lastX
   -----------|----------|--------|----------|----------
   20200806   |   0900   |   60   |   1830   | 61
   20200807   |   0920   |   62   |   1835   | 59

what i’ve tried

select
            p.created_date as date,
            min(p.created_time) as firstTime,
            max(p.created_time) as lastTime,
from
     passes as p
where
      p.id=1
group by p.created_date;

but i cant get the values of xs.

the rows:

Answer

One method uses conditional aggregation:

select p.created_date as date,
       min(p.created_time) as firstTime,
       max(p.created_time) as lastTime,
       max(case when seqnum = 1 then x end) as first_x,
       max(case when seqnum_desc = 1 then x end) as last_x,
from (select p.*,
             row_number() over (partition by id order by created_time) as seqnum,
             row_number() over (partition by id order by created_time desc) as seqnum_desc
      from passes p
     ) p
where p.id=1
group by p.created_date;

You can also phrase the conditional aggregation as:

       max(x) filter where (seqnum = 1) as first_x,
       max(x) filter where (seqnum_desc = 1) as last_x,

Another method uses arrays:

select p.created_date as date,
       min(p.created_time) as firstTime,
       max(p.created_time) as lastTime,)
       (array_agg(p.x order by p.created_date asc))[1] as first_x,
       (array_agg(p.x order by p.created_date desc))[1] as last_x
from passes p
where p.id = 1
group by p.created_date;