Using parameter with interval in query

Question

I have this query that by default, gets all the data within the past 60 minutes (from the current time). However, the user also has the ability to select a time (thus the parameter newTime). I want to get all the past data within the past hour of that selected time.

@app.route('/_ajax', methods=['GET', 'POST'])
def userCount():
    try:
        newTime = request.form['time']
    except:
        newTime = datetime.now()
    cur.execute("""SELECT count(DISTINCT(username)) from user_table WHERE tstampz >
                   (%s::date - INTERVAL '60 mins')""", (newTime,))
    count=cur.fetchone()[0]

This works perfectly for the current time, yet when I change the time selection, it displays an aggregate of data from 60 minutes ago all the way back to the selected date.

What I want: If I select August 1st at 2 PM, I want all the data from August 1st 1 PM - 2 PM.

What I'm getting: All the data from August 1st at 2 PM to 60 minutes ago.

How can I make query compatible with the intervals I'm looking for?

Juan Carlos Oropeza · Accepted Answer · 2017-08-04 14:43:41Z

1

Right now you have an open range

  WHERE tstampz > (%s::date - INTERVAL '60 mins')

You need close the upper limit

  WHERE tstampz >  (%s::date - INTERVAL '60 mins')
    AND tstampz <  %s::date

Be sure to validate if you need >= <= to include the border cases

answered Aug 4, 2017 at 14:43

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Sarah Over a year ago

When I try these, it gives me IndexError: tuple out of range. Not sure why

Juan Carlos Oropeza Over a year ago

Im talking just about the query, you need close the range. I'm not familiar with python sintaxis, but maybe you need add two parameter at the end. something like , (newTime, newTime) because now you have another parameter?

Juan Carlos Oropeza Over a year ago

other things you should try are A) Check what value you have in newTime B) That index error is very weird Test your query with the time hard code WHERE tstampz > 2017-08-01 12:00:00 AND tstampz < 2017-08-01 13:00:00

Sarah Over a year ago

I've been able to print out any values for newTime, and they are correct and in the correct format. Also, adding the second parameter as you suggested fixed my index error. However, it's not displaying the right data and it's no longer displaying the correct data for the current time anymore

Juan Carlos Oropeza Over a year ago

Can you test the query direct in postgresql first? Can you write what is the exact range you are looking for? For what I understand on your question the range should solve the problem. Unless you are missing the border cases.

|

Clodoaldo Neto · Accepted Answer · 2017-08-04 15:29:50Z

0

You need a closed interval and do not cast to date. If necessary cast to timestamp or timestamptz depending on tstampz data type:

cur.execute("""
    SELECT count(DISTINCT(username)) 
    from user_table 
    WHERE 
        tstampz > %s::timestamptz - INTERVAL '60 mins'
        and
        tstampz < %s::timestamptz
""", (newTime, newTime))

answered Aug 4, 2017 at 15:29

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Using parameter with interval in query

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related