Count of Daily Users in PostgreSQL

Question

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'}]

There is no date or timestamp column in your users table, so how would you know on which day they were created? — user330315
– user330315, Commented Oct 22, 2018 at 18:09

Zack · Accepted Answer · 2018-10-22 18:30:13Z

3

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)

answered Oct 22, 2018 at 18:30

Zack

2,3511 gold badge10 silver badges13 bronze badges

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

4 Comments

Jordash Over a year ago

this works great! Any idea how to include days that have a 0 count as part of the results?

Zack Over a year ago

If you need to include days that aren't in your data, you can use something like generate_series (documentation) to create the dates you need.

Jordash Over a year ago

could you provide an example? I can't figure it out from the documentation

Zack Over a year ago

See this answer for information on using generate_series to "fill in the missing dates". If you're still having issues, you might want to create a new question.

Yevhen Bondar · Accepted Answer · 2018-10-22 18:30:00Z

2

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

answered Oct 22, 2018 at 18:30

Yevhen Bondar

4,7471 gold badge15 silver badges40 bronze badges

Comments

Nick Ellis · Accepted Answer · 2018-10-22 18:19:01Z

1

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

answered Oct 22, 2018 at 18:19

Nick Ellis

1,07711 silver badges25 bronze badges

6 Comments

Jordash Over a year ago

I'm getting this error: date_trunc(unknown, double precision) does not exist

Zack Over a year ago

The date_trunc function requires a Postgres TIMESTAMP-type value, not a UNIX timestamp type.

Nick Ellis Over a year ago

So the column is a double rather than an actual timestamp which means you can't use that function. you might be able to do date_trunc('day', to_timestamp(users.created)) "day",

Jordash Over a year ago

I got it working with this: date_trunc('day', to_timestamp(users.created)) "day", Now I just need to figureout how to get it into the right timezone

Nick Ellis Over a year ago

It should be UTC, so you can just convert from there

|

Collectives™ on Stack Overflow

Count of Daily Users in PostgreSQL

3 Answers 3

4 Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related