Postgresql Distinct Statement

Question

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

Barbaros Özhan · Accepted Answer · 2019-12-30 08:16:26Z

3

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.

edited Dec 30, 2019 at 8:16

answered Dec 30, 2019 at 7:22

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

karmakaze Over a year ago

Using GROUP BY timestamp might not be what's wanted as timestamp has high resolution (e.g. microseconds).

Barbaros Özhan Over a year ago

yes, you're right for the option including microseconds precision @karmakaze , but no problem exists for the data in OP's case.

karmakaze · Accepted Answer · 2019-12-30 07:35:01Z

1

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)

answered Dec 30, 2019 at 7:35

karmakaze

36.4k1 gold badge34 silver badges33 bronze badges

Collectives™ on Stack Overflow

Postgresql Distinct Statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related