Two SQLite tool recommendations
In regards to my post yesterday on AIR and SQLite I thought I'd share two tools that can help developers.
I've been using a cool AIR based SQLite tool from Christophe Coenraets. You can find it here. It works rather nicely and I like that it has a 'history' feature since I always have trouble finding the exact location AIR stores my DB file. (I typically trace the nativepath of my File object when I forget.)
Then a commenter named Gareth pointed out that you can find a Firefox plugin as well. (About the only thing you can't do in a Firefox plugin is peace in the Middle East.) I installed the SQLite Manager today and it runs pretty well.
I'm not sure which tool I'll give preference too, and I'm sure there are others as well.
And lastly, if any AIR/SQLite experts out there want to help me with my date problem, let me know.
Comments
Not an expert Ray but would like to hear about your date problem.
# Posted By Anuj Gakhar
| 1/10/08 9:33 AM
I'm getting confusing feedback on the AIR side if AIR+SQLite supports true date columns. It seems like it does. And I know the tools I use to look at my db say my column is a date.
But I'm trying to do a simple date comparison. Ie, get * from hours where date > today and nothing I try seems to work.
But I'm trying to do a simple date comparison. Ie, get * from hours where date > today and nothing I try seems to work.
# Posted By Raymond Camden
| 1/10/08 9:35 AM
hmm, Is date the name of your column by any chance? sounds like a reserved word to me..
# Posted By Anuj Gakhar
| 1/10/08 9:44 AM
I thought so as well. Thing is - I can select date form hours and it works.
# Posted By Raymond Camden
| 1/10/08 9:46 AM
http://www.sqlite.org/datatypes.html
Look at this, it says Sqlite is Typeless. Everything is considered as a string. It might be best to save dates as numerics and then do a comparison.
Look at this, it says Sqlite is Typeless. Everything is considered as a string. It might be best to save dates as numerics and then do a comparison.
# Posted By Anuj Gakhar
| 1/10/08 9:49 AM
var now:Date = new Date();
var currentTime:Number = now.getTime();
that should give you the number of seconds since January 1, 1970. Pretty good to use that for date comparisons.
var currentTime:Number = now.getTime();
that should give you the number of seconds since January 1, 1970. Pretty good to use that for date comparisons.
# Posted By Anuj Gakhar
| 1/10/08 10:04 AM
With SQLite JDBC driver from http://www.zentus.com/sqlitejdbc/ I actually use my favourite SQL editor Aqua Data Studio to edit AIR embedded databases. Works great. Same JDBC driver should be able to get your local CF to talk to AIR DBs as well.
To get it to work you add SQLite JDBC driver to your java path, and then datasource URL is something like "jdbc:sqlite:/path/to/airapp.db" and driver is "org.sqlite.JDBC".
To get it to work you add SQLite JDBC driver to your java path, and then datasource URL is something like "jdbc:sqlite:/path/to/airapp.db" and driver is "org.sqlite.JDBC".
# Posted By Erki Esken
| 1/10/08 11:38 AM
Check this project:
http://code.google.com/p/asqlib/
Instead of writing sql statements all by yourself, those classes can ganerate sql statements for you. It's still work in progress, so expect changes - more classes for larger sql support.
Have a nice day.
http://code.google.com/p/asqlib/
Instead of writing sql statements all by yourself, those classes can ganerate sql statements for you. It's still work in progress, so expect changes - more classes for larger sql support.
Have a nice day.
# Posted By mloncaric
| 1/10/08 12:55 PM
sqlite is typeless with suggestions, but supports suggested types. my only experiece with sqlite is within trac, so I am not sure if the date store was specific to trac implementation or sqlite. either way, here is how I got similar request to work:
where datetime(t.time, 'unixepoch', 'localtime') > datetime('now', '-25 days', 'localtime')
the site that documents the datetime function is here:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFu...
hope this helps
where datetime(t.time, 'unixepoch', 'localtime') > datetime('now', '-25 days', 'localtime')
the site that documents the datetime function is here:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFu...
hope this helps
# Posted By shag
| 1/10/08 1:42 PM
shag - that throws an error for me when I use the SQLite AIR app:
Error:
near "unixecpoch": syntax error
My precise sql:
select *
from hours
where datetime(hours.date, 'unixepoch', 'localtime') > datetime('now', '-25 days', 'localtime')
Error:
near "unixecpoch": syntax error
My precise sql:
select *
from hours
where datetime(hours.date, 'unixepoch', 'localtime') > datetime('now', '-25 days', 'localtime')
# Posted By Raymond Camden
| 1/10/08 1:50 PM
Another tool I have found useful is: -
http://www.sqlmaestro.com/products/sqlite/
Christophe's tool is also useful (as u mentioned)
http://www.sqlmaestro.com/products/sqlite/
Christophe's tool is also useful (as u mentioned)
# Posted By J
| 1/10/08 5:08 PM
sorry ray. i am working on some assumptions here. did you try this already?
select * from hours
where date > date('now')
if it doesn't work, i would ask for your create table statement and/or what your data looks like when you select it. i feel certain this is in the format of your data.
also, to one of the other posts, aqua data is a great tool. it natively connects to all the major db's, and will connect to anything else with an odbc or jdbc driver. it is a very sweet tool.
select * from hours
where date > date('now')
if it doesn't work, i would ask for your create table statement and/or what your data looks like when you select it. i feel certain this is in the format of your data.
also, to one of the other posts, aqua data is a great tool. it natively connects to all the major db's, and will connect to anything else with an odbc or jdbc driver. it is a very sweet tool.
# Posted By shag
| 1/10/08 10:21 PM
Shag - can you download my app? You can find it here:
http://www.coldfusionjedi.com/index.cfm/2008/1/9/W...
Enter a project, client, then a few hours, and you will have sample data.
http://www.coldfusionjedi.com/index.cfm/2008/1/9/W...
Enter a project, client, then a few hours, and you will have sample data.
# Posted By Raymond Camden
| 1/11/08 8:28 AM
Hey Ray,
Looking at your date values, it looks like they're getting entered strangely into the dB. It looks like it's adding milliseconds to the date. It shows up in SQLite Manager as 1199941200000, but when converting back to a string date, this will not convert properly. However, if you remove the last 3 zeros, it comes back correctly at 2008-10-1.
Looking at your date values, it looks like they're getting entered strangely into the dB. It looks like it's adding milliseconds to the date. It shows up in SQLite Manager as 1199941200000, but when converting back to a string date, this will not convert properly. However, if you remove the last 3 zeros, it comes back correctly at 2008-10-1.
# Posted By Gareth
| 1/11/08 2:32 PM
I thought maybe I was entering data wrong. Let me play with that.
# Posted By Raymond Camden
| 1/11/08 2:33 PM
That seems to have doen it. Now I need to figure out how to get stuff for today. date > date('now') doesn't seem to work.
# Posted By Raymond Camden
| 1/11/08 2:40 PM
How about this
SELECT * from hours
where date('now') <= date(date, 'unixepoch')
SELECT * from hours
where date('now') <= date(date, 'unixepoch')
# Posted By Gareth
| 1/11/08 2:58 PM
No go. No error, but no results. Even if I switch < to >.
# Posted By Raymond Camden
| 1/11/08 3:07 PM
It looks like the time might be a little screwy. If you enter 1200000000 as the date, it says that it is 2008-1-10 (correct), but if you enter 1200014000, it says that it is 2008-1-11 (incorrect, actually 2008-1-10 20:13:20), so I'm not sure if it's doing something with GMT or something weird like that. 2008-1-11 starts from 1200027600.
# Posted By Gareth
| 1/11/08 3:24 PM
Ah, ha! Looks like that is what it is :) If you use this date 1200027600 (2008-1-10 19:00:00) it shows as 2008-1-11. But change that to 1200027599, 1 second earlier, and it returns 2008-1-10.
# Posted By Gareth
| 1/11/08 3:28 PM
I apologize to all of the subscribers for all of my posts.
Try this
SELECT *
FROM hours
WHERE date('now', 'localtime') <= date(date, 'unixepoch', 'localtime')
This will add 5 hours to the date time (if you are EST) to account for the time difference from GMT.
Try this
SELECT *
FROM hours
WHERE date('now', 'localtime') <= date(date, 'unixepoch', 'localtime')
This will add 5 hours to the date time (if you are EST) to account for the time difference from GMT.
# Posted By Gareth
| 1/11/08 3:52 PM
Still now working for me, but maybe I'm still storing it bad. When I do a generic select with no filter, I have
1/11/08
Should I have store 2008-11-1 instead?
1/11/08
Should I have store 2008-11-1 instead?
# Posted By Raymond Camden
| 1/11/08 3:55 PM
ok, after much fumbling around (you wouldn't believe the self inflicted issues i had with this), i finally got this up and going. i was about to ask for your changes after @gareth's post. then realized, i could just drop the 0's myself. that having been said, here you are:
select * from hours
where datetime(hours.date, 'unixepoch', 'localtime') > date('now')
if this doesn't work, then please post/send the updated code.
select * from hours
where datetime(hours.date, 'unixepoch', 'localtime') > date('now')
if this doesn't work, then please post/send the updated code.
# Posted By shag
| 1/11/08 4:40 PM
sorry, the 'localtime' isn't necessary. i also just realized I used > vs >=, and it worked. looks like sqlite uses different tie breaking logic. i would suggest >= in case future changes to sqlite.
select * from hours
where datetime(hours.date, 'unixepoch') >= date('now')
select * from hours
where datetime(hours.date, 'unixepoch') >= date('now')
# Posted By shag
| 1/11/08 4:46 PM
@Ray,
I'm not sure why it's not working. If it returns the date correctly, I'm guessing that it should be working. What do you get if you type in
select date('now') from hours
(just to see what is returned when you select "now")?
Another thing to try is messing around with the unixepoch dates and see if you can get it to return something. I went to this page http://www.esqsoft.com/javascript_examples/date-to...
and grabbed some converted dates and put them into the query to test against and see what was returned.
I'm not sure why it's not working. If it returns the date correctly, I'm guessing that it should be working. What do you get if you type in
select date('now') from hours
(just to see what is returned when you select "now")?
Another thing to try is messing around with the unixepoch dates and see if you can get it to return something. I went to this page http://www.esqsoft.com/javascript_examples/date-to...
and grabbed some converted dates and put them into the query to test against and see what was returned.
# Posted By Gareth
| 1/11/08 5:42 PM