6

i inserted a string, date, time and int value into an sqlite database using this code

void addRemAction(RemActions remaction) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_ACTOINS_TITLE, remaction.getTitle()); // Action title
    values.put(KEY_ACTIONS_MSG, remaction.getMsg()); // action msg



    values.put(KEY_ACTIONS_DATE, dateFormat.format(new Date()));  // action date
    values.put(KEY_ACTIONS_TIME, remaction.getTime()); // action time
    values.put(KEY_ACTIONS_PLACE_LONG, remaction.getPlong()); // action place long
    values.put(KEY_ACTIONS_PLACE_LAT, remaction.getPlat());  // action place lat

    // Inserting Row
    db.insert(TABLE_ACTIONS, null, values);
    db.close(); // Closing database connection

and this is my code to retrieve it

RemActions getRemAction(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_ACTIONS, new String[] {KEY_ACTOINS_TITLE, KEY_ACTIONS_MSG, KEY_PLACES_NAME, KEY_ACTIONS_DATE, KEY_ACTIONS_TIME}, KEY_ACTIONS_ID + "=?",
            new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    RemActions remActions = new RemActions(cursor.getString(0),
            cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getInt(4));
    // return remActions
    return remActions;

now my ide is giving me errors because the 4th argument in my constructor for the RemAction class is of type Date from the java.util.date class.

My question is "how do i set the cursor to get it in a date format?"

ill b glad to provide additional code on request

Padma Kumar
  • 19,893
  • 17
  • 73
  • 130
CtJnx
  • 71
  • 1
  • 2
  • 5
  • 1
    welcome to SO..close db and also cursor in getRemAction() method it will give u exception. – TheFlash Jul 30 '13 at 11:36
  • Also, you can just use, `cursor.getDate(1)` to get the date. Have not used them in a while, but it should be something like this , refer : http://www.coderanch.com/t/445539/JDBC/databases/retrieve-Date-JDBC-sql – The Dark Knight Jul 30 '13 at 11:38
  • @TheDarkKnight Please note Android does not use JDBC, and there's no getDate in SQLite. – m0skit0 Jul 30 '13 at 14:34

3 Answers3

14

Since you cannot save date/time values in SQLite, you should first convert them to milliseconds and store them as that. 

Date c = new Date(System.currentTimeMillis());
long milliseconds = c.getTime();
//To get current time

If you then need to change milliseconds back to a date format:

Date c = new Date(cursor.getLong(4));

Then you can format the date using SimpleDateFormat.

juergen d
  • 201,996
  • 37
  • 293
  • 362
ClaireG
  • 1,244
  • 2
  • 11
  • 23
2

java.time

The java.util Date-Time API is outdated and error-prone. It is recommended to stop using it completely and switch to the modern Date-Time API*.

Solution using java.time, the modern Date-Time API:

  1. Use Instant#now to get the current instant.
  2. Use Instant#toEpochMilli to convert an instant to the number of milliseconds from the Unix epoch (1970-01-01T00:00:00Z).
  3. Use Instant#ofEpochMilli to get an instance of Instant using milliseconds from the Unix epoch (1970-01-01T00:00:00Z).

Demo:

import java.time.Instant;

public class Main {
    public static void main(String[] args) {
        Instant instant = Instant.now();
        System.out.println(instant);

        long instantToEpochMilli = instant.toEpochMilli();
        System.out.println(instantToEpochMilli);

        Instant instantOfEpochMilli = Instant.ofEpochMilli(instantToEpochMilli);
        System.out.println(instantOfEpochMilli);
    }
}

Output of a sample run:

2021-07-13T21:23:46.836723Z
1626211426836
2021-07-13T21:23:46.836Z

ONLINE DEMO

An Instant represents an instantaneous point on the timeline in UTC. The Z in the output is the timezone designator for a zero-timezone offset. It stands for Zulu and specifies the Etc/UTC timezone (which has the timezone offset of +00:00 hours).

Learn more about the modern Date-Time API from Trail: Date Time.

* For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.

Arvind Kumar Avinash
  • 71,965
  • 6
  • 74
  • 110
  • 1
    I don’t think the OP is storing milliseconds since the epoch, though the other answers seem to assume that they do. I would also find it wiser to store a string in ISO 8601 format, using `Instant.toString()` and `Instant.parse()` for conversion (since, IIRC, SQLite does not offer a `datetime` data type). – Ole V.V. Jul 14 '21 at 04:39
  • Thanks, @OleV.V. for the feedback. Yes, [SQLite does not have any datetime datatype](https://www.sqlite.org/datatype3.html) and probably that is why the other answer has suggested to store and retrieve the milliseconds since the epoch. Since the OP has accepted that answer, I assumed that it fulfilled the requirement. So, in that case `Instant.parse()` will not be required. Why I did not mention `Instant#toString` explicitly is that I assume that the OP knows the fact that `System.out.println(instant)` prints `instant.toString()`. Nevertheless, your comment is valuable and helpful. – Arvind Kumar Avinash Jul 14 '21 at 07:57
1

Since you're storing this value as Date#getTime(), you can recover it with new Date(cursor.getLong(4)).

Besides, you need to close the Cursor, which you're not doing.

m0skit0
  • 25,268
  • 11
  • 79
  • 127