group by time or hour in knex

Question

I am using Postgres with NodeJS and Knex. I have a number of records with created_at format 2016-12-12 14:53:17.243-05. I want to group all records by the hour such that:

Hour 14:00:00-15:00:00 would have n records within that hour.
Hour 15:00:00-16:00:00 would have n records within that hour.
etc...

I was going to do this by simply querying for the records I want, then using momentJS to group them by hour, but I think this should be handled on the query. I'm just not sure what the syntax looks like.

Here's my starting query:

  getChatVolume: function(startDate, endDate) {
    var chats = db.knex
        .select(['sessions.id', db.knex.raw('sessions.start_timestamp::date as date')])
        .from('sessions')
        .where(db.knex.raw('sessions.start_timestamp'), '>=', startDate)
        .andWhere(db.knex.raw('sessions.start_timestamp'), '<=', endDate)
        .groupByRaw("date_trunc('hour', sessions.start_timestamp)")
    return chats;
  },

This explains how you can do it for MySQL, Oracle, and SQL Server, but not Knex.

Evan Carroll · Answer 1 · 2016-12-12T23:20:57.630

1

groupByRaw — .groupBy(sql)
Adds a raw group by clause to the query.

so try this,

.groupByRaw("date_trunc('hour', messages.created_at)")

edited Dec 12 '16 at 23:20

answered Dec 12 '16 at 20:46

Evan Carroll

78,363
46
261
468

`missing FROM-clause entry for table "date_trunc('hour', messages"` - – user3871 Dec 12 '16 at 20:48
any reason why this would happen? – user3871 Dec 12 '16 at 23:17
I figured you meant that so I tried it and got `column "messages.id" must appear in the GROUP BY clause or be used in an aggregate function` – user3871 Dec 12 '16 at 23:26
delete that from your select list, you're not grouping by that. – Evan Carroll Dec 12 '16 at 23:28
Sorry Evan, I've had to modify what I'm querying for, but the requirement to group by Hour still stands. See above edit. With ur code, I'm still getting error `column "sessions.start_timestamp" must appear in the GROUP BY clause or be used in an aggregate function` – user3871 Dec 12 '16 at 23:33
I've also tried `.groupBy('extract(hour from timestamp sessions.start_timestamp)')` AND `.groupByRaw('extract(hour from timestamp sessions.start_timestamp)')` with errors `rejection error: missing FROM-clause entry for table "extract(hour from timestamp sessions”` AND `syntax error at or near “sessions”` respectively – user3871 Dec 12 '16 at 23:34
stop using groupBy with functions, we know we need groupByRaw that was a bug in the documentation of knex. I've [already patched it](https://github.com/knex/documentation/pull/15). You don't undrestand how `GROUP BY` works, and that's another whole problem. You can only SELECT for things that you're grouping by, or aggregates calculated over the group. If you select for `sessions.start_timestamp` in a query that you're GROUPING BY the hours, do you want the first in the hour, the second, or the last? SQL doesn't know that, so it prohibits it. – Evan Carroll Dec 12 '16 at 23:43
Get your query working in SQL first, and then work on getting it in knex – Evan Carroll Dec 12 '16 at 23:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/130444/discussion-between-growler-and-evan-carroll). – user3871 Dec 12 '16 at 23:49

score 0 · Answer 2 · answered Jul 30 '22 at 05:07

The query is built using the Knex with raw plugin for NodeJS.

It will be useful to group by date in the datetime column. I have a LoginDateTime column with values like below,

2022-07-27T01:30:00.000.
2022-07-27T01:450:00.000
2022-07-27T01:50:00.000
2022-07-28T01:50:00.000

let groupBy = Knex.raw(`CAST(LoginDateTime AS DATE)`);
let activeUsers = await Knex('Logs')
            .select(Knex.raw(`CAST(LoginDateTime AS DATE) as date, 
              count(LoginDateTime) as count`))
            .groupBy(groupBy)
            .orderBy(groupBy, 'DESC');

It will produce the following results:

[
  {  "date": "2022-07-27","count": 3},
  {  "date": "2022-07-28","count": 1}
]

group by time or hour in knex

2 Answers2