CODEWITHSUNDEEP
sqliteandroid-sqlite
Jesbin MJ
Jesbin MJ

Reputation: 3249

Inserting current date and time in SQLite database

I want to create a table in SQLite in which one of the field is for date, in which date and time of current instance should save. Which data type should I use?

I'm planning to use 'timestamp'. How to insert current timestamp value to the field? Also how to write content values for this date field?

Upvotes: 49

Views: 131498

Answers (6)

surfmuggle
surfmuggle

Reputation: 5942

In my case i wanted to have a timestamp with fractions of a second.

  • The keyword CURRENT_TIMESTAMP has only a precision of YYYY-MM-DD HH:MM:SS (see docs DEFAULT clause).
  • The function strftime() can return fractions of a second

Example to use strftime() in an INSERT

INSERT INTO YourTable (TimeStamp)  
       VALUES (strftime('%Y-%m-%d %H:%M:%S:%s'))

Comparison of CURRENT_TIMESTAMP and strftime()

SELECT 'CURRENT_TIMESTAMP' as Timestamp_Command, 
        CURRENT_TIMESTAMP as TimeStamp_Precision,
       'only seconds'  as Timestamp_Comment 
UNION ALL           
SELECT 'strftime(%Y-%m-%d %H:%M:%S:%s)' as Timestamp_Command, 
        (strftime('%Y-%m-%d %H:%M:%S:%s')) as TimeStamp_Precision,
        'with fraction of a second' as Timestamp_Comment

Sqlite_Select_Current_Timestamp_vs_strftime

Example to use it in c#

The following is based on bulk insert in sqlite with ado.net

public static void InsertBulk(SqliteConnection connection)
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
       var command = connection.CreateCommand();
       command.CommandText =
            @"INSERT INTO BulkInsertTable (CreatedOn, TimeStamp)
              VALUES ($createdOn, strftime('%Y-%m-%d %H:%M:%S:%s'))";

       var parameter3 = command.CreateParameter();
       parameter3.ParameterName = "$createdOn";
       command.Parameters.Add(parameter3);  

       // Insert a lot of data
       // calling System.DateTime.Now outside the loop is faster
       var universalTime = System.DateTime.Now.ToUniversalTime();
       for (var i = 0; i < 15_000; i++)
       {
          parameter3.Value = System.DateTime.Now.ToUniversalTime();
          // faster 
          // parameter3.Value = universalTime;
          command.ExecuteNonQuery();
        }
        transaction.Commit();
    }
    connection.Close();
}

Upvotes: 0

CL.
CL.

Reputation: 180010

SQLite supports the standard SQL variables CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP:

INSERT INTO Date (LastModifiedTime) VALUES(CURRENT_TIMESTAMP)

The default data type for dates/times in SQLite is TEXT.

ContentValues do not allow to use generic SQL expressions, only fixed values, so you have to read the current time in Java:

cv.put("LastModifiedTime",
       new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));

Upvotes: 79

youfu
youfu

Reputation: 1627

Since SQLite 3.38.0, there is a unixepoch() function that returns UNIX timestamp in integer. Does the same thing as strftime('%s').

References:

Upvotes: 0

David Garoutte
David Garoutte

Reputation: 371

To get the current local(system) time, add the 'localtime' option:

select datetime('now', 'localtime');

Upvotes: 5

Sergey Grabak
Sergey Grabak

Reputation: 183

I'm using timestamps a lot in my app. For me the best way to keep the timestamp is to convert it in milliseconds. After that it is easy to convert it to any locale.

If you need the current time use System.currentTimeMillis(). Content values are easy to use, you just and field and value, like:

ContentValues ins_reminder = new ContentValues();
ins_reminder.put("REMIND_TIMESTAMP", System.currentTimeMillis());

Upvotes: 1

Premsuraj
Premsuraj

Reputation: 2505

INSERT INTO Date (LastModifiedTime) VALUES(DateTime('now'))

Use this site for further reference.

Upvotes: 31

Related Questions