I am trying to insert a record into a sqlite database using current date and time.

[martinne]

I am new to sqllite and QT, so I would appreciate any help I can get.
I am simply trying to create a table that has a date field included in it. When I populate the table I would like the date field to be populated with the current system date and time. I tried using the following:

db.exec("insert into readings4 values(4, 796.5,52.5,200,250,DATETIME('NOW')");

The above command does not seem to work. Please advise...


I have listed the rest of my code below for reference.

Thanks!



QSqlQuery tableTest = db.exec("SELECT * FROM readings4");

if(tableTest.lastError().type() != 0) {
tableTest = db.exec("CREATE TABLE readings4 ('nodeId' INTEGER ghNOT NULL , 'temperature' FLOAT, 'humidity' FLOAT, 'light' INTEGER, 'soil' FLOAT, 'dateTime' DATE)");

//Check to see that new table was created successfully
if(tableTest.lastError().type() != 0) {
QMessageBox::critical(0, QObject::tr("DB table init error"), tableTest.lastError().text(), QMessageBox::Ok);
return false;
}
QMessageBox::information(0, QObject::tr("Creating new table"), "Could not find readings table.\n Creating it now.", QMessageBox::Ok);

db.exec("insert into readings4 values(1, 796.5,52.5,200,250,'2010-03-23 12:03')");
db.exec("insert into readings4 values(2, 76.5,52.5,200,250,'2010-03-23 12:03')");
QSqlQuery lastExec = db.exec("insert into readings4 values(3, 76.5,52.5,200,250,'2010-03-23 12:03')");

}

db.exec("insert into readings4 values(4, 796.5,52.5,200,250,DATETIME('NOW')");

[JohannesMunk]

Hi!

I have not used sqlite yet, but with mysql the following table setup works:

CREATE TABLE IF NOT EXISTS actions (int id,...., pTime TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS actions (int id,...., pTime TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

To copy to clipboard, switch view to plain text mode 

On insert/update you never explicitly set the value for pTime. That way it it gets the default value, which is the current_timestamp.. As bonus, that way the timestamp will be the database servers time and not the client time..

According to this http://www.sqlite.org/lang_createtable.html this syntax should be available in sqlite, too.

HIH

Johannes

[martinne]

Johannes,

Thanks for your reply. I got it to work simply by using db.exec("insert into readings4 values(4,796.5,52.5,200,250,datetime())");