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

Question

Using C ++ (Visual Studio) and sqlite. How to associate a date with 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: // 1. what 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 do not want to set the current time using sql (e.g. CURRENT_TIMESTAMP, etc.).

Answer 1

Here is what I understood. It works, but I am 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); 

Answer 2

There is no trick:

 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 Data Type

SQLite does not have a storage class reserved for storing dates and / or time. Instead, SQLite's built-in date and time functions are capable of storing dates and times as TEXT, REAL, or INTEGER values:

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

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

Answer 3

You can get 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 more details.