Sqlite: How to bind and insert date from C++?

Using C++ (Visual Studio) and sqlite. How do I bind a date to a parameter?

sqlite3_stmt *statement;

const char *sql = 
    "INSERT INTO employees "
        "(full_name,"
        "date_started)" 
    " VALUES "
        "(@full_name,"
        "@date_started)";

sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);

int parameterIndex = sqlite3_bind_parameter_index(statement, "@full_name");
sqlite3_bind_text(statement, parameterIndex, "John Smith", -1, SQLITE_TRANSIENT);

parameterIndex = sqlite3_bind_parameter_index(statement, "@date_started");

// <??? what goes here ???>
// I want to include the local current time, so I want to know:
/开发者_Python百科/ 1. what's the best way to get local time in C++
// 2. and what goes here for the date binding

sqlite3_step(statement);

sqlite3_finalize(statement);

Note: I don't want to set the current time using sql (e.g, CURRENT_TIMESTAMP, etc.)

There is no trick to it:

const char * sql =
    "INSERT INTO Employees(full_name, data_started) VALUES (?, ?)";
time_t time = 0x3DE43B0C;
sqlite3_bind_int64(statement, 2, time);

Here is the relevant part of the documentation:

1.2 Date and Time Datatype

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:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

You should be able to get the local time using the localtime() function.

/* localtime example */
#include <stdio.h>
#include <time.h>

int main ()
{
  time_t rawtime;
  struct tm * timeinfo;

  time ( &rawtime );
  timeinfo = localtime ( &rawtime );
  printf ( "Current local time and date: %s", asctime (timeinfo) );

  return 0;
}

See http://www.cplusplus.com/reference/clibrary/ctime/localtime/ for detailed information and an example.

Here's what I figured out. It's working but I'm open to better methods.

time_t rawtime;
struct tm *currentTime;
time ( &rawtime );
currentTime = localtime ( &rawtime );

const int TIME_STRING_LENGTH = 20;
char buffer [TIME_STRING_LENGTH];

// SQLite expected date string format is "YYYY-MM-DD HH:MM:SS" (there are others too)
strftime(buffer, TIME_STRING_LENGTH, "%Y-%m-%d %H:%M:%S", currentTime);
sqlite3_bind_text(statement, parameterIndex, buffer, -1, SQLITE_TRANSIENT);