How to manage dates in Android sqLite database when I care only about the date (which is to be unique) and not the time?

Question

This is my first question so bear with me and just let me know if it's not clear what I mean. I have no idea how to look for an answer. I'm pretty new to both Android and Java.

I want to use sqLite database table for storing values and I want to have only one, unique date and "value" combination per day. Idea is to make kind of a log where anything can be logged only once per day (except updating or editing afterwards). I decided to use Unix timestamps and INTEGER as data type for date. Table looks something like this:

("Unix_timestamp","value","state")

Right now I am inserting System.currentTimeMillis()/1000L but then there might be thousands of dates per one day with the same "value" not to mention problem with selecting.

What is the most convenient way of inserting and selecting dates? Should it be done via sqLite functions or some Android or java methods?

EDIT: I almost forgot to add: I have an idea of inserting dates in Unix time but in a way they are eg. always the begining of the day (midnight) but I couldn't figure out how to do this, which methods to use. I feel like sqLite is really bad when it comes to dates.

Answer 1

If I am not wrong, you want to save data in UTC timezone so that it can converted in any timezone easily.

Case 1 : For datetime

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);
format.setTimeZone(TimeZone.getTimeZone("UTC"));

Date date = format.parse(dateText);
long millis = date.getTime();
//your code - save datetime in milliseconds in SQLite numeric field

Case 2 : for date only

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); 

Date date = format.parse(dateText);
long millis = date.getTime();
//your code - save datetime in milliseconds in SQLite numeric field

You can create unique constraint on date column in order to keep unique values.

Answer 2

To get a timestamp for the start of the current day (in seconds) in SQLite you can use the strftime function like so:

strftime('%s','now','start of day')

Alternatively, you can build this value in java using Calendar:

Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);
long time = calendar.getTimeInMillis();

You can use a uniqueness constraint in your CREATE TABLE command to enforce that each combination of timestamp and value is unique across all rows.

CREATE TABLE tableName (..., UNIQUE(column1, column2));

If you want, in the same statement you can specify a conflict algorithm to handle cases where an insert or update creates a violation of this uniqueness constraint. Alternatively, you can specify it when you try to do the insert/update:

SQLiteDatabase db = ...; // get database
long rowid = db.insertWithOnConflict(TABLE, null, values, SQLiteDatabase.CONFLICT_IGNORE);

CONFLICT_IGNORE won't raise an exception and won't write a new row if it creates a constraint violation. In that case insertWithOnConflict() will return -1, so that way you can tell if the insert succeeded or not.