SQLIte3 and Dates

Long time visitor at this site but I think this maybe my first question posted. The information found here has solved every problem I’ve encountered with my, very limited knowledge of, AppleScript until now.


I am building an Applescript Studio application that stores broadband usage stats, by date, in an sqlite3 database. (As an aside I have developed a widget that shows broadband usage stats and am expanding the information being made available to users of the widget - but the widget has nothing to do with the problem).

The stats being stored in the db are: Date, Download, Upload, Free. I create the fields in the database as date, and integer float.

The source data comes from an XML file retrieved, indirectly, from Australia’s largest ISP - called Bigpond.

I am aware that dates in sqlite must be in the format “yyyy-mm-dd hh:mm:ss” although it appears, maybe, that declaring an sqlite field as date one can use a “yyyy-mm-dd” format

The source dates are in the form “04 Feb 2008” which I convert to a string “2008-02-04” and insert this into the date field of the sqlite database … apparently successfully.

I am not using Database Events - everything is done using “do shell script”

The Problem:

Queries against the database by a date or date range are not working - the query either returns nothing or every row. These queries are either in Applescript or using MesaSQLite.

As the date based queries I’ve tried both these:

set tblQuery to "SELECT pkid, date(date), total, download, upload, free from daily_totals  where date >= " & quote & sDate & quote & " and date is <= " & quote & eDate & quote & " ORDER BY date DESC;" & space


set tblQuery to "SELECT pkid, date(date), total, download, upload, free from daily_totals  where date >= " & sDate & " and date is <= "  & eDate  & " ORDER BY date DESC;" & space

The create and and insert routines snippets are:

set newTbl to "create table " & tblName & "(pkid INTEGER PRIMARY KEY AUTOINCREMENT, date DATE, total INTEGER FLOAT, download INTEGER FLOAT, upload INTEGER FLOAT, free INTEGER FLOAT, note TEXT); "

and …

set d1 to "insert into daily_totals (date, total, download, upload, free) values('" & theDate & "'," & thisTotal & "," & thisDownload & "," & thisUpload & "," & thisFree & "); "

and date conversion snippet for the inserted field “theDate”:

set eachWord to every word of theDate as list

set aDay to item 1 of eachWord as text
set aMonth to item 2 of eachWord as text  (somewhere else I add a leading zero if month is less than 10) 
set aYear to item 3 of eachWord as text
set theDate to quote & aYear & "-" & aMonthTxt & "-" & aDay & quote as string

I’ve also tried

set theDate to aYear & "-" & aMonthTxt & "-" & aDay as string

All to no avail. “Where” based queries against the database do not work.

I am suspicious of the format of the date data I am inserting into the database.

Any suggestions or advice?


Am posting a reply to my own question just for the record and for the benefit of others in the future.

Problem fixed. Got the query on dates to work using the following:

set tblQuery to "SELECT pkid, date(myDate), total, download, upload, free FROM daily_totals WHERE date(myDate) >= '" & sDate & "' AND date(myDate) <= '" & eDate & "' ORDER BY myDate ASC;" & space

Two points to make:

  1. The “date(myDate)” conversions in the query
  2. The “rain drops” bounding the date fields, so that a date becomes ‘2008-02-01’

Hope this helps others in the future.