Time slot request ignoring timestamp date

Question

I am trying to query a shopping table in my rails database (Postgres) and I want to query time ranges.

For example, I would like to know how many purchases were made between 14:00 and 15:00 on all dates.

There is a column in this table created_at, but I don’t see how to do it without searching for a specific date.

I tried:

Purchases.where("created_at BETWEEN ? and ?", Time.now - 1.hour, Time.now)

But it will ultimately just look for today's date with those times.

Answer 1

You need to extract only part of the hour from created_at using the Date_part / extract PostgreSQL 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])

Answer 2

time:

Purchases.where("created_at::time >= ?
             AND created_at::time <  ?", Time.now - 1.hour, Time.now)

, .

:

• /. , . BETWEEN ...

• (timestamp timestamp with time zone) :
  Rails PostgreSQL

, , :

CREATE INDEX tbl_created_at_time_idx ON tbl (cast(created_at AS time));

:

• SQL cast() Postgres ::. .

• timestamp [without time zone], IMMUTABLE . timestamp with time zone. , , . AT TIME ZONE, . , UTC:

CREATE INDEX tbl_created_at_time_idx2
ON tbl(cast(created_at AT TIME ZONE 'UTC' AS time));

, , :
, ?