CODEWITHSUNDEEP
pythonpython-2.7sqlite
Adam12344
Adam12344

Reputation: 1053

Python sqlite comparing dates stored as text in query

I am using Python and have built a table in sqlite. I didn't realize Python has a timestamp field type for sqlite, so I stored it as text.

Below is my code and the result.. the only dates shown in the tables are for 2019-06-18 but the query returns for 2019-06-19... no idea why

import sqlite3
# Connecting to the database file
sqlite_file = 'insta.sqlite'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

# Get all rows
c.execute("SELECT * FROM daily_counts")
count_row = c.fetchall()
for row in count_row:
    print(row)

# Get Daily Count
c.execute('SELECT count FROM daily_counts where current_date = ? and client_id = ? and metric = ?',
    ('2019-06-18', 0, 'C'))
count_row = c.fetchall()
print(count_row)

# Get Daily Count
c.execute('SELECT count FROM daily_counts where current_date = ? and client_id = ? and metric = ?',
    ('2019-06-19', 0, 'C'))
count_row = c.fetchall()
print(count_row)

# Get Daily Count
c.execute('SELECT count FROM daily_counts where current_date = ? and client_id = ? and metric = ?',
    ('2019-06-20', 0, 'C'))
count_row = c.fetchall()
print(count_row)

Results: (note that when the condition is the 19th it returns the results for the 18th, and the 18th returns no results..)

(0, u'2019-06-18', u'A', 2)
(0, u'2019-06-18', u'B', 6)
(1, u'2019-06-18', u'C', 180)
(1, u'2019-06-18', u'D', 258)
(1, u'2019-06-18', u'E', 111)
(0, u'2019-06-18', u'C', 180)
(1, u'2019-06-18', u'F', 3)

[]

[(180,)]

[]

I assume I should be using the timestamp field type instead of text? But I would still expect this to work

Upvotes: 0

Views: 68

Answers (1)

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

current_date is a sqlite reserved word.

Add double-quotes or backticks to the column name (eg "current_date") in the sql strings and you should get the expected result.

Upvotes: 1

Related Questions