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

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

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

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

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

Examples related to sql

Examples related to postgresql