SQLite Forum

How do I access urls in a sqlite database using timedelta?
Login

How do I access urls in a sqlite database using timedelta?

(1.1) Originally by anonymous with edits by Richard Hipp (drh) on 2020-08-31 12:32:57 from 1.0 [link]

I have an sqlite database that contains a timestamp column and a url column. I'm trying to select the urls using timestamps and timedelta with timedelta set to 1 hour. I have tried to use "past_time" to select them but it's not working for me for some reason. I have also tried "BETWEEN" to isolate the times but it doesn't work either. It's not giving me any errors but just returns an empty list every time.

~~~~~
timenow = datetime.now()
delta = timedelta(minutes=0)
delta2 = timedelta(minutes=5)
prevtime = timenow - delta
prevtime2 = timenow - delta2

past_time = prevtime.strftime('%Y-%m-%d %H:%M:%S')
past_time2 = prevtime2.strftime('%Y-%m-%d %H:%M:%S')

conn = sqlite3.connect('ddother.db')
c = conn.cursor()
c.execute('SELECT adUrl FROM donedeal_listings WHERE timestamp BETWEEN "past_time2" AND "past_time"')

all_urls = c.fetchall()

print(all_urls)

conn.commit()
~~~~~

(2.1) By Ryan Smith (cuz) on 2020-08-31 13:54:11 edited from 2.0 in reply to 1.1 [link]

How do you suppose SQLite would know what "past_time2" and "past_time" are?

To understand what I mean - Try that same code but change the execute line to:  
```
c.execute('SELECT "past_time2", "past_time"')
```
Does that work as expected? (i.e. showing dates in stead of words?)

If not, then try the following:

Not sure what language that is (looks C#-ish but without statement terminators), so I do not know what the string concatenation character or quote-escape characters are for your language, but assuming it's "+" and doubled quotes (''), try this code:
```
c.execute('SELECT adUrl FROM donedeal_listings WHERE timestamp BETWEEN '''+past_time2+''' AND '''+past_time+'''')
```

If that doesn't help, please say what your OS, Language platform and SQLite connectors are.


Good luck,
Ryan

(3) By anonymous on 2020-08-31 14:54:20 in reply to 2.1 [link]

Hi Ryan, thanks for taking the time to reply. I'm using Python and thanks for pointing out c.execute('SELECT "past_time2", "past_time"').
It just gives me [('past_time2', 'past_time')] so my problem is here.
I'll need to approach it from another direction.

(4) By David Raymond (dvdraymond) on 2020-08-31 14:56:18 in reply to 1.1 [link]

What you have there isn't connecting the variable names in Python with the statement at all. So it's going to look for fields in the table with names of "past_time2" and "past_time". And since SQLite isn't strict (by default) about quoting, then if those fields don't exist it'll take them as string literals instead. Which is why it probably runs without an error, but gives an unexpected answer.

You need to bind the two values to the statement.

c.execute("SELECT adUrl FROM donedeal_listings WHERE timestamp BETWEEN ? AND ?;", (past_time2, past_time))

(5) By anonymous on 2020-08-31 18:02:00 in reply to 4

Hi David, thanks for replying. Yes that worked and gave me what I was looking for. Thanks again.