0

I have saved date entries in a sqlite database table. The date fields were converted to a format taken from qdatewidget like this:

due_date = self.dateEdit.date().toString(self.dateEdit_3.displayFormat())

which makes it a string like 10/14/2021, but saves it as a date, which is fine.

Now i would like to read this entries back, but sqlite wont give anything back when i try this:

day = (self.dateEdit_2.date().toString(self.dateEdit_3.displayFormat()))

because day then is also a string 10/14/2021, but the entries in sqlite are date. How could i solve this?

eyllanesc
  • 235,170
  • 19
  • 170
  • 241
accpert.com
  • 109
  • 11
  • What do you mean by "saves it as a date"? There isn't a specific datatype for dates in sqlite. You seem to be making some kind of distinction between "string" and "date", but it's not at all clear what you mean by that. – ekhumoro Oct 14 '21 at 17:41
  • I have set the type of the database table field to date, so the data is stored as date type. – accpert.com Oct 14 '21 at 18:27
  • In sqlite, DATE and DATETIME are the same as NUMERIC. There is no specific date type. If you set a value like `10/14/2021`, sqlite will store it as TEXT, because it can't be interpreted as an INTEGER or REAL. In sqlite, you need to use the [Date And Time Functions](https://www.sqlite.org/lang_datefunc.html) to handle such values correctly. – ekhumoro Oct 14 '21 at 18:53
  • If you're using the python sqlite3 module, you can [configure it to read/write python datetime objects](https://stackoverflow.com/q/1829872/984421). Also, pyqt has [convenience methods](https://stackoverflow.com/q/22451283/984421) for converting `QDate/QDateTime` objects to python datetime objects. – ekhumoro Oct 14 '21 at 18:58
  • if this would be true I wouldn't have a problem because I try to read the values with a string type. like where date ="14/10/2021". it fails because the table field is a date type – accpert.com Oct 14 '21 at 19:00
  • But that's simply because `"14/10/2021" != "10/14/2021"`. – ekhumoro Oct 14 '21 at 19:02
  • as you can see in the second peace of coding I take the date from a qdate object, namely a qdateedit. I only get a string type value out of it, that is the problem cause sqlite has a date type field – accpert.com Oct 14 '21 at 19:03
  • in sqlite it is 14/10/2021 and I am reading with 14/10/2021. it fails because of the different types – accpert.com Oct 14 '21 at 19:05
  • From the docs: [SQLite does not have a storage class set aside for storing dates and/or times](https://www.sqlite.org/datatype3.html#date_and_time_datatype). So you can only store dates as TEXT, INTEGER or REAL. – ekhumoro Oct 14 '21 at 19:17
  • ok let's say it stores a text. how would you ever find out a date greater than 14/10 /2021? – accpert.com Oct 14 '21 at 19:23
  • You would need to store the dates in a *sortable* format - i.e. as numeric values (timestamps), or `YYYY-MM-DD` if using strings. If you use a format like `DD/MM/YYYY`, "14/10/2021" would evaluate greater than "01/11/2021", because the comparison is done character by character, left to right. – ekhumoro Oct 14 '21 at 19:32
  • now we are running in circles. a string is what I already have and it doesn't work. – accpert.com Oct 15 '21 at 06:44
  • meahnwhile i found out that this select statement data = helper.select(f"select date(DateEdit) from DateEdit where DateEdit >= (select date('now'))") is getting entries entries from today on. Here one can see that the select date('now') is a date type field and the entries in sqlite are also stored as datetype date. – accpert.com Oct 15 '21 at 09:54
  • Let's see: `SELECT date('now')` -> `2021-10-15`; `SELECT typeof(date('now'))` -> `text`. So it's just a *string value*, exactly as the SQLite docs say. Now let's try: `SELECT date("14/10/2021")` -> `NULL`. This happens because the `date()` function requires strings in the format `YYYY-MM-DD`. So: `SELECT date("2021-10-14")` -> `2021-10-14`; and of course, `SELECT typeof(date("2021-10-14"))` -> `text`. So, in all cases, you're just comparing *strings*, and they have to be in the format `YYYY-MM-DD` to get meaningful results. – ekhumoro Oct 15 '21 at 19:31
  • then i am in a illusion beliving that due_date = self.dateEdit.date().toPyDate() converts it to a date. In the debugger i can see a date format for due_date after that. Secondly, if i open the table with sqlitestudio and look at the field types i see Date, Datetime, Time, etc. – accpert.com Oct 16 '21 at 08:33
  • As I already explained in my third comment above, PyQt provides convenience methods for converting `QDate/QDateTime` objects to [python datetime objects](https://docs.python.org/3/library/datetime.html). So no mystery there. And as I also explained in my second comment, in SQLite, `DATE` and `DATETIME` are just [typename aliases](https://www.sqlite.org/datatype3.html#affinity_name_examples) for `NUMERIC` - so again, no mystery. – ekhumoro Oct 16 '21 at 10:56
  • The problem here is that you are trying to compare string values with different formats, i.e. `"14/10/2021" != "2021-10-14"`. The SQLite date and time functions *cannot parse arbitrary date formats*. You MUST use one of the [supported formats](https://www.sqlite.org/lang_datefunc.html#time_values). The solution to your problem is to convert all the dates in your db to the format `YYYY-MM-DD`. Then if you want to display the values in a different format, you can do e.g. `SELECT strftime('%d/%m/%Y', '2012-10-14')` -> `14/10/2021`. – ekhumoro Oct 16 '21 at 11:08
  • I is a string yes. I found out when i save the entries like date_to_save = self.dateEdit.date().toPyDate() and read them back like date_to_read_from = "'" + str(self.dateEdit.date().toPyDate()) + "'" date_to_read_to = "'" + str(self.dateEdit_2.date().toPyDate()) + "'" data = helper.select(f"select date(DateEdit) from DateEdit where DateEdit between {date_to_read_from} and {date_to_read_to}") it works. The dates must be in ' quotes. – accpert.com Oct 17 '21 at 08:36
  • It's completely unnecessary to manually add quotes, and [generally bad practice](https://docs.python.org/3/library/sqlite3.html#sqlite3-placeholders) to construct select statements using string operations. You should always try to use parameter substitution. What is `helper.select()`? If it's a third-party sqlite package, it should automatically handle the substitutions for you. If it doesn't, it's not secure, and you shouldn't use it. – ekhumoro Oct 17 '21 at 12:19
  • Also note that `str(self.dateEdit.date().toPyDate())` will return a string like this: `2021-10-17`. Have you made sure that all the values in your `DateEdit` column are in that exact same format? Because if they're not, your select statements cannot produce reliable results. – ekhumoro Oct 17 '21 at 12:20
  • helper.select is just a def i am using: def select(self, query): Select * c = self.cursor c.execute(query) return c.fetchall(). I changed the variable to : date_to_read_from = self.dateEdit.date().toPyDate() and the selection to DateEdit between '{date_to_read_from}'. the toPyDate() is always saving in format 2021-10-17 no matter what system language i choose. I tryed with us and german. both works fine. – accpert.com Oct 17 '21 at 15:23
  • Change it to: `def select(self, query, *params): return self.cursor.execute(query, params).fetchall()`. And note that the sqlite3 module knows how to convert python datetime objects. So using the above function, your query reduces to: `data = helper.select('SELECT DateEdit FROM DateEdit WHERE DateEdit BETWEEN ? AND ?', self.dateEdit.date().toPyDate(), self.dateEdit_2.date().toPyDate())`. (NB: there's no point in doing `date(DateEdit)`, since that just passes the value through unchanged). – ekhumoro Oct 17 '21 at 19:10
  • ok thanks alot, i learned so much. – accpert.com Oct 18 '21 at 14:04

0 Answers0