Save SQLite TIMESTAMP in the format of MySQL TIMESTAMP, Android

Question

I want to save current TIMESTAMP to a SQLite database in Android.

But it should be in MySQL TIMESTAMP format which is 2014-04-02 20:04:05

How to make it in db.execSQL();

Please help!

The two formats should simply be the same. If not, the SQlite database shouldn't accept it — weeknie
– weeknie, Commented Apr 2, 2014 at 14:39
@weeknie Actually I'm not going to save MySQL TIMESTAMP, but I need to save current TIMESTAMP in the form of MySQL — eMM
– eMM, Commented Apr 2, 2014 at 14:42
@HamidShatu There are many formats. I need it in 2014-04-02 20:04:05 format — eMM
– eMM, Commented Apr 2, 2014 at 14:44

Karakuri · Accepted Answer · 2014-04-02 14:40:33Z

1

Have a look at SQLite date and time functions. You would do something like strftime(%Y-%m-%d %H:%M:%S, 'now') in your SQL string. This particular format also has a shortcut: datetime('now')

db.execSql("insert into table (column) values ( datetime('now') )");

My preference though is to simply store the current time as a long, which you can then format any way you like (or any way the user likes, or any way the system defaults to showing dates/times).

answered Apr 2, 2014 at 14:40

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eMM Over a year ago

How would I do it this way please db.execSql("CREATE TABLE "+ TABLE_TODO + "("+KEY_CREATED_AT + " DATETIME" +")");

Karakuri Over a year ago

Ah, that's a different question. Somethign you need to know about SQLite is that it really only has 5 column types: INTEGER, REAL, TEXT, BLOB, and NULL. It may recognize other type names like varchar, but it translates that to a type it knows: TEXT. From the SQLite documentation: "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"

HappyDump · Accepted Answer · 2014-04-02 14:43:01Z

1

You should use the following date format in order to insert dates/times in SQLite:

DateFormat dateFormatISO8601 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

and retrieve in string as below:

String crntDate = dateFormatISO8601.format(new Date());

P.S. Make sure your column is of type DATETIME

answered Apr 2, 2014 at 14:43

HappyDump

4539 silver badges18 bronze badges

3 Comments

eMM Over a year ago

how to create the table with a DATETIME column in the format of yyyy-MM-dd HH:mm:ss please?

HappyDump Over a year ago

create table tbl1(id int primary key, dt datetime);

eMM Over a year ago

Then I have to insert crnDate for the column dt isn't it?

Collectives™ on Stack Overflow

Save SQLite TIMESTAMP in the format of MySQL TIMESTAMP, Android

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related