CODEWITHSUNDEEP
sqlpostgresqldistinct
ragav hats
ragav hats

Reputation: 77

Postgresql Distinct Statement

How can i get the minutes distinct value with timestamp ... Like , if table contains 1 minute 100 records are there...so i want count of records present or not per minute ... For example,

SELECT DISTINCT(timestamp) FROM customers WHERE DATE(timestamp) = CURRENT_DATE

Result should be ..like 

timestamp         record
30-12-2019 11:30  5
30-12-2019 11:31  8

Upvotes: 2

Views: 58

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

One option would be ::date conversion for timestamp column including GROUP BY :

SELECT timestamp, count(*)
  FROM tab
 WHERE timestamp::date = current_date
GROUP BY timestamp

Demo for current day

timestamp::date might be replaced with date(timestamp) like in your case.

Update : If the table contains data with precision upto microseconds, then 

SELECT to_char(timestamp,'YYYY-MM-DD HH24:MI'), count(*)
  FROM tab
 WHERE date(timestamp) = current_date
 GROUP BY to_char(timestamp,'YYYY-MM-DD HH24:MI')

might be considered.

Upvotes: 3

karmakaze
karmakaze

Reputation: 36164

Try something like the following:

SELECT DATE_TRUNC('minute', timestamp) as timestamp, COUNT(*) as record
FROM customers
WHERE DATE(timestamp) = CURRENT_DATE
GROUP BY DATE_TRUNC('minute', timestamp)
ORDER BY DATE_TRUNC('minute', timestamp)

Upvotes: 1

Related Questions