查询时间范围而忽略时间戳记的日期 [英] Query on a time range ignoring the date of timestamps

问题描述

我正在尝试在Rails数据库（Postgres）中查询购买表，我想查询时间范围。

I'm trying to query a purchases table in my rails database (Postgres) and I want to query on time ranges.

例如，我想要了解在所有日期的下午2点到下午3点之间进行了多少次购买。

For example, I'd like to know how many purchases were made between 2 PM and 3 PM across all dates.

其中有一个 created_at 列在此表中，但我也看不到如何在不搜索特定日期的情况下完成此操作。

There is a created_at column in this table but I don't see how to accomplish this without searching for a specific date as well.

我尝试过：

Purchases.where（ created_at BETWEEN？和？，Time.now-1.hour，Time.now）

但这最终只会用这些时间搜索今天的日期。

But this ultimately will just search for today's date with those times.

推荐答案

使用 PostgreSQL的date_part从created_at中仅提取小时部分/ extract function 。

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20

例如，类似这样：

Purchases.where(["EXTRACT(HOUR FROM created_at) BETWEEN ? AND ?", 13, 14])

这篇关于查询时间范围而忽略时间戳记的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！