Local datetime in SQLite

This article shows how to get the local date time from SQLite


When we run the following SQL, it returns the UTC time..

select datetime('now');

2012-12-26 16:01:07




To return the local time, you can add the the parameter 'localtime' 

select datetime('now', 'localtime');

2012-12-27 00:01:07

[How-to] Get the year-month-day (yyyy-mm-dd) from a date field in SQLite

SQLite is the database for the Android Application.

The following SQL is an example of getting date by SQL statement.

select _id, strftime('%Y-%m-%d', create_date) c_date from TABLE1

Returned result:

Another SQL example shows how to get counts of records by date

select strftime('%Y-%m-%d', create_date), count(strftime('%Y-%m-%d', create_date) ) from TABLE1 group by strftime('%Y-%m-%d', create_date)

More reference for the date field in SQLite can be found in the official website
http://www.sqlite.org/lang_datefunc.html