CODEWITHSUNDEEP
sqlpostgresqldate
Wilber C
Wilber C

Reputation: 49

Group By day on PostgreSQL depending on country time zone

I want to group by day counting registers of "ORDERS" table, but I have registers of different timezones because of the countrys.

with "end_datetime" as (
        select
            current_timestamp as "end_date"
        ), "start_datetime" as (
            select case
                when date_trunc('weeks', (select "end_date" from "end_datetime") - '8 weeks'::interval) < '2022-01-01 00:00:00' 
                    then '2022-01-01 00:00:00'
                    else date_trunc('weeks', (select "end_date" from "end_datetime") - '8 weeks'::interval)
                end as "initial_date"
        ),"principal" as (
            select DATE_TRUNC('day',o."createdAt")::TIMESTAMP::DATE  ,
                    COUNT(o.id) AS "countTotal",
                    lower(countries."name") as "country",
                    (select "initial_date" from "start_datetime") as initial_date,
                    (select "end_date" from "end_datetime" as end_date)
            from "Orders" as o
                left join "Cities" as "cities" on "cities"."id" = o."cityID"
                left join "Countries" as "countries" on "countries"."id" = "cities"."countryID"
                left join "Enterprises" as "enterprises" on "enterprises".id = o."enterpriseID"
                left join "GeneralTypes" as "generaltypes" on "generaltypes".subtype = o."statusID" 
            where o."createdAt" between (select "initial_date" from "start_datetime") and (select "end_date" from "end_datetime")       --Info de las últimas 8 semanas
            GROUP BY DATE_TRUNC('day',o."createdAt"), "country" 
        )select * from "principal"

enter image description here

Is there a good way to do that?

The field o."createdAt" is timestamptz

Upvotes: 0

Views: 475

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22952

You will need a table mapping country to timezone name. Your problem is that many countries have multiple timezones so you will presumably have to pick one city in each country to base your timezone off.

Then you can do AT TIME ZONE like this:

=> SELECT tz, CURRENT_TIMESTAMP AT TIME ZONE tz FROM (VALUES ('Europe/Paris'), ('Europe/Moscow')) t (tz);
      tz       │         timezone          
───────────────┼───────────────────────────
 Europe/Paris  │ 2022-02-02 18:29:47.56131
 Europe/Moscow │ 2022-02-02 20:29:47.56131
(2 rows)

Upvotes: 1

Related Questions