How to Insert a SQLite Record with a Datetime Set to 'Now' in Android Application

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



Leave a reply



Submit