groupBy datetime

[Locke]

Feature request: I want to count items grouped by a DATETIME field. To group on the day I am doing this in sqlite3:

SELECT COUNT(id) AS sum, strftime("%Y-%m-%d", date) AS year_month_date FROM myTable GROUP BY strftime("%Y-%m-%d", date);

[dresende]

Ignore the comment on the other side, I didn't see the ticket here.

[dresende]

You can do this already, it's just not that straightforward.

var query = require("sql-query");
var sql   = (new query.Query()).select();

sql.from("myTable")
   .count('id', 'sum')
   .fun('strftime', [ '%Y-%m-%d', 'date' ], 'year_month_date')
.groupBy('year_month_date');

console.log(sql.build());
// SELECT COUNT(`id`) AS `sum`, STRFTIME(`%Y-%m-%d`, `date`) AS `year_month_date` FROM `myTable` GROUP BY `year_month_date`

[dresende]

Wait, there's a problem in that query... the string format is escaped as an identifier, I'm going to try and fix this.

[dresende]

Every string passed to functions is supposed to be an identifier. What do you think about creating a special object? Example:

var query = require("sql-query");
var sql   = (new query.Query()).select();

sql.from("myTable")
   .count('id', 'sum')
   .fun('strftime', [ query.Text('%Y-%m-%d'), 'date' ], 'year_month_date')
.groupBy('year_month_date');

console.log(sql.build());
// SELECT COUNT(`id`) AS `sum`, STRFTIME('%Y-%m-%d', `date`) AS `year_month_date` FROM `myTable` GROUP BY `year_month_date`

[Locke]

I think this could be a good way, but I leave that to you as I want to use this functionallity in node-orm2 (see referenced ticket above).