select records from postgres where timestamp is in certain range

Question

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  gt   2010-01-01 00 00 00  AND arrival  lt   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  gt   2010-01-01 00 00 00   timestamp without time zone  AND  arrival  lt   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  gt   2010-01-01 00 00 00   timestamp without time zone  AND  arrival  lt   2011-01-01 00 00 00   timestamp without time zone      - gt   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  gt   2010-01-01 00 00 00   timestamp without time zone  AND  arrival  lt   2011-01-01 00 00 00   timestamp without time zone    Total runtime  1 265 ms

User · Answer

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.

User · Answer

SELECT    FROM reservations  WHERE arrival  gt    2012-01-01  AND arrival  lt   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

User · Answer

Search till the seconds for the timestamp column in postgress select   from  quot TableName quot  e     where timestamp  gt    2020-08-08T13 00 00  and timestamp  lt   2020-08-08T17 00 00

[sql] select records from postgres where timestamp is in certain range

Examples related to sql

Examples related to postgresql