CODEWITHSUNDEEP
postgresql
Jordash
Jordash

Reputation: 3103

Count of Daily Users in PostgreSQL

I have a database that looks like this

users
phone        created
8001234578   1540231160
9001234578   1540220360
1001234578   1540144760
2001234578   1540058360

Note that the created column is a unix timestamp.

I want to group them by users created on the same day with a count of users, so the above example database should return something like this.

[
  {day: '10/22/2018', count: 2},
  {day: '10/21/2018', count: 1},
  {day: '10/20/2018', count: 1},
]

I tried learning about the to_char command but I couldn't figure it out, this is what I tried:

SELECT 
  to_char(created, 'Day') as day,
COUNT(*) as count
FROM users
WHERE created >= ${startDate} AND created <= ${endDate}
GROUP BY to_char(created, 'Day')

It returned this:

[{day: ' .ay', count: '4'}]

Upvotes: 1

Views: 1712

Answers (3)

Zack
Zack

Reputation: 2341

First, if it's within your control, you should consider using Postgres's built in TIMESTAMP data type, instead of storing your dates as UNIX timestamps. This will make your queries much easier.

That being said, if you're stuck with using UNIX timestamps (presumably stored as integers), you'll have to convert them to a TIMESTAMP anyways to get what you want. You can use the TO_TIMESTAMP to convert from the UNIX timestamp, and use the DATE_TRUNC function to just get the date portion:

SELECT 
    DATE_TRUNC('day', TO_TIMESTAMP(created)), 
    COUNT(*)
FROM users
GROUP BY DATE_TRUNC('day', TO_TIMESTAMP(created))

Of course, if you're storing the dates as a Postgres TIMESTAMP, it's the same query, but simpler:

SELECT 
    DATE_TRUNC('day', created), 
    COUNT(*)
FROM users
GROUP BY DATE_TRUNC('day', created)

Upvotes: 3

Yevhen Bondar
Yevhen Bondar

Reputation: 4707

You can set datetime format, so you got result like this

postgres# select to_char(now(), 'DD Mon YYYY');
   to_char   
-------------
 22 Oct 2018
(1 row)

In your case query will be like this

SELECT 
  to_char(created, 'DD Mon YYYY') as day,
COUNT(*) as count
FROM users
WHERE created >= ${startDate} AND created <= ${endDate}
GROUP BY to_char(created, 'DD Mon YYYY')

Here you can find datetime format specification https://www.postgresql.org/docs/9.6/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE

Upvotes: 2

Nick Ellis
Nick Ellis

Reputation: 1077

SELECT date_trunc('day', users.created) "day", count(*) as count
FROM users
WHERE created >= ${startDate} AND created <= ${endDate}
GROUP BY 1

Upvotes: 1

Related Questions