Store date & time in SQLite database in Android
The documentation at https://www.sqlite.org/datatype3.html 1.2 Date and Time Datatype
says that depending of the usage of the date and time functions of sqlite it stores date and time values in several kind of field types. They types you explained.
Also the docs say at https://www.sqlite.org/lang_datefunc.html you can use the DATETIME Function to create a date time string value. Also including the timezone.
Hope that helps. I would prefer to use unix timestamp and seprated timezone value. Because you can then create the date object easer.
How to add datetime function in SQLite
You can follow different ways. You can use DATETIME('NOW')
while inserting timestamps into the records, can't you? Or you can use this while creating table.
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
where time is the column which takes current time.
For more details refer this
Best way to work with dates in Android SQLite
You can use a text field to store dates within SQLite
.
Storing dates in UTC format, the default if you use datetime('now')
(yyyy-MM-dd HH:mm:ss)
will then allow sorting by the date column.
Retrieving dates as strings from SQLite
you can then format/convert them as required into local regionalised formats using the Calendar or the android.text.format.DateUtils.formatDateTime
method.
Here's a regionalised formatter method I use;
public static String formatDateTime(Context context, String timeToFormat) {
String finalDateTime = "";
SimpleDateFormat iso8601Format = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
Date date = null;
if (timeToFormat != null) {
try {
date = iso8601Format.parse(timeToFormat);
} catch (ParseException e) {
date = null;
}
if (date != null) {
long when = date.getTime();
int flags = 0;
flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;
finalDateTime = android.text.format.DateUtils.formatDateTime(context,
when + TimeZone.getDefault().getOffset(when), flags);
}
}
return finalDateTime;
}
Insert/Update Proper date in SQLLite Table in Android
SQLite does not support a Date
type as seen in some other DBMS. Instead dates are stored either as text in a well known format or as numeric using the unix epoch.
The datetime function reads text in different formats, including YYYY-MM-DD HH:MM:SS
and returns them as text in format YYYY-MM-DD HH:MM:SS
, so in your case, datetime
will return what is already stored in the database in the same format, but will change timezone to localtime (assuming it is in utc before).
Android will make the result of datetime
available through Cursor.getString
. You can get the Date object back by using DateFormat.parse
.
The following should more or less work (without knowing your database schema):
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
[...]
Cursor cursor = mDb.query(DATABASE_TABLE, new String[]{"datetime("+DATE_COLUMN+",'localtime')"}, null, null, null. null, null);
cursor.moveToNext();
Date parsed = dateFormat.parse(cursor.getString(0));
Reference: https://sqlite.org/lang_datefunc.html
How to corectly insert a datetime value in SQLite under Android?
Your problem is you are declaring a datatype that doesn't exist in SQlite. From the docs:
" 1.2 Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions."
So change your datatype in the database, recreate it, and change your methods to insert/update/retrieve data from those columns to match the proper datatype and things should work better for you.
update sqlite column with current date time in android
Another approach is to create a 'TRIGGER' that updates MODIFIED automatically on each 'UPDATE' with SQLite itself, read about it here.
How can I insert data into the SQLITE database with a foreign key using a content values?
Try to use triggers in sqlite
An SQLite trigger is a named database object that is executed automatically when an INSERT, UPDATE, or DELETE statement is issued against the associated table.
check out this link to learn more about triggers
Related Topics
Android: Retrieve Contact Name from Phone Number
Appcompatactivity.Oncreate Can Only Be Called from Within the Same Library Group
Compiling the Latest Openssl for Android
Is Secure.Android_Id Unique for Each Device
How to Get Screen Coordinates from Marker in Google Maps V2 Android
Android: Using Simplecursoradapter to Get Data from Database to Listview
Programmatically Obtain the Android API Level of a Device
How to Access Fragment's Child Views Inside Fragment's Parent Activity
Tabhost with Fragments and Fragmentactivity
How to Limit the Height of Spinner Drop Down View in Android
Getting the Battery Current Values for the Android Phone
Android.Database.Cursorindexoutofboundsexception: Index 0 Requested, with a Size of 0
Pathpattern to Match File Extension Does Not Work If a Period Exists Elsewhere in the File Name
Is There a Simple Example of the Popupwindow Class Using Android V2.0
"Canvas: Trying to Draw Too Large Bitmap" When Android N Display Size Set Larger Than Small
Android - Keep Listview's Item Highlighted Once One Has Been Clicked