Inserting timestamp in sqlite in Javascript

Question

I want Sqlite to autopopulate timestamp, but Javascript doesn't allow me to pass only 2 values (like

VALUES (?, ?)

) and let Sqlite handle the 3rd value for the timestamp. I know I can provide the 3rd value with

new Date()

but this is not the preferred way.

If I run the below code as is, the timestamp column is all null.

What is the right way to do this?

 db.serialize(function () {

        db.run("CREATE TABLE if not exists songs (title TEXT, lyric TEXT, timestamp DATATIME DEFAULT CURRENT_TIMESTAMP)");



        var stmt = db.prepare("INSERT INTO songs VALUES (?, ?, ?)");

        for (var i = 0; i < 10; i++) {

            stmt.run("Title" + i, "Lyric" + i);

        }

        stmt.finalize();



    });

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

If you want to use the default value for a column then just leave it out of the INSERT:

var stmt = db.prepare("INSERT INTO songs (title, lyric) VALUES (?, ?)");

Two things to note here:

Specifying the column names in an INSERT statement is always a good idea, hence the (title, lyric) addition. This way there is no ambiguity as to what the placeholders are referring to.
You're only specifying two columns (and letting the third use its default value) so there are only two placeholders.

Inserting timestamp in sqlite in Javascript

Sign up