PostgreSQL - GROUP BY时间戳值？ [英] PostgreSQL - GROUP BY timestamp values?

问题描述

我有一张储存有采购订单的表格。每行具有指示订单何时被放置的时间戳。我想要创建一个报告，指出每天，每月或每年的购买次数。我想我会做一个简单的SELECT COUNT（xxx）从tbl_orders GROUP BY tbl_orders.purchase_time并获取值，但结果是我不能GROUP BY一个时间戳列。

有没有另一种方法来完成这个？我想要一个灵活的解决方案，所以我可以使用任何时间框架我需要（每小时，每月，每周等）感谢您的任何建议，你可以给！

按时间戳列分组对我来说很好，但要记住，即使1微秒的差异也会阻止两行分组。

要按较大的时间段分组，请按时间戳列上的表达式分组，返回适当截断的值。 date_trunc 在这里很有用，因为 to_char 。

I've got a table with purchase orders stored in it. Each row has a timestamp indicating when the order was placed. I'd like to be able to create a report indicating the number of purchases each day, month, or year. I figured I would do a simple SELECT COUNT(xxx) FROM tbl_orders GROUP BY tbl_orders.purchase_time and get the value, but it turns out I can't GROUP BY a timestamp column.

Is there another way to accomplish this? I'd ideally like a flexible solution so I could use whatever timeframe I needed (hourly, monthly, weekly, etc.) Thanks for any suggestions you can give!

解决方案

Grouping by a timestamp column works fine for me here, keeping in mind that even a 1-microsecond difference will prevent two rows from being grouped together.

To group by larger time periods, group by an expression on the timestamp column that returns an appropriately truncated value. date_trunc can be useful here, as can to_char.

这篇关于PostgreSQL - GROUP BY时间戳值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！