CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Vishvendra Singh
Vishvendra Singh

Reputation: 187

How to store and retrieve date and time in Sqlite database for Android

I am developing a task scheduler / time manager app which calculates how much time the user is spending on his day-to-day activities. I need to store and retrieve the starting time and ending time of the user's activities from the database. But due to there being so many columns in the database schema, it is difficult to implement. I don't see a good way of dealing with this. Please suggest a better way of doing that.

here is the code

package com.example.timejanitor;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class Fixed_Task_Database extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "FixedTask.db";

    public Fixed_Task_Database(Context context, String name,
            CursorFactory factory, int version) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        try {
            db.execSQL("CREATE TABLE fixedtask(_id INTEGER PRIMARY KEY AUTOINCREMENT,taskName TEXT, sHour INTEGER, sMinute INTEGER,fHour INTEGER, fMinute INTEGER,taskDay INTEGER,taskMonth INTEGER, taskYear INTEGER )");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS fixedtask");
        onCreate(db);
    }

}

Upvotes: 0

Views: 3215

Answers (1)

Henry
Henry

Reputation: 43738

The SQLite documentation states:

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.

You could use any of these for your program. Note that Java internally also uses a long to represent date-time values. It counts the milliseconds since 1970-01-01 00:00:00 UTC and is therefore easy to convert to the SQLite INTEGER format.

Upvotes: 2

Related Questions