I am working with a PostgreSQL database in a rails application and facing a challenge in extracting the user records on the basis of only the time component from timestamp
type created_at
column in my users table.
This query works fine:
users = User.where(" DATE_PART('hour', created_at) >= ?
AND DATE_PART('minute', created_at) >= ?
OR DATE_PART('hour', created_at) <= ?
AND DATE_PART('minute', created_at) <= ?",
10, 0, 15, 30).pluck :created_at
But I'm curious if there's a more elegant approach to extract the desired records. Essentially, I aim to query records based solely on time, excluding the date. For instance, I want to retrieve all users created between 10:00
and 12:30
on any day (year, month and day do not matter).
I also tried this:
User.where("EXTRACT(HOUR FROM created_at) BETWEEN ? AND ?", 14, 15)
but it's only dealing with hours not with the minutes.
Desired time could be 10
-12
hours or 10:00
-12:30
.
How can I manage these minutes if I want to extract the records from 10:00
to 12:30
?
In short, I'm seeking method where I can specify the desired time and obtain all records within those time slots.
PostgreSQL has a time
type, that you can cast timestamp
to in order to extract everything from hours down (not excluding seconds and fractions of seconds), in one go. Demo at db<>fiddle:
create table test(created_at timestamp);
--sample timestamps around every hour between now and 4 days from now
insert into test select g+random()*'1h'::interval
from generate_series(now(),now()+'4 days','1h')_(g);
select created_at
from test
where created_at::time between '11:15' and '12:21:10.123';
created_at |
---|
2023-12-08 11:55:58.167576 |
2023-12-09 11:39:04.189425 |
2023-12-10 12:09:11.234004 |
2023-12-11 11:40:42.80833 |
From RoR, you can pass the time fields into PostgreSQL make_time()
. The third field is a double precision
, which allows you to pass seconds with fractions:
User.where("created_at::time BETWEEN make_time(?,?,?)
AND make_time(?,?,?)",
11, 15, 0,
12, 21, 10.123)
You can also pass any timestamp with a matching time, and cast to only use its time
element:
User.where("created_at::time BETWEEN cast(:start_time as time)
AND cast(:end_time as time)",
{start_time: Time.new(2023,12,07,11,15),
end_time: DateTime.new(1981,05,05,15,30)} )
It's possible to further speed this up by adding a functional index on created_at
:
create index on test((created_at::time));