My data are dates in the format;
YYYY-MM-DD HH:MM:SS.SSS
I am trying to use the function strftime () to convert the data to seconds
since 1970-01-01. I was hoping that within a WHERE clause or the ON condition
in an OUTER JOIN.
What I wanted to do was something like this;
SELECT t1.dateitem1,
t2.dateitem2
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON
strftime ('%s', t1.dateitem1) BETWEEN strftime (t2.dateitem2) - 600
AND
strftime (t2.dateitem2) + 600
Where columns dateitem1 and dateitem 2 are both declared as DateTime fields.
Can this ( or something like it ) work?
Bart
________________________________
From: [email protected] on behalf of Simon Davies
Sent: Mon 9/21/2009 4:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons
2009/9/21 Barton Torbert <[email protected]>:
> Hello,
>
> I am having trouble doing a rather odd data comparison.
>
> I have two table, each with a DateTime field. The timestamps in these fields
> do not match exactly. I want to find the row in the second table that is
> within a specific time period around the time period to the DateTime in the
> first table.
Note that SQLite has no native DateTime type. A DateTime could be a
text string, or a real Julian day. What is your data?
>
> I have tried various combinations of reformatting the data to do a
> comparison. None of these works. In particular I had hoped that the
> strftime function (converting using the '%s' format to seconds since
> 1970-01-01 would work but it does not. The comparison with = is okay, but I
> need to use some combination of < and > or a BETWEEN. Nothing seems to work.
I can't tell what is wrong because you have provided no examples.
>
> Does anybody have a suggestion?
(Re)read http://www.sqlite.org/lang_datefunc.html
Provide more information on what you are doing...
>
> Bart
>
Regards,
Simon
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
