I was making a query like this to a PostgreSQL-database today:
SELECTextract(DAY FROM "time"),extract(HOUR FROM "time"),extract(MINUTE FROM "time"),count(DISTINCT "full_name")FROM"hits"WHERE"time"BETWEEN'2015-02-23'AND'2015-02-24'GROUP BYextract(DAY FROM "time"),extract(HOUR FROM "time"),extract(MINUTE FROM "time");
This is the kind of result I would get when trying it out in PSequel:
+-----------+-----------+-----------+-------+| date_part | date_part | date_part | count |+-----------+-----------+-----------+-------+| 23 | 0 | 0 | 5 || 23 | 0 | 1 | 1 || 23 | 0 | 2 | 1 || 23 | 0 | 3 | 1 || 23 | 0 | 4 | 4 || 23 | 0 | 5 | 2 || 23 | 0 | 6 | 2 || 23 | 0 | 7 | 3 |+-----------+-----------+-----------+-------+
I built the same query using Knex.js:
const resolution ='extract(day from "time"), extract(hour from "time"), extract(minute from "time")'db('hits').select(db.raw(`${resolution}, count(distinct "full_name")`)).whereBetween('time', [t1, t2]).groupByRaw(resolution).then(respond).catch(next)
This, however, got me a result like this:
[{ date_part: 0, count: "5" },{ date_part: 1, count: "1" },{ date_part: 2, count: "1" },{ date_part: 3, count: "1" },{ date_part: 4, count: "4" },{ date_part: 5, count: "2" },{ date_part: 6, count: "2" },{ date_part: 7, count: "3" }]
As you can tell, date_part
-columns for day
and hour
are missing. This is understandable because JavaScript objects can not have duplicate keys. To get around this issue, we need Knex.js to display our results using arrays. This is possible by using options
:
db('hits').select(db.raw(`${resolution}, count(distinct "full_name")`)).whereBetween('time', [t1, t2]).groupByRaw(resolution).options({ rowMode: 'array' }).then(respond).catch(next)
With the correct options, the result from Knex.js will look like this:
;[[23, 0, 0, '5'],[23, 0, 1, '1'],[23, 0, 2, '1'],[23, 0, 3, '1'],[23, 0, 4, '4'],[23, 0, 5, '2'],[23, 0, 6, '2'],[23, 0, 7, '3']]
Hi, I’m Max! I'm a fullstack JavaScript developer living in Berlin.
When I’m not working on one of my personal projects, writing blog posts or making YouTube videos, I help my clients bring their ideas to life as a freelance web developer.
If you need help on a project, please reach out and let's work together.
To stay updated with new blog posts, follow me on Twitter or subscribe to my RSS feed.