Jump to content
Eminence

AutoIT SQLIite3 Order by Date

Recommended Posts

Eminence
Posted (edited)

Hello,

I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.

This is my current code:

Local $iCurrentDate = _Now()

Local $sqlHandle = _dbOpen($sDbPath)
Local $aResult, $iRows, $iCols

_SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols)
_ArrayDisplay($aResult)
_dbClose($sDbpath)

What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.

 

*EDIT

The date format in the database is by MM/DD/YYYY HH:MM:SS.

Edited by Eminence

Share this post


Link to post
Share on other sites
TheXman

I think you answered your question with the additional information that you provided.

You need to spend some time understanding the differences between SQLite and other relational databases like MySQL, MS SQL, Oracle, etc.  First of all, SQLite does not have a datatype or storage type of DATE.  Everything in a SQLite database is stored as either null, text, blob (binary), or a number (real or integer).  Second, if you read the sqlite documentation on the datetime function, you will see that "mm/dd/yyyy hh:mm:ss" is not an acceptable time string.

If you do not want to have to go thru all sort of conversion gymnastics, it would be much easier to store your dates in a commonly accepted SQLDATE format (YYYY-MM-DD HH:MM:SS) or numeric format like Julian or Epoch.  As far as SQLite is concerned, your dates are strings and are being sorted as such.  Obviously it cannot sort your dates correctly if the are stored in a MM/DD/YYYY format.

You can start learning more about SQLite datatype and date/time functions by going to the following links:

https://www.sqlite.org/datatype3.html
https://www.sqlite.org/lang_datefunc.html

  • Like 1

Whoever said that there's no such thing as a dumb question, probably asked a lot of DUMB questions.  Dumb questions are ones that you can easily find the answer to on your own.

Share this post


Link to post
Share on other sites
Eminence

Oh my bad. Yep, understood now. Thanks @TheXman :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • TrashBoat
      By TrashBoat
      So Im trying to make a simple 2d game and make some sort of collision detection so why not to make a 2 dimensional array but i have no clue how  to write it in multiple lines
      Global $map[5,5] = [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0] something like this but it doesn't work
    • Zein
      By Zein
      #include "..\Include\Array.au3" #include "..\Include\File.au3" #include "..\Include\AutoItConstants.au3" Local $aRetArray Local $sFilePath = "n.csv" _FileReadToArray($sFilePath, $aRetArray, ",") ; _FileReadToArray($sFilePath, $aRetArray, $FRTA_COUNT, ",") _ArrayDisplay($aRetArray, "Original", Default, 8) The above code shows two versions of _FileReadToArray and both don't work as expected.
      The first one doesn't use the comma as a delimiter. (so I get a single column array)  I tried adding "Default" between $aRetArray and "," then it told me it had an incorrect number of parameters. 

      I looked again at the documentation:
       
      #include <File.au3> _FileReadToArray ( $sFilePath, ByRef $vReturn [, $iFlags = $FRTA_COUNT [, $sDelimiter = ""]] )
      And I with or without the flags params I should be getting a 2D array due to my file being a csv. 
      I then tried a regular flag, $FRTA_COUNT, and it tells me that I'm using a variable $FRTA_COUNT while it's not declared. Tried putting in 1 instead and it told me again, incorrect number of params. 

       
    • nooneclose
      By nooneclose
      I need to perform a subtotal in excel and I would like to automate this process using Autoit if possible like always any and all help will be greatly appreciated. 
      I can not find a good example but the two from Microsoft. Here is one of the two from msdn.microsoft.com/en-us/vba/excel-vba/articles/range-subtotal-method-excel
      I do not really understand how to translate this into AutoIt, but I gave it a try and here is what I have.
      $OpenRange      = "A1:E200" $xlSum          = -4157 $Added_Array[2] = [2, 3] $OpenRange.Subtotal("B1", $xlSum, $Added_Array, True, False, True) I just need to perform a subtotal on a range based on a header called department, and then perform a sum on the results.
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • Eminence
      By Eminence
      Hello,
      Using SQLite, I was trying to select a specific column with having duplicate entries removed by using the DISTINCT function. 
      Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) FROM data_db What the above snippet does is just list only one supervisor but with the total count of unique employee names in the whole database when it should be listing out all supervisor entries from the database and list the total count of employees per each supervisors. If I remove the COUNT function, it does list out all unique entries of supervisor names from the list. Attached is a screenshot of an example database as well. 
      Any help will be much appreciated. Thank you!

×