Results 1 to 9 of 9

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

  1. #1
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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

    Qt Code:
    1. QDateTime beginDate = QDateTime::currentDateTime();
    2. beginDate.setTime(QTime(0,0,0,0));
    3. QDateTime endDate = QDateTime::currentDateTime();
    4. endDate.setTime(QTime(23,59,59,999));
    5.  
    6. qDebug() << "beginDate: " << beginDate; //not what I'm expecting to get
    7. qDebug() << "endDate: " << endDate;
    8.  
    9. QSqlQuery selectQuery("SELECT * FROM userlogevents WHERE dateTime BETWEEN ? and ?", m_selectDataBase);
    10. selectQuery.addBindValue(beginDate);
    11. selectQuery.addBindValue(endDate);
    To copy to clipboard, switch view to plain text mode 
    Last edited by anda_skoa; 6th October 2016 at 17:12.

  2. #2
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

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

    //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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  3. #3
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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.

  4. #4
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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

  5. #5
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

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


  6. The following user says thank you to anda_skoa for this useful post:

    jfinn88 (6th October 2016)

  7. #6
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

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

    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:
    Qt Code:
    1. select * from userlogevents where dateTime like date('now','localtime')||"%";
    To copy to clipboard, switch view to plain text mode 

    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. 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:

    Qt Code:
    1. select * from userlogevents where dateTime between date('now','localtime')||" 00:00:00.000" and date('now','localtime')||" 23:59:59.999";
    To copy to clipboard, switch view to plain text mode 
    If you don't have an index on that field, a table scan will occur regardless.
    Last edited by jefftee; 6th October 2016 at 20:24.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!

  8. The following 2 users say thank you to jefftee for this useful post:

    anda_skoa (7th October 2016), jfinn88 (6th October 2016)

  9. #7
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    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
    Qt Code:
    1. CREATE TABLE userlogevents(id INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT NOT NULL, eventMessage TEXT NOT NULL, dateTime TEXT NOT NULL);
    To copy to clipboard, switch view to plain text mode 

    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:
    Qt Code:
    1. QString beginDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 00:00:00");
    2. QString endDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 23:59:59");
    3.  
    4. qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
    5. qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;
    To copy to clipboard, switch view to plain text mode 
    Last edited by jfinn88; 7th October 2016 at 01:08.

  10. #8
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

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

    Quote Originally Posted by jfinn88 View Post
    I may have got confused on this part
    See the explanation of SQLITE data types here, they're essentially useless.

    Quote Originally Posted by jfinn88 View Post
    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.

    Quote Originally Posted by jfinn88 View 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.

    Quote Originally Posted by jfinn88 View 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.

    Quote Originally Posted by jfinn88 View Post
    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:
    Qt Code:
    1. QString beginDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 00:00:00");
    2. QString endDate = QDateTime::currentDateTime().toString("MM-dd-yyyy 23:59:59");
    3.  
    4. qDebug() << "UserEventLog::selectEvent() beginDate: " << beginDate;
    5. qDebug() << "UserEventLog::selectEvent() endDate: " << endDate;
    To copy to clipboard, switch view to plain text mode 
    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...
    Last edited by jefftee; 7th October 2016 at 01:28.
    I write the best type of code possible, code that I want to write, not code that someone tells me to write!

  11. The following user says thank you to jefftee for this useful post:

    jfinn88 (7th October 2016)

  12. #9
    Join Date
    Jun 2016
    Posts
    99
    Thanks
    18
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    Unix/X11

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

    Thanks for the link to the doc

    Quote Originally Posted by jefftee View Post
    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.
    Qt Code:
    1. date('now','localtime')||' 00:00:00.000' and date('now','localtime')|| '23:59:59.999'"
    To copy to clipboard, switch view to plain text mode 

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

Similar Threads

  1. select estimate comboBox,date et time
    By aymenkn in forum Qt Programming
    Replies: 4
    Last Post: 28th March 2014, 08:56
  2. Replies: 5
    Last Post: 19th April 2011, 12:13
  3. Replies: 2
    Last Post: 13th April 2010, 17:50
  4. Replies: 1
    Last Post: 15th April 2009, 10:00
  5. drawing points on canvas after a time period
    By quickNitin in forum Qt Programming
    Replies: 3
    Last Post: 12th May 2006, 15:12

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.