issue with sqlite select qry by date & time for 24 hour period

[jfinn88]

When I out put begin and end dates there not what I expect I just want the begin date variable to get current date with time set to zero (beginning of day)
I want the end date variable to get the current date with the time set to midnight basically (end of day)

but its not quit working



QDateTime beginDate = QDateTime::currentDateTime();
beginDate.setTime(QTime(0,0,0,0));
QDateTime endDate = QDateTime::currentDateTime();
endDate.setTime(QTime(23,59,59,999));

qDebug() << "beginDate: " << beginDate; //not what I'm expecting to get
qDebug() << "endDate: " << endDate;

QSqlQuery selectQuery("SELECT * FROM userlogevents WHERE dateTime BETWEEN ? and ?", m_selectDataBase);
selectQuery.addBindValue(beginDate);
selectQuery.addBindValue(endDate);

[d_stranz]

//not what I'm expecting to get

Would have been nice if you had told us what you *did* get instead of letting us guess.

[Lesiok]

1. SQLite does not have a storage class set aside for storing dates and/or times. I do not think this query to work.
2. At midnigth it is possible that two calls of QDateTime::currentDateTime gives You different dates.

[jfinn88]

When I check whats gets stored in the variables I get this out put:

beginDate: QDateTime(2016-10-06 00:00:00.000 MDT Qt::TimeSpec(LocalTime))
endDate: QDateTime(2016-10-06 23:59:59.999 MDT Qt::TimeSpec(LocalTime))


This what I want stored in them so the sql script will exe fine
2016-10-06 00:00:00.000
2016-10-06 23:59:59.999

[anda_skoa]

QDateTime::toString()?

Cheers,
_

[jefftee]

Since you effectively want to ignore the time component of QDateTime, you should use QDate::currentDate().toString("yyyy-MM-dd ") (note space at end of format) and append the time specs you wish for your query parameters.

You could alternatively solve using SQLITE by itself if desired:


select * from userlogevents where dateTime like date('now','localtime')||"%";


Edit: I should mention that I have no idea what your database schema is, whether you have indexed your tables, etc. I also assume you've stored your date/time stamps in the format that SQLITE expects date/time values as can be seen here (https://www.sqlite.org/lang_datefunc.html). If you have stored the date/time values in any other format, SQLITE won't be able to make comparisons correctly. Note this is completely unrelated to the data type you specified when creating the table. The example above will result in a full table scan, which may or may not be acceptable. If you have an index built for dateTime, it would be more efficient to change to the following:



select * from userlogevents where dateTime between date('now','localtime')||" 00:00:00.000" and date('now','localtime')||" 23:59:59.999";

If you don't have an index on that field, a table scan will occur regardless.

[jfinn88]

I dont have an index on that filed...

I may have got confused on this part


Note this is completely unrelated to the data type you specified when creating the table



CREATE TABLE userlogevents(id INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT NOT NULL, eventMessage TEXT NOT NULL, dateTime TEXT NOT NULL);


My schema is very simple I only have four columns (id, userName, eventMsg, dateTime) the last three fields are text fields ( I wasn’t sure about setting the data type to the dateTime field so I set it to text, above is my table creation sql script )

I have looked into sqlite date() time() functions using the sqlite doc like you mentioned. I have also read else where that you may have issue using other date formats other than YYYY-MM-DD when comparing dates... however I thought maybe there was work around using qt libraries and c++

I believe I do all my comparison in c++ so not sure if that would make a difference

so it sounds like I'm stuck with European dateTime format ? I don't think I will be able to use MM-DD-YYYY format then or use dates with times...

I'm having an issue with an archive fucntion that I just posted that is most likely related to this same issue idk though...

this is what I did to get that part to work earlier, I wanted to keep the variable data type to dateTime but was forced to change them to QStrings and it seem to work okay for selecting data from database however I have run into other issue since then:


QString beginDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 00:00:00");
QString endDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 23:59:59");

qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;

[jefftee]

I may have got confused on this part
See the explanation of SQLITE data types here (https://www.sqlite.org/datatype3.html), they're essentially useless.


I have looked into sqlite date() time() functions using the sqlite doc like you mentioned. I have also read else where that you may have issue using other date formats other than YYY-MM-DD when comparing dates... however I thought maybe there was work around using qt libraries and c++
Not sure exactly where else you read that, but it's exactly what I said in my prior post.


I believe I do all my comparison in c++ so not sure if that would make a difference
Your original example shown the comparison was done in SQL using the BETWEEN operator. If your example has changed and you haven't re-posted your code, you might want to consider that or else you leave us all dazed and confused when you make statements that aren't supported by the example code you post.


so it sounds like I'm stuck with European dateTime format ? I don't think I will be able to use MM-DD-YYYY format then or use dates with times...
You're stuck with the format required by the database engine you chose, which in this case is SQLITE. Make it easy on yourself and store the date/time data in one of the supported (and documented) formats. You can read the date/time data from the database and show in any format you desire, but the key is you need to store date/time data in a format the database will recognized it as date/time data.


I'm having an issue with an archive fucntion that I just posted that is most likely related to this same issue idk though...

this is what I did to get that part to work earlier, I wanted to keep the variable data type to dateTime but was forced to change them to QStrings and it seem to work okay for selecting data from database however I have run into other issue since then:


QString beginDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 00:00:00");
QString endDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 23:59:59");

qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;

I can't figure out what your new problem is, but if I had to guess, it's because you haven't stored data in one of the documented date/time formats and so SQLITE is doing pure lexical comparisons of what you want to be treated as date/time data. i.e. in your desired format, you want 10-06-2016 > 10-07-2015. Since it's not stored in a supported SQLITE date/time format, SQLITE winds up doing a comparison based on lexical order, which means that 10-06-2016 < 10-07-2015, clearly not what you expect.

The supported/recommended date/time formats like 2016-10-06 vs 2015-10-07 is 1) recognized as a date/time value and 2) lexical order comparison works as you would expect. This is *not* a coincidence.

Edit: Now I see you have started a new post with the same topic... sigh...

[jfinn88]

Thanks for the link to the doc



Not sure exactly where else you read that, but it's exactly what I said in my prior post.


I know it was in your prior post but its also posted on stackOver flow, tutorialspoint and other forum help sites but some of the explanations where confusing. I appreciate you bring this up as it is an important feature to know when dealing with dates.



Your original example shown the comparison was done in SQL using the BETWEEN operator. If your example has changed and you haven't re-posted your code, you might want to consider that or else you leave us all dazed and confused when you make statements that aren't supported by the example code you post.


I wasn't fully understanding the BETWEEN operator sorry for the confusion your right



You're stuck with the format required by the database engine you chose, which in this case is SQLITE. Make it easy on yourself and store the date/time data in one of the supported (and documented) formats. You can read the date/time data from the database and show in any format you desire, but the key is you need to store date/time data in a format the database will recognized it as date/time data.


Thank you for explaining this!



I can't figure out what your new problem is, but if I had to guess, it's because you haven't stored data in one of the documented date/time formats and so SQLITE is doing pure lexical comparisons of what you want to be treated as date/time data. i.e. in your desired format, you want 10-06-2016 > 10-07-2015. Since it's not stored in a supported SQLITE date/time format, SQLITE winds up doing a comparison based on lexical order, which means that 10-06-2016 < 10-07-2015, clearly not what you expect.

The supported/recommended date/time formats like 2016-10-06 vs 2015-10-07 is 1) recognized as a date/time value and 2) lexical order comparison works as you would expect. This is *not* a coincidence.

Edit: Now I see you have started a new post with the same topic... sigh...

I was messing several things up mainly formatting with your help though I think I got my issues cleared up, sorry for the double post again thanks Jeff

update: I will try you sqlite sqcript suggestion for select date range with time e.g.

date('now','localtime')||' 00:00:00.000' and date('now','localtime')|| '23:59:59.999'"

update: above sqlite script executes fine I would have never been able to figure out that syntax thanks!