Question 179128 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Group By Date with hours offset

Is it possible to use a different start and end time of the day when using the group by clause on the postgres
The requirement arose due to the business requirement where shops have different shop open and close hours so to calculate the sales for a day a shop will be open from morning 10 am to the next morning 3 am. In such a scenario the group by clause should consider 4 am to 3.59:59 am the next day as a single day instead of 00 to 23:59:59.

There is an existing feature AT TIMEZONE by postgres, but is it possible to do the group by clause without using AT TIMEZONE as it is not the intended use case?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Got the response from chatGPT

    Yes, it is possible to achieve the desired grouping without using the AT TIMEZONE function in PostgreSQL. One approach is to use conditional expressions to adjust the timestamps before grouping them.

    Here's an example query that demonstrates how you can group the sales by the desired time range:

    SELECT
  CASE
    WHEN EXTRACT(HOUR FROM sale_timestamp) >= 4 THEN DATE_TRUNC('day', sale_timestamp)
    ELSE DATE_TRUNC('day', sale_timestamp) - INTERVAL '1 day'
  END AS sale_day,
  COUNT(*) AS total_sales
FROM
  sales
GROUP BY
  sale_day;

    In this query, we use the CASE expression to check if the hour of the sale_timestamp is greater than or equal to 4. If it is, we truncate the timestamp to the day using DATE_TRUNC('day', sale_timestamp). Otherwise, we subtract one day from the truncated timestamp to consider it as part of the previous day's sales.

    By grouping the adjusted timestamps, you can get the desired result. Adjust the logic inside the CASE expression to fit your specific shop opening and closing hours.

    Keep in mind that using AT TIMEZONE can simplify the adjustment process, especially if you need to handle time zones and daylight saving time changes. However, if you prefer not to use it for this particular use case, the conditional approach outlined above should work effectively.


  2. 0

    To have the day start at 4 a.m. rather than midnight, then subtract 4 hours from the time :

    Simple data :

    CREATE TABLE sales (
  id int,
  sale_timestamp timestamp
);

insert into sales values
(1, '2023-01-01 04:00:15'), -- 2023-01-01
(2, '2023-01-01 04:00:15'), -- 2023-01-01
(3, '2023-01-02 02:00:00'), -- 2023-01-01 (since it is before 4am)
(4, '2023-01-02 04:15:15'), -- 2023-01-02
(5, '2023-01-02 04:15:15'); -- 2023-01-02

select (sale_timestamp - interval '1 hour' * 4)::date as sale_day, count(1)
from sales
group by sale_day

    Result :

    sale_day count
    2023-01-01 3
    2023-01-02 2

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search