System.Data.SQLite

Hi,

 In my application, I use a lot of DateTime field. I have a lot of query which use a dateTime field. For example:

- Select all data for a selected week

- Select all data for a selected month, or year

So, what is the best method to handle these query :

-> Use a DateTime field, but with SQLite, function Year(), Month(), does not exist. So a lot of supplementary tasks should be done for getting the year and the month information from the DateTime field.

-> Split the DateTime information into 3 fields : year, month, day. So it's easy to make the previous query with these information.

What is your opinion ?  

Read up on the datetime functions available in the SQLite engine core at:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

 In help file (SQLite.NET.chm) under Date Time functions is last sentece:

New functions "week_number(date)" returns the week number of the year on the supplied date parameter, "datetime2seconds(datetime)" return the number of seconds from the supplied datetime parameter.

I'm looking for week_number(date) function in SQLite.org documentation but I'm not able to find it. Is this somenthing special included in System.Data.SQLite provider?

I think the best approach to deal with queries that retrieve rows for a given period such as a day, a week, a month or a year, is to write the where clause in range terms. If you enclose a column in a function, SQLite is not able to use existing indexes, however if you express the period as a range, SQLite can use the index and therefore the query runs much faster.

For example, (assuming you have the Year function)  instead of:

WHERE Year(DateColum) = 2008

(Assuming you are using ISO format for storing dates), you should use:

WHERE DateColumn >= '2008-01-01 00:00:00' AND DateColumn < '2009-01-01 00:00:00'