Postgresql query between date ranges

Postgresql query between date ranges

Asked on December 24, 2018 in PostgreSQL.
Add Comment


  • 3 Answer(s)

    Try to use >= start AND < end.

    Here is an example:

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

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

    Here, the end date is also simplified; add one month. No messing about with 28th, 30th, 31st, and so on.

    The advantage is , it is able to maintain use of indexes.

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

    The code involves calculate the conditions for every ow in the table and not using index to find the range of match rows.

    Answered on December 24, 2018.
    Add Comment

    The Range Types are supported in postgre sql. So we can use this code,and this method is better than string comparison.

    SELECT user_id
    FROM user_logs
    WHERE '[2014-02-01, 2014-03-01]'::daterange @> login_date
    
    Answered on December 24, 2018.
    Add Comment

    One can use this code for postgre sql version 8.1 or above:

    SELECT user_id
    FROM user_logs
    WHERE login_date BETWEEN SYMMETRIC '2014-02-01' AND '2014-02-28'
    

        Actually, BETWEEN SYMMETRIC and BETWEEN are same, except there is no need that the argument to the left of AND be less than or equal to the argument on the right. Otherwise, the two arguments are automatically swapped. so the nonempty range is always applied.

    Answered on December 24, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.