postgresql split select by 12 hours-CodePudding

SQL

 SELECT py.id, 
           py.external_id, sv.provider_id,
           gr.path::text||'/'||ag.full_name::text,  
           st.name, sv.name, py.destination, py.amount,
           REPLACE(py.status_id, 'SUCCESS', 'Success'), 
           py.amount_status_id, 
           py.created_dt,
           py.provider_msg
      FROM payment AS py
INNER JOIN services AS sv ON sv.id = py.service_id
INNER JOIN source_types AS st ON st.id = py.source_type_id
INNER JOIN agents AS ag ON ag.id = py.agent_id
INNER JOIN groups AS gr ON gr.id = ag.group_id
     WHERE py.created_dt >= '2021-10-24' AND py.created_dt < '2021-10-25'

I have a request like this. How do I split it into 12 hours? So that I have the data for a certain date from 00.00 to 12.00 and from 12.00 to 00.00 For example, for October 24 from 00.00 to 12.00 and for October 24 from 12.00 to 00.00?

Ultimately, this request, I would like to get a result like this: ----- ------------- --------------------- | id | external_id | created_dt | ----- ------------- --------------------- | 111 | 333 | 2021-10-24 00:00:01 | ----- ------------- ---------------------

For example from 00.00 to 12.00

CodePudding user response：

The question is not very clear, but I guess you want split the data of one day into half a day.

You can use function date_part to extract hour of the timestamp, and then put it after where clause to split data. such as:

SELECT py.id, 
           py.external_id, sv.provider_id,
           gr.path::text||'/'||ag.full_name::text,  
           st.name, sv.name, py.destination, py.amount,
           REPLACE(py.status_id, 'SUCCESS', 'Success'), 
           py.amount_status_id, 
           py.created_dt,
           py.provider_msg
      FROM payment AS py
INNER JOIN services AS sv ON sv.id = py.service_id
INNER JOIN source_types AS st ON st.id = py.source_type_id
INNER JOIN agents AS ag ON ag.id = py.agent_id
INNER JOIN groups AS gr ON gr.id = ag.group_id
     WHERE py.created_dt >= '2021-10-24' AND py.created_dt < '2021-10-25'
       AND date_part('hour', py.created_dt) < 12