CODEWITHSUNDEEP
postgresqltimezone
user2193672
user2193672

Reputation: 217

Postgres count by date with timezone

I want to query a table to find out a count of objects created by date, day and month in Postgres.

Fetch count for last 30 days

SELECT d.date, count(se.id)
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
FROM generate_series(0, 30) AS offs) d LEFT OUTER JOIN
     someTable se 
     ON d.date = to_char(date_trunc('day', se.created_at), 'YYYY-MM-DD')  
GROUP BY d.date;

Fetch count by day

select to_char(created_at,'day') as Day,
       extract(month from created_at) as Date,
       count("id") as "Count"
from someTable
group by 1,2

Fetch count by month

select to_char(created_at,'Mon') as mon,
       extract(year from created_at) as yyyy,
       count("id") as "Count"
from someTable
group by 1,2

This works fine for me. The problem that I have is, I want the data to be fetched based on different timezones. I have stored the time in UTC. I would be able to run these queries with different timezones.

What is the best way to do it?

Upvotes: 1

Views: 1512

Answers (1)

BinaryMee
BinaryMee

Reputation: 2142

Check this answer to get the datetime in Postgres with different timezone.

Fetch count for last 30 days

SELECT d.date, count(se.id)
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
FROM generate_series(0, 30) AS offs) d LEFT OUTER JOIN
     someTable se 
     ON d.date = to_char(date_trunc('day', se.created_at::timestamp with time zone at time zone 'EST'), 'YYYY-MM-DD')  
GROUP BY d.date;

Fetch count by day

select to_char(created_at::timestamp with time zone at time zone 'EST','day') as Day,
       extract(month from created_at) as Date,
       count("id") as "Count"
from someTable
group by 1,2

Fetch count by month

select to_char(created_at::timestamp with time zone at time zone 'EST','Mon') as mon,
       extract(year from created_at) as yyyy,
       count("id") as "Count"
from someTable
group by 1,2

Also refer to this Postgres documentation to learn about timezone with datetime.

Upvotes: 3

Related Questions