i have a table in postgresql database called orders. where all the order related informations are stored. now, if an order gets rejected that certain order row gets moved from the orders table and gets stored in the rejected_orders table. As a result, the count function does not provide the correct number of orders.

Now, if I want to get the number of order request(s) in a certain day. I have to subtract the id numbers between the last order of the day and first order of the day. Below, i have the query for number total request for March 1st, 2022. Sadly, the previous employe forgot to save the timezone correctly in the database. Data is saved in the DB at UTC 00 timezone, Fetched data needs to be in GMT 06 timezone.

select 
(select id from orders
 where created_at<'2022-03-02 00:00:00 06' 
order by created_at desc limit 1
) 
- 
(select id from orders 
where created_at>='2022-03-01 00:00:00 06' 
order by created_at limit 1
) as march_1st;



march_1st 
-----------
185

Now, If I want to get total request per day for certain time period(let's for month March, 2021). how can I do that in one sql query without having to write one query per day ?

To wrap-up,

total_request_per_day = id of last order of the day - id of first order of the day.

How do I write a query based on that logic that would give me total_request_per_day for every day in a certain month. like this,

|Date       | total requests|
|01-03-2022 | 187           |
|02-03-2022 | 202           |
|03-03-2022 | 227           |
................
................

CodePudding user response：

With respect, using id numbers to determine numbers of rows in a time period is incorrect. DELETEing rows leaves gaps in id number sequences; they are not designed for this purpose.

This is a job for date_trunc(), COUNT(*), and GROUP BY.

The date_trunc('day', created_at) function turns an arbitrary timestamp into midnight on its day. For example, it turns ``2022-03-02 16:41:00into2022-03-02 00:00:00`. Using that we can write the query this way.

SELECT COUNT(*) order_count, 
       date_trunc('day', created_at) day
  FROM orders
 WHERE created_at >= date_trunc('day', NOW()) - INTERVAL '7 day'
   AND created_at <  date_trunc('day', NOW())
 GROUP BY date_trunc('day', created_at)

This query gives the number of orders on each day in the last 7 days.

Every minute you spend learning how to use SQL data arithmetic like this will pay off in hours saved in your work.

CodePudding user response：

Try this :

SELECT  d.ref_date :: date AS "date"
     , count(*) AS "total requests"
  FROM generate_series('20220301' :: timestamp, '20220331' :: timestamp, '1 day') AS d(ref_date)
  LEFT JOIN orders
    ON date_trunc('day', d.ref_date) = date_trunc('day', created_at)
 GROUP BY d.ref_date

• generate_series() generates the list of reference days where you want to count the number of orders

• Then you join with the orders table by comparing the reference date with the created_at date on year/month/day only. LEFT JOIN allows you to select reference days with no existing order.

• Finally you count the number of orders per day by grouping by reference day.