select records from postgres where timestamp is in certain range

55
votes

I have arrival column of type timestamp in table reservations ( I'm using postgres ). How would I select all dates within this year for example?

I know I could do something like this:

select * FROM reservations WHERE extract(year from arrival) = 2012;

But I've ran analyze and it looks like it require a sequence scan. Is there a better option?

P.S. 1 Hmm. both ways seem to require seq. scan. But the one by wildplasser produces results faster - why?

cmm=# EXPLAIN ANALYZE select * FROM reservations WHERE extract(year from arrival) = 2010;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on vrreservations  (cost=0.00..165.78 rows=14 width=4960) (actual time=0.213..4.509 rows=49 loops=1)
   Filter: (date_part('year'::text, arrival) = 2010::double precision)
 Total runtime: 5.615 ms
(3 rows)

cmm=# EXPLAIN ANALYZE SELECT * from reservations WHERE arrival > '2010-01-01 00:00:00' AND arrival < '2011-01-01 00:00:00';
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on reservations  (cost=0.00..165.78 rows=51 width=4960) (actual time=0.126..2.491 rows=49 loops=1)
   Filter: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))
 Total runtime: 3.144 ms
(3 rows)

** P.S. 2 - After I have created index on arrival column second way got even faster - since it looks like query uses index. Mkey - I guess I'll stik with this one. **

                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on reservations  (cost=4.77..101.27 rows=51 width=4960) (actual time=0.359..0.791 rows=49 loops=1)
   Recheck Cond: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))
   ->  Bitmap Index Scan on arrival_idx  (cost=0.00..4.76 rows=51 width=0) (actual time=0.177..0.177 rows=49 loops=1)
         Index Cond: ((arrival > '2010-01-01 00:00:00'::timestamp without time zone) AND (arrival < '2011-01-01 00:00:00'::timestamp without time zone))
 Total runtime: 1.265 ms
3
sqlpostgresql
5.615 ms, is that too slow? 3.144 ms is better, but no one will see the difference of 2 milliseconds. EXTRACT() is a function and looks to be slower than two operator comparisons. - Frank Heikens
@Frank Heikens Right - that's because there is only about 5000 rows in the table so far - there''ll be more. - zzz
BTW: your queryplans show an estimated rowsize of 4960, which is huge. - wildplasser
@wildplasser what does that mean? rowsize of 4960? - zzz
It means you have very large rows (many columns and/or large columns). This could indicate a suboptimal data model. - wildplasser

3 Answers

78
votes
SELECT * 
FROM reservations 
WHERE arrival >= '2012-01-01'
AND arrival < '2013-01-01'
   ;

BTW if the distribution of values indicates that an index scan will not be the worth (for example if all the values are in 2012), the optimiser could still choose a full table scan. YMMV. Explain is your friend.

7
votes

Another option to make PostgreSQL use an index for your original query, is to create an index on the expression you are using:

create index arrival_year on reservations ( extract(year from arrival) );

That will open PostgreSQL with the possibility to use an index for

select * 
FROM reservations 
WHERE extract(year from arrival) = 2012;

Note that the expression in the index must be exactly the same expression as used in the where clause to make this work.

2
votes

Search till the seconds for the timestamp column in postgress

select * from "TableName" e
    where timestamp >= '2020-08-08T13:00:00' and timestamp < '2020-08-08T17:00:00';