CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Muhammad Haroon
Muhammad Haroon

Reputation: 284

Android SQL DATETIME CURRENT_TIMESTAMP

In my android application I have a datetime field with CURRENT_TIMESTAMP as default value. The problem is that it is inserting different value then the emulator's time.

Here is my code:

public void onCreate(SQLiteDatabase db) {
   String query = "CREATE TABLE " + TABLE_BLOCKEDSMS + " (" +
            COLUMN_BLOCKEDSMS + " TEXT," +
            COLUMN_DATETIME + "DATETIME DEFAULT CURRENT_TIMESTAMP);";
    db.execSQL(query);
}

Any help will be appreciated.

Upvotes: 1

Views: 5559

Answers (1)

MikeT
MikeT

Reputation: 56953

I think DATETIME DEFAULT (datetime('now','localtime')) will do what you want and will be sortable it is stored as/like 2017-01-12 07:33:10.

i.e. 

public void onCreate(SQLiteDatabase db) {
   String query = "CREATE TABLE " + TABLE_BLOCKEDSMS + " (" +
            COLUMN_BLOCKEDSMS + " TEXT," +
            COLUMN_DATETIME + "DATETIME DEFAULT (datetime('now','localtime')));";
    db.execSQL(query);
}

As per the comments the part of the statement COLUMN_DATETIME + "DATETIME DEFAULT (datetime('now','localtime'))); is explained as follows:-

COLUMN_DATETIME is the name of the column, DATETIME is the type of the column (noting that SQLite does not have rigid types Datatypes In SQLite Version 3 ). DEFAULT indicates the default value to be used (optional).

If the value is explicit e.g 0 'A' etc, then the value simply follows DEFAULT. However, if the value is calculated via an expression then it must be enclosed in brackets (). SQLite has numerous functions of which 5 are date and time functions. These are date, time, datetime, julianday and strftime.

Either datetime or strftime could be used, although datetime was chosen for this example/solution. So hence we have ... DEFAULT ( datetime(<argumnets>)) where haven't as yet been explained.

The syntax for datetime is datetime(timestring, modifier, modifier, ...). Timestring being the time (inclusion of date allowed). now generates a time timestring according to the current datetime. However, it is UTC. The localtime modifer, modifies the time according to local time (NOTE assuming that the timestring is UTC with caveat of unpredictable results if not).

Further and more detailed information can be found here SQL As Understood By SQLite - Date And Time Functions

Upvotes: 5

Related Questions