SQLite3插入时间实例

SQLite3插入时间实例

能够将时间插入sqlite3，并能够按时间区间搜索。

void my_first_sqlite3_func()
 {
     int i;
    sqlite3 *db;
    sqlite3_stmt *stmt;
     char name[16], occ[16];
     char *sql_drop="drop table if exists people;";
     char *sql_create="create table people (id, time, blob);";
     char *sql_insert="insert into people values (?, ?, ?);";
    // char *sql_select="select * from people;";
  char *sql_select="select * from people where time between datetime('2012-04-12 20:35:10.30') and datetime('2012-04-12 20:35:30.30');";
  int len = strlen(sql_select);
   
    sqlite3_open("mydb.db", &db);
 sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0,0,0);
    sqlite3_prepare(db, sql_drop, strlen(sql_drop), &stmt, NULL);
     sqlite3_step(stmt);
 
     sqlite3_prepare(db, sql_create, strlen(sql_create), &stmt, NULL);
     sqlite3_step(stmt);

  char *data = "fjaksdjfkasdjfklasjdkfjasdkfjksadjfklsdajfksdajfklsdjkfljsdakfkjdgkadjfkajkfajskfljasdlkfjadsk";
   
     sqlite3_prepare(db, sql_insert, strlen(sql_insert), &stmt, NULL);
    printf("begin write!\n");
 char myTime[30];
    for(i=10;i<50;i++)
     {
   sprintf(myTime, "2012-04-12 20:35:%d.30", i);
   int len = strlen(data);
  sqlite3_bind_int(stmt, 1, i);
  sqlite3_bind_text(stmt, 2, myTime, strlen(myTime), NULL);
  sqlite3_bind_blob(stmt, 3, data, len,  NULL);
        sqlite3_step(stmt);
        sqlite3_reset(stmt);
     }
  printf("finish write!\n");
    
   printf("begin search!\n");
    sqlite3_prepare(db, sql_select, strlen(sql_select), &stmt, NULL);
     i=0;
     while(SQLITE_DONE !=sqlite3_step(stmt))
    {
      int id = sqlite3_column_int(stmt, 0);
   char * time = (char *)sqlite3_column_text(stmt, 1);
   int bytes = sqlite3_column_bytes(stmt, 2);
   char *out_data = (char *)sqlite3_column_blob(stmt, 2);
   i++;
      printf("%d   %s\n", i, time);
    }
 
  printf("finish search!\n");
    sqlite3_finalize(stmt);
    sqlite3_close(db);
 }

int main()
{
 my_first_sqlite3_func();

 getchar();
 return 0;
}