Ranking table Rows based on shift timing-CodePudding

i have a table called inputtable with columns equipment_id(int),telemetry_time(timestamp),i want to assign a row_number to the row from 8 am to 8am shift everyday. the below is the sample data link,

INPUT TABLE

equipment_id	telemetry_time
2	2022-06-16 04:00:00
2	2022-06-16 04:30:00
2	2022-06-16 06:00:00
2	2022-06-16 06:30:00
2	2022-06-16 08:00:00
2	2022-06-16 08:30:00
2	2022-06-16 20:00:00
2	2022-06-16 23:30:00
2	2022-06-17 02:30:00
2	2022-06-17 03:00:00
2	2022-06-17 10:30:00

REQUIRED OUTPUT

equipment_id	telemetry_time	Batch
2	2022-06-16 04:00:00	1
2	2022-06-16 04:30:00	2
2	2022-06-16 06:00:00	3
2	2022-06-16 06:30:00	4
2	2022-06-16 08:00:00	1
2	2022-06-16 08:30:00	2
2	2022-06-16 20:00:00	3
2	2022-06-16 23:30:00	4
2	2022-06-17 02:30:00	5
2	2022-06-17 03:00:00	6
2	2022-06-17 10:30:00	1

i have added desired output in the below link with table name desiredtable https://www.db-fiddle.com/f/vhZbgTG8TcJ7SwqanoWEUL/4 please help me im new to SQL,

CodePudding user response：

Shift your times back by eight hours to get the right date:

with date_shift as (
  select equipment_id, telemetry_time, 
         (telemetry_time - interval '8 hours')::date as tdate
    from inputtable
)
select equipment_id, telemetry_time, 
       row_number() over (partition by equipment_id, tdate
                              order by telemetry_time)
  from date_shift;

db<>fiddle here

CodePudding user response：

select
    *,
    dense_rank() over (partition by date,
    equipment_id
order by
        telemetry_time) as batch
from
    (
    select
        equipment_id,
        (telemetry_time - interval '8 hour')::date as date,
        telemetry_time
    from
        inputtable ) as t
order by
    equipment_id,
    telemetry_time

CodePudding user response：

Try this query Note: This query is not depend on the Equipment_id. and totally depend on the date and the time

   select  *,
   rank() OVER (PARTITION by  Shift_start_category::date, Shift_end_category::date ORDER BY telemetry_time  asc)
   from (
   select equipment_id, telemetry_time,
   case when(telemetry_time <=  telemetry_time::date   (' 8 hour')::interval) and  (telemetry_time >=  telemetry_time::date   (' 1920 minute')::interval)
   then  telemetry_time::date    (' -24 hour')::interval 
   when(telemetry_time >=  telemetry_time::date   (' 8 hour')::interval) and  (telemetry_time <=  telemetry_time::date   (' 1920 minute')::interval)
    then  telemetry_time::date    ('  24 hour')::interval 
   else telemetry_time::date   (' 8 hour')::interval end Shift_start_category,    
   case when(telemetry_time >=  telemetry_time::date   (' 8 hour')::interval) and  (telemetry_time <=  telemetry_time::date   (' 1920 minute')::interval)
   then  telemetry_time::date    ('  24 hour')::interval 
   when(telemetry_time <=  telemetry_time::date   (' 8 hour')::interval) and  (telemetry_time >=  telemetry_time::date   (' 1920 minute')::interval)
    then  telemetry_time::date    ('  24 hour')::interval 
   else telemetry_time::date   (' 8 hour')::interval end Shift_end_category
   from public.inputtable) A
  order by telemetry_time asc;

Shift_start_category and Shift_end_category fields only for display purposes .