Not an expert Ray but would like to hear about your date problem.

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.

hmm, Is date the name of your column by any chance? sounds like a reserved word to me..

I thought so as well. Thing is - I can select date form hours and it works.

http://www.sqlite.org/datat...

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.

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.

With SQLite JDBC driver from http://www.zentus.com/sqlit... 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".

Check this project:
http://code.google.com/p/as...

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.

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/cvstr...
hope this helps

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')

Another tool I have found useful is: -

http://www.sqlmaestro.com/p...

Christophe's tool is also useful (as u mentioned)

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.

Shag - can you download my app? You can find it here:

http://www.coldfusionjedi.c...

Enter a project, client, then a few hours, and you will have sample data.

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.

I thought maybe I was entering data wrong. Let me play with that.

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.

How about this

SELECT * from hours
where date('now') <= date(date, 'unixepoch')

No go. No error, but no results. Even if I switch < to >.

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.

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.

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.

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?

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.

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')

@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/java...
and grabbed some converted dates and put them into the query to test against and see what was returned.