Inserting the timestamp in SQLite can be done in multiple ways. You can either store complete timestamp which contains both date and time or you can store only the date or time. Checkout the following ways of storing the date in database.

Method 1

Inserting current timestamp by default

If you don’t want to insert the timestamp manually each time you create a row, you can do it by keeping the default value while creating the table. Use the DEFAULT keyword and one of the following data type.

CURRENT_TIME – Inserts only time
CURRENT_DATE – Inserts only date
CURRENT_TIMESTAMP – Inserts both time and date

CREATE TABLE users(
    id INTEGER PRIMARY KEY,
    username TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Method 2

Using datetime() while inserting the row

You can also insert datetime manually using datetime() function while inserting the row.

db.execSQL("INSERT INTO users(username, created_at) VALUES('ravitamada', 'datetime()'");

Method 3

Using java Date functions

You can also use java Date() and SimpleDateFormat() methods. Create a function that returns timestamp and use the value while setting the content value for date column.

Following function getDateTime() returns datetime.

private String getDateTime() {
        SimpleDateFormat dateFormat = new SimpleDateFormat(
                "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        Date date = new Date();
        return dateFormat.format(date);
}

and use the value returned by getDateTime() to set content value.

ContentValues values = new ContentValues();
values.put('username', 'ravitamada');
values.put('created_at', getDateTime());
// insert the row
long id = db.insert('users', null, values);
Author

Ravi is hardcore Android programmer and Android programming has been his passion since he compiled his first hello-world program. Solving real problems of Android developers through tutorials has always been interesting part for him.

  • Fer

    Thanks. Very helpful, one question though… how would you store that value in java later on? I mean, how does the DATETIME SQLite value translates to Java?. 🙂

  • ram

    could you please provide source code for this.

  • dhinesh5152

    great work… very useful sqlite and java tips

  • Rygel

    Oh man! you saved my life lol. it works like a charm thanks!

  • Malaika Khan

    Please Ravi tell me that why I am getting error in this line Date date = new Date();

    • balaji

      you might have imported the wrong package for Date

      • Malaika Khan

        ok Thanks

    • Sahil Kukkar

      set parameter as long
      like
      that will work

      Date date = new Date(0);

  • YOyie GuangCo

    what should i import for SimpleDateFormat, Locale and date ? sorry for the noob question

  • VsC

    Very good. But i always get date like 10-12-1968… How i change it to the actual date?

    • Sahil Kukkar

      same problem here did u got any solution for that please share with me

  • al

    Thanks

  • Vijayadhas

    Thank you..!!!

  • Aya Ahmed

    what is value which returned by insert function??

    • Ahmad

      Returns:
      the row ID of the newly inserted row, or -1 if an error occurred

  • Waqas Wagan

    dear sir i am working on network service discovery could you helpme ?

  • rana fahad

    Dear Sir i have a problem to create SQLite database and also if its created then how to export to this database from the application.

  • Sunita Hiremath

    if i want to insert date in my data base automatically..

  • Pulkit Kumar

    I believe, Current date time is in GMT, not local device time.

  • Sunita Hiremath

    i m able to insert date but getting exception while retrieving

    • krrish

      catch (Exception e)
      {

      Toast.makeText(MainActivity.this, “ERROR “+e.toString(), Toast.LENGTH_LONG).show();

      }
      this tell u what exactly the exception is all about

  • gerry ajah

    i using the current_timestamp, but it give me the wrong time. maybe it’s different timezone or something. how i fix this ??

  • Xavi (Drakgoku1)

    Always I get datetime() instead of current time

    I show my example :

    private static final String TIMESTAMP = “dataActual”;

    Table :
    total += ANALITICS.TIMESTAMP +” DATETIME DEFAULT CURRENT_TIMESTAMP, “;
    Insert
    contentValue.put(ANALITICS.TIMESTAMP, “datetime()”); // Always get datetime(); why ?

    • Venktesh Sakthi

      because u used it as string.u want to write it as String.valueof(datetime());

  • ziplock9000

    I thought SQLite did not have DATETIME formats and you had to fudge it with a TEXT string?

  • Dineshkumar Devarasu

    how to current time and current date using datepicker and timepicker automatically then how to store current time and date using sqlite database.

  • Kyo

    Hey, nice tip!!
    I want to ask you, do you know how to fetch values datetime in cursor?

  • Shivam Agnihotri

    First method is not working
    REATE TABLE users(id INTEGER PRIMARY KEY,username TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP); nothing is created in android

    • sheungchonglou

      CREATE TABLE users(id INTEGER PRIMARY KEY,username TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
      instead of
      CREATE TABLE users(id INTEGER PRIMARY KEY,username TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);

  • Ankit Gaurav

    Thanks lots for this blog post. Great tutorial. 🙂

  • Jorge Serrano

    Great. Thanks!
    Precise and concise.

  • Satyen Udeshi

    For anyone looking to store datetime value as per current time zone, you can use the following function:
    db.execSQL(“INSERT INTO users(username, created_at) VALUES(‘satyenudeshi’, ‘datetime(‘now’,’localtime’)'”);

  • Dilip Ghawade

    how to store a radiobutton, value in database

  • Graduated Link

    Simple and powerful. Thanks!

  • biruk tsegaye

    Thanks a lot!

  • BHavesh

    how to compare date and time less then or equal to

  • thx

  • Abhishek Phalke

    sir how to show particular database record show in AutoCompleteTextView please tell me

  • Amol Gupta

    Just curious will will the created at fire even when updating an existing row

    CREATE TABLE users(
    id INTEGER PRIMARY KEY,
    username TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    ??