pysiede2 date and time manuplilation from sqlite3 database
-
hello there i have been stuck with an issue over a month now , i am trying to retrieve data which was added on the current day from sqite3 database, if i try to use strftime('%d', saledate) to retrieve/query data of the current day from the saledate column it does not work as expected```
self.conn = sqlite3.connect("database.db")
self.c = self.conn.cursor()
self.c.execute("CREATE TABLE IF NOT EXISTS sales(name TEXT, KSH INTEGER, saledate TIMESTAMP)")d_s = self.c.execute("SELECT SUM(KSH) FROM sales GROUP BY strftime('%d', saledate), strftime('%d', saledate)").fetchone()
d_s = float(''.join(map(str, d_s)))
self.ui.label_28.setText(str(d_s))
self.ui.label_28.setFont(QFont("Times", 20))
self.ui.label_28.setStyleSheet("QLabel { background-color: rgb(44, 44, 44);color : white; }")
self.connection.close()thankyou -
@erico said in pysiede2 date and time manuplilation from sqlite3 database:
if i try the method below it doesn't work
This is no error description at all...
-
Hi and welcome to devnet,
What do you mean by does not work as expected ?
What do you have in your database ?
Are you sure you are starting with a non empty database ?
Why are you grouping twice with the same date ?
Why do you need strftime in the first place ?By the way, you are using the SQLite Python library, you will likely have better chance to get an answer on a Python forum.
-
hello there i have been stuck with an issue over a month now , i am trying to retrieve data which was added on the current day from sqite3 database, if i try to use strftime('%d', saledate) to retrieve/query data of the current day from the saledate column it does not work as expected```
self.conn = sqlite3.connect("database.db")
self.c = self.conn.cursor()
self.c.execute("CREATE TABLE IF NOT EXISTS sales(name TEXT, KSH INTEGER, saledate TIMESTAMP)")d_s = self.c.execute("SELECT SUM(KSH) FROM sales GROUP BY strftime('%d', saledate), strftime('%d', saledate)").fetchone()
d_s = float(''.join(map(str, d_s)))
self.ui.label_28.setText(str(d_s))
self.ui.label_28.setFont(QFont("Times", 20))
self.ui.label_28.setStyleSheet("QLabel { background-color: rgb(44, 44, 44);color : white; }")
self.connection.close()thankyou@erico
In addition to the preceding comments.I am not a SQLite user, so I may be mistaken about the following, it;s just for you to check. But even if your query "works"
i am trying to retrieve data which was added on the current day
-
Does your table only contain dates in the current month? Else I don't see how your code won't amalgamate rows with the same day but on different months?
-
fetchone(): Why do you thing this would return something on "the current day" (I take that to mean "today", maybe that's not what you mean)?
You might want to play with your proposed query in one of the available "SQLite Workbench"-type applications, so that you can be sure it behaves as you intend before involving any Qt issues.
-
