MonetDB: Group by different parts of a timestamp

Question

I have a timestamp column in a monetdb table which I want to occasionally group by hour and occasionally group by day or month. What is the most optimal way of doing this in MonetDB?

In say postgres you could do something like:

select date_trunc('day', order_time), count(*)
    from orders
    group by date_trunc('day', order_time);

Which I appreciate would not use an index, but is there any way of doing this in MonetDB without creating additional date columns holding day, month and year truncated values?

Thanks.

Answer 1

You could use the EXTRACT(DAY FROM order_time) possibly as part of a subquery before grouping.

Answer 2

It might be a little late for answer, but the following should work for truncating to day precision:

SELECT CAST(order_time AS DATE) AS order_date, count(*)
FROM orders
GROUP BY order_date;

It works by casting the timestamp value to type DATE which is a MonetDB built-in type and the cast is pretty fast.

It does not have the flexibility of date_trunc in Postgres, but if you need to go to monthly of yearly precision, you could use the somewhat slower but usable EXTRACT to get the relevant parts of the timestamp and group by them. For monthly grouping, you could do:

SELECT EXTRACT(YEAR FROM order_time) AS y,
       EXTRACT(MONTH FROM order_time) AS m,
       count(*)
FROM orders GROUP BY y, m;

The only disadvantage is that you will have the date split to two columns.