Friday, February 13, 2015

SQLite Date Time Functions Examples


An SQLite Database table named 'trx_log' contains a string date time field trxDateTime and other fields like trxTargetNumber, trxAmount, trxStatus. A snapshot of table schema is given below, some sample data is also included.

sqlite-android-search-by-year-group-by-month



All fields other than trxAmount are string, only trxAmount which represents a superficial transaction amount is a number type.

SQLite Year calculation function

Sqlite provides strftime function to extract specific part from a date time string. We will use it to find out year in SQLite. Sample SQL query code is given below:

SELECT strftime('%Y', trxDateTime) as sYear 
FROM trx_log
This query will return four rows of data (based on snapshot shown earlier) each containing 2014 and labeled sYear.
The first parameter here is a format string, in this format string %Y represents a year.
Please notice that the Y here is a capital letter.









How to make where clause based on year in SQLite SQL?

You will get the year part of a date using strftime function and then make a decision based on that. For example, if we want data from year 2014 we can use following query.

SELECT strftime('%Y', trxDateTime) as sYear 
FROM trx_log WHERE  strftime('%Y', trxDateTime)='2014'  

Same technique can be used to group data based on year as well. Use of this technique to group by data is well demonstrated in following post:
http://aprogrammersday.blogspot.com/2014/02/group-by-month-or-year-and-sum-in-sqlite.html

SQLite month calculation function

The strftime function can also be used to find out the month section from a date time string column. The exact SQLite SQL query for finding the month is given below

SELECT strftime('%m', trxDateTime) as sMonth 
FROM trx_log
The query mentioned above will return four rows of data (based on snapshot shown earlier) containing 02 as month. It is worth mentioning again that the m in this case will be a small letter. Capital M will cause the SQLite engine to return minutes instead of month.

How to make where clause based on month in SQLite SQL?

It is very easy to use a month field as basis of a where query or group by operation in SQLite. We will use the same strftime function again. An example SQ< query is shown below.
SELECT strftime('%m', trxDateTime) as sYear 
FROM trx_log WHERE  strftime('%m', trxDateTime)='02'  
Please note that SQLite will return months between January to September as two digits where the leading digit will be 0 always. For months December to 

SQLite day calculation function

How to find a day from SQLite table using SQL? Use the strftime function and supply appropriate formatting string in first parameter. The SQLite SQL query for finding the month is given below:

SELECT strftime('%d', trxDateTime) as sDay 
FROM trx_log

How to make where clause based on month in SQLite SQL?

You can also use strftime function to filter data and group it by certain values of a specific day. We showed you earlier how to find out the day from a SQLite date field. How to use it for a where operation is shown below:

SELECT strftime('%d', trxDateTime) as sYear 
FROM trx_log WHERE  strftime('%d, trxDateTime)='02'  

No comments:

Post a Comment

Feel free to talk back...