Postgresql query between date ranges

by

With dates (and times) many things become simpler if you use >= start AND < end.

For example:

SELECT
  user_id
FROM
  user_logs
WHERE
      login_date >= '2014-02-01'
  AND login_date <  '2014-03-01'

In this case you still need to calculate the start date of the month you need, but that should be straight forward in any number of ways.

The end date is also simplified; just add exactly one month. No messing about with 28th, 30th, 31st, etc.

This structure also has the advantage of being able to maintain use of indexes.

Many people may suggest a form such as the following, but they do not use indexes:

WHERE
      DATEPART('year',  login_date) = 2014
  AND DATEPART('month', login_date) = 2

This involves calculating the conditions for every single row in the table (a scan) and not using index to find the range of rows that will match (a range-seek).

More Related Contents:

  1. How do you do date math that ignores the year?
  2. Getting date list in a range in PostgreSQL
  3. PostgreSQL: how to convert from Unix epoch to date?
  4. Check if current date is between two dates Oracle SQL
  5. Check whether string is a date Postgresql
  6. How to compare dates in datetime fields in Postgresql?
  7. Getting results between two dates in PostgreSQL
  8. Query for count of distinct values in a rolling date range
  9. PostgreSQL “Column does not exist” but it actually does
  10. Any downsides of using data type “text” for storing strings?
  11. Query for array elements inside JSON type
  12. Calculating Cumulative Sum in PostgreSQL
  13. Is it possible to make a recursive SQL query?
  14. Group by month and year in MySQL
  15. Change PostgreSQL columns used in views
  16. Way to try multiple SELECTs till a result is available?
  17. How to set auto increment primary key in PostgreSQL?
  18. List columns with indexes in PostgreSQL
  19. PostgreSQL IF statement
  20. Split given string and prepare case statement
  21. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  22. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  23. PostgreSQL wildcard LIKE for any of a list of words
  24. Query that ignore the spaces
  25. I keep getting the error “relation [TABLE] does not exist”
  26. PostgreSQL Where count condition
  27. How to write a constraint concerning a max number of rows in postgresql?
  28. Postgres function returning table not returning data in columns
  29. how to remove time from datetime
  30. Is there a postgres CLOSEST operator?

Leave a Comment