CODEWITHSUNDEEP
sqlpostgresql
Neil.Corbin
Neil.Corbin

Reputation: 23

How do I count distinct timestamps for each day in postgres?

I have some sql below that returns

Output

select
  distinct(file_modified_datetime),
  count(distinct(date_trunc('day',file_modified_datetime)))
from inv_by_wh_and_lot
group by file_modified_datetime

I'm looking to show a subtotal of this count for each day whereby 2021-06-23 would return 2

Upvotes: 0

Views: 991

Answers (2)

Jim Jones
Jim Jones

Reputation: 19653

Cast the timestamp to date and group by it:

SELECT f::date,sum(c) FROM (
  SELECT file_modified_datetime,
     count(distinct file_modified_datetime) FROM t
  GROUP BY file_modified_datetime) j (f,c)
GROUP BY f::date;

Demo: db<>fiddle

WITH t (file_modified_datetime) AS (
  VALUES ('2021-06-01 11:24:55'::timestamp),
         ('2021-06-01 11:24:55'::timestamp),
         ('2021-06-01 18:26:51'::timestamp),
         ('2021-06-05 19:08:12'::timestamp)
)
SELECT f::date,sum(c) FROM (
  SELECT file_modified_datetime,
     count(distinct file_modified_datetime) FROM t
  GROUP BY file_modified_datetime) j (f,c)
GROUP BY f::date;

     f      | sum 
------------+-----
 2021-06-05 |   1
 2021-06-01 |   2
(2 rows)

Upvotes: 1

JuanDM
JuanDM

Reputation: 1330

You can use this, casting the timestamp to date in the group by

select
  file_modified_datetime::date,
  count(*)
from inv_by_wh_and_lot
group by file_modified_datetime::date

Upvotes: 0

Related Questions