CODEWITHSUNDEEP
sqlpostgresqlcharts
tobspr
tobspr

Reputation: 8376

Database Query to generate a Time-based Chart

I have a logins table in the following (simplified) structure:

id | login_time
---------
1  | 2019-02-04 18:14:30.026361+00
2  | 2019-02-04 22:10:19.720065+00
3  | 2019-02-06 15:51:53.799014+00

Now I want to generate chart like this:

https://prnt.sc/mifz6y

Basically I want to show the logins within the past 48 hours.

My current query:

SELECT count(*), date_trunc('hour', login_time) as time_trunced FROM user_logins
WHERE login_time > now() - interval '48' hour
GROUP BY time_trunced
ORDER BY time_trunced DESC

This works as long as there are entries for every hour. However, if in some hour there were no logins, there will be no entry selected, like this:

time_trunced | count
---------------------
12:00        | 1
13:00        | 2
15:00        | 3
16:00        | 5

I would need a continous query, so that I can simply put the count values into an array:

time_trunced | count
---------------------
12:00        | 1
13:00        | 2
14:00        | 0 <-- This is missing
15:00        | 3
16:00        | 5

Based on that I can simply transform the query result into an array like [1, 2, 0, 3, 5] and pass that to my frontend.

Is this possible with postgresql? Or do I need to implement my own logic?

Upvotes: 1

Views: 322

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think I would do:

select gs.h, count(ul.login_time)
from generate_series(
   date_trunc('hour', now() - interval '48 hour'),
   date_trunc('hour', now()),
   interval '1 hour'
) gs(h) left join
     user_logins ul
     on ul.login_time >= gs.h and
        ul.login_time < gs.h + interval '1 hour'
group by gs.h
order by gs.h;

Upvotes: 2

Tom Mac
Tom Mac

Reputation: 9853

This can almost certainly be tidied up a bit but should give you some ides. Props to clamp for the generate_series() tip :

SELECT t.time_trunced,coalesce(l.login_count,0) as logins 
FROM
(
 -- Generate an inline view with all hours between the min & max values in user_logins table
 SELECT date_trunc('hour',a.min_time)+ interval '1h' * b.hr_offset as time_trunced
 FROM (select min(login_time) as min_time from user_logins) a
 JOIN (select generate_series(0,(select ceil((EXTRACT(EPOCH FROM max(login_time))-EXTRACT(EPOCH FROM min(login_time)))/3600) from user_logins)::int) as hr_offset) b on true
) t
LEFT JOIN 
 (
 -- OP's original query tweaked a bit
 SELECT count(*) as login_count, date_trunc('hour', login_time) as time_trunced
 FROM user_logins
 GROUP BY time_trunced
) l on t.time_trunced=l.time_trunced
order BY 1 desc;

Upvotes: 1

Related Questions