Results 1 to 3 of 3

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

  1. #1
    Join Date
    Apr 2010
    Posts
    2
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

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

    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')");

  2. #2
    Join Date
    Feb 2007
    Location
    Karlsruhe, Germany
    Posts
    469
    Thanks
    17
    Thanked 90 Times in 88 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: I am trying to insert a record into a sqlite database using current date and time

    Hi!

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

    Qt Code:
    1. 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

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: I am trying to insert a record into a sqlite database using current date and time

    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())");

Similar Threads

  1. Replies: 0
    Last Post: 11th March 2010, 08:41
  2. Date defines in Sqlite?
    By Raccoon29 in forum General Programming
    Replies: 1
    Last Post: 24th November 2009, 00:14
  3. SQLite sometimes doens't INSERT into database
    By cevou in forum Qt Programming
    Replies: 5
    Last Post: 30th October 2009, 09:10
  4. sqlite and date
    By Raccoon29 in forum General Programming
    Replies: 2
    Last Post: 18th August 2008, 12:43
  5. Bulk insert into SQLite
    By munna in forum Qt Programming
    Replies: 6
    Last Post: 19th November 2007, 04:56

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.