I want fetch time datas without specify minutes . So with one query, can i fetch these two times time: ~T[21:30:00] andtime: ~T[21:15:00] ?
start_time = ~T[21:00:00]
end_time = ~T[22:00:00]
MyApp.Repo.all(from f in Foo,
where: f.time >= ^start_time and f.time < ^end_time)
You can use Time.add/3 to get from your start time to the end time.
3 Likes
Thanks. What do you think about effiency of that code? Or should i splite time column to two different column :hour and :minute ?
The efficiency of that query depends on your database, not on your code. If you are so concerned, add a DB index to the column holding the timestamps.
Never do premature optimization.
1 Like
I’m not really sure how efficient it is. But I don’t really see it being bad. You could also try using a fragment to search.
MyApp.Repo.all(from f in Foo,
where: fragment("extract(hour from ?)", f.time) == ^"21")
Keep in mind I haven’t tested the above, but it should work, or something close to it anyways. Take a look here for functions to process dates and times within postgres.
1 Like
I tend to favor @Ankhers first approach as opposed to extracting the hour out in the query. That’s simply because the database must now run that operation on every row. If you have an index on the time column, you won’t be able to use it, unless the index is for the specific operation you’re performing.
In short, favor doing the work once, rather than many times.
3 Likes
I don’t think it will make much difference. The only benefit I see is that it may make the work you do before the query simpler.
1 Like
The second approach could work fine if you had an expression index for it. But it would have to be a separate index from an index directly on the time column. So, you could end up with two potentially.
All of this is completely dependent on the situation though. If you have some other column you also always query on, it may reduce the result set, such that either solution is perfectly fine. I tend to avoid most indexes until it’s obvious they’re necessary. Before, I would try to pre-empt what indexes would be useful and later find most of them are unused because another one gave better performance, so the query planner always picked it. Having unused indexes affects write performance without any gain. Just watch your slow query log and you’ll be able to see what needs optimizing.
3 Likes
I actually had date:string, hour:integer, minute:integer columns. I started refactor my app to date:date , time:time columns. After comments i decided one date_time:naive_datetime and index. I dont know how postgresql works behind scene. My story is fetching booking slots depends on court_id and date. New problem is only court_id index works well ? or both court_id and date indexes? What do you think about having 2 indexes in same table ?
If you’re going to always query two columns you should use a compound index, not one for each field. In most circumstances, a database can only use one index at a time.
1 Like
Although, you might be fine with just an index on court_id.
