TimeTracker Updated, and how I did date filtering in SQLite/AIR

This post is more than 2 years old.

Sometime ago I began work on a simple AIR tool to help me record my hours. It's been a while since I worked on it, but this weekend I decided to take another look at a nagging issue that I had never resolved. How do I do date filters in SQLite? Specifically, the "Enter Hours" screen should only show the hours you entered for today. (I plan on adding a Reports tab later to let you browse hours and generate reports.)

I worked and worked and worked on this issue before, but figured a fresh look may help. I finally got it working. I used the strftime function, which is really meant to format dates, but I was able to use it in the WHERE clause as well. This is the final code I ended up with:

hourDataStatement.text = 'select hours.description, hours.hours, hours.date, projects.name as project, clients.name as client from hours, projects, clients where hours.projectidfk = projects.id and projects.clientidfk = clients.id ' + 'and strftime("%d",date) = strftime("%d",date("now")) ' + 'and strftime("%m",date) = strftime("%m",date("now")) ' + 'and strftime("%Y",date) = strftime("%Y",date("now")) ' + 'order by date desc';

In case that doesn't make sense, what I've done is added where clauses that do:

where 'format the date to show just day == the day of today' AND 'format the date to show just the month == the month of today' AND 'format the date to show just the year == the year of today'

Not incredibly elegant, but it worked!

During my testing I also ran into another issue. One thing I tell people about Flex development is - it is incredibly easy for the most part. All you have to do is get comfortable with the tag set and have an idea of what you can do in ActionScript. What is difficult is getting used to asynchronous operations. The idea that - I can run some remote function but I have to write a function to handle the result. Maybe it's just me, but time and time again this trips me up.

The problem I ran yesterday involved the start up code for the application. I had deleted my old database so everything was run fresh. I got an error stating that the schema had changed. I think I know why. My old code did this:

  • Open an async connection, when open, run SetUp()
  • SetUp would create new tables if necessary
  • Setup used a Synch connection.
  • When done, continue on the app
  • The rest of the app used the original asynch connection.

So my take on the bug was - the original async connection got "confused" when the db was changed underneath it. I edited my code a bit so as we now do:

  • Open synch connection and make tables.
  • When done, open the new asynch connection.

And this corrected the problem. I've included both the source and the AIR installer for the latest version. Now I'm very happy as I get to play with charts and generating reports!

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Charlie Arehart posted on 3/25/2008 at 6:34 AM

Hey Ray, thanks for sharing this (the time tracking app). I've been looking at various ones (installed and web hosted) for a couple of months, and have been annoyed by most. Yours looks nice and simple.

Of course, there are lots of directions you can go in adding to it, if you will. Any of the alternatives can give you ideas. The ones I've found are listed in my tools/resources page at http://carehart.org/resourc....

The challenge, as always, is between keeping things simple and getting complicated (or never being able to please everyone). Your shown your ability to walk that line, of course. :-) Thanks again.

Comment 2 by Raymond Camden posted on 3/25/2008 at 7:18 AM

Thanks for the kind words. I've found that the apps I appreciate the most are the ones I need myself - and this is one I definitely need.

Kyle Hayes is also definitely to thank. He really improved the UI and was a big help.

Comment 3 by Simon Romanski posted on 3/25/2008 at 7:51 AM

I like this program: http://www.rad3.com/timeloc/

Comment 4 by Charlie Arehart posted on 3/25/2008 at 8:24 AM

Simon, I added that one (and Ray's) to my resource list. (I welcome people sharing with me more resources to list. There's a link to do that on each of the 40+ categories.)