Postgresql: Between with Datetime

PostgreSQL: between with datetime

You expected 1-01-01 ... 1-12-31 ... but how is PostgreSQL supposed to know what you mean by that?

Input string literals are interpreted according to the settings of your current session (which defaults to general settings in postgressql.conf unless overruled). In particular datestyle:

DateStyle (string)

Sets the display format for date and time values, as well as the rules
for interpreting ambiguous date input values. For historical reasons,
this variable contains two independent components: the output format
specification (ISO, Postgres, SQL, or German) and the
input/output specification for year/month/day ordering (DMY, MDY,
or YMD). These can be set separately or together. The keywords
Euro and European are synonyms for DMY; the keywords US,
NonEuro, and NonEuropean are synonyms for MDY. See Section
8.5 for more information. The built-in default is ISO, MDY, but initdb will initialize the configuration file with a setting that
corresponds to the behavior of the chosen lc_time locale.

(While output format is mostly determined by lc_time.)

In your case, the mutilated timestamp literal 1-12-31 23:59:59 is obviously interpreted as:

D-MM-YY h24:mi:ss

While you would have hoped for:

Y-MM-DD h24:mi:ss

3 options

1. Set datestyle so that it interprets literals in the same way as you do. Maybe ISO, YMD?

2. Use to_timestamp() to interpret the string literal in a well defined way - independent of other settings. Much better.

    SELECT to_timestamp('1-12-31 23:59:59', 'Y-MM-DD h24:mi:ss');
   

3. Better yet, use ISO 8601 format (YYYY-MM-DD) for all datetime literals. That is unambiguous and independent from any settings.

    SELECT '2001-12-31 23:59:59'::timestamp;
   

Rewrite query

Your query is faulty to begin with. Handle range queries differently. Like:

SELECT d.given_on 
FROM   documents_document d
WHERE  EXTRACT('month' FROM d.given_on) = 1
AND    d.given_on >= '2001-01-01 0:0'
AND    d.given_on <  '2002-01-01 0:0'
ORDER  BY d.created_on DESC;

Or, simpler yet:

SELECT d.given_on 
FROM   documents_document d
WHERE  d.given_on >= '2001-01-01 0:0'
AND    d.given_on <  '2001-02-01 0:0'
ORDER  BY d.created_on DESC;

Range types in PostgreSQL 9.2 or newer may be of interest.

Postgresql query between date ranges

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).

Querying postgresql from Python for DateTime values between two dates

When working with sql, you should always use your sql library to substitute parameters into the query, instead of using Python's string operators. This avoids the risk of malformed queries or sql injection attacks. See e.g., this page. Right now your code won't run because it directly inserts dStart and dEnd without any quoting, so they are interpreted as mathematical expressions (2016 - 5 - 12 = 1999).

There's also a secondary problem that your query will exclude dateTime values on the end date, because endDate will be treated as having a time value of 00:00:00 when it is compared to dateTime. And if you use to_char() or some other function to extract just the date from the dateTime column to do the comparison, it will prevent your query from using indexes, making it very inefficient.

Here is some revised code that may work for you:

def fn(dStart, dEnd):
    query = """ 
        SELECT "recordId" 
        FROM "Table" 
        WHERE "dateTime" >= %(start)s AND "dateTime" < %(end)s + interval '1 day'
    """
    query_params = {'start': dStart, 'end': dEnd}
    df1 = pd.read_sql_query(query1, con=engine, params=query_params)
    return df1

This code relies on a few assumptions (welcome to the wonderful world of datetime querying!):

1. you will pass dStart and dEnd to fn(), instead of just a single dateTime,
2. the dateTime column is type timestamp with timezone (not text),
3. the timezones in the dateTime column are correct, and
4. the dates given by dStart and dEnd are in the server's timezone or you have used SET TIMEZONE ... with your engine object to select the right time zone to use for this session.

Notes

Different database engines use different placeholders for the parameters, so you will need to check your database driver's documentation to decide what placeholders to use. The code above should work fine for postgresql.

With the code above, dStart and dEnd will be inserted into the query as strings, and postgresql automatically convert them into timestamps when it runs the query. This should work fine for the example dates you gave, but if you need more direct control, you have two options:

1. call fn() with Python date or datetime values for dStart and dEnd, and the code above will insert them into the query as postgresql dates or timestamps; or
2. explicitly convert the dStart and dEnd strings into postgresql dates by replacing %(start)s and %(end)s with something like this: to_date(%(start)s, 'YYYY-MM-DD').

How to compare dates in datetime fields in Postgresql?

@Nicolai is correct about casting and why the condition is false for any data. i guess you prefer the first form because you want to avoid date manipulation on the input string, correct? you don't need to be afraid:

SELECT *
FROM table
WHERE update_date >= '2013-05-03'::date
AND update_date < ('2013-05-03'::date + '1 day'::interval);

Postgresql - Select between specific Time and Date Range

SELECT v1, localminute 
FROM "table" 
WHERE localminute BETWEEN '2013-11-01'::date AND '2014-12-01'::date 
AND (extract('hour' from localminute) >= 22 OR extract('hour' from localminute) < 6);

PostgreSQL BETWEEN operator behaving differently

I assume that created_timestamp is a timestamp (with or without time zone).

The literal you used, 06-11-2019, corresponds to midnight on that date:

SELECT '06-11-2019'::timestamp with time zone;
      timestamptz       
------------------------
 2019-06-11 00:00:00+02
(1 row)

So it is not surprising that the results don't include the data from June 11th.

There are two ways forward:

1. Use the next day and the < operator:

   ... WHERE created_timestamp >= '05-31-2019' AND created_timestamp < '06-12-2019'
   

2. convert the timestamp to a date:

   ... WHERE date(created_timestamp) BETWEEN '05-31-2019' AND '06-11-2019'
   

The second option cannot use an index on created_timestamp (but it can use an index on date(created_timestamp)).

Your query depends on the PostgreSQL setting of DateStyle, so make sure that that setting is always the way you need it.

query to fetch records between two date and time

Combine the two columns into a single timestamp by adding the time to the date:

select *
from some_table
where date_column + time_column 
         between timestamp '2017-06-14 17:30:00' and timestamp '2017-06-19 08:26:00';

Note that this will not use an index on date_column or time_column. You would need to create an index on that expression. Or better: use a single column defined as timestamp instead.

PostgreSQL Get a random datetime/timestamp between two datetime/timestamp

You can do almost everything with the date/time operators:

select timestamp '2014-01-10 20:00:00' +
       random() * (timestamp '2014-01-20 20:00:00' -
                   timestamp '2014-01-10 10:00:00')

---

Related Topics