如果你操作數(shù)據(jù)庫時(shí)想通過時(shí)間加以限制，那么請以這樣的形式存儲時(shí)間：year-month-day hour：minute：second，給一個(gè)linux下的存儲方法：void *gettime(char name[])
{
struct tm *p;
//char name[512];
char c[5];
time_t t;
t=time(NULL);
p=localtime(&t);
sprintf(c,"%d",1900+p->tm_year);
strcat(name,c);
strcat(name,"-");
if(1+p->tm_mon<10)
strcat(name,"0");
sprintf(c,"%d",1+p->tm_mon);
strcat(name,c);
strcat(name,"-");
if(p->tm_mday<10)
strcat(name,"0");
sprintf(c,"%d",p->tm_mday);
strcat(name,c);
strcat(name," ");
if (p->tm_hour<10)
strcat(name,"0");
sprintf(c,"%d",p->tm_hour);
strcat (name,c);
strcat(name,":");
if(p->tm_min<10)
strcat(name,"0");
sprintf(c,"%d",p->tm_min);
strcat(name,c);
strcat(name,":");
if(p->tm_sec<10)
strcat(name ,"0");
sprintf(c,"%d",p->tm_sec);
strcat(name,c);
printf("current time is:%s\n",name);
}
時(shí)間被轉(zhuǎn)換成了字符串，然后存儲到數(shù)據(jù)庫里，之后如果想查某個(gè)時(shí)間之前的，或者某個(gè)時(shí)間之后的，或者某個(gè)時(shí)間區(qū)間，那么就要再次將字符串轉(zhuǎn)換成時(shí)間，兩個(gè)函數(shù)to_days,str_to_date。
（1）to_days
就像它的名字一樣，它只能轉(zhuǎn)換到每一天，就是說一天的時(shí)間字符串會被轉(zhuǎn)換成一個(gè)數(shù)，如
mysql> select to_days('2010-11-22 14:39:51');
+--------------------------------+
| to_days('2010-11-22 14:39:51') | +--------------------------------+ | 734463 | +--------------------------------+
mysql> select to_days('2010-11-23 14:39:51'); +--------------------------------+ | to_days('2010-11-23 14:39:51') | +--------------------------------+ | 734464 | +--------------------------------+
可以看出22日與23日的差別就是，轉(zhuǎn)換之后的數(shù)增加了1，這個(gè)粒度的查詢是比較粗糙的，可能不能滿足我們的查詢要求，那么就引入細(xì)粒度的查詢方法str_to_date。
（2）str_to_date
這個(gè)函數(shù)可以把字符串時(shí)間完全的翻譯過來，就很好用了。
mysql> select str_to_date("2010-11-23 14:39:51",'%Y-%m-%d %H:%i:%s');
+--------------------------------------------------------+
| str_to_date("2010-11-23 14:39:51",'%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2010-11-23 14:39:51 |
+--------------------------------------------------------+
我針對自己的數(shù)據(jù)庫的一個(gè)查詢操作
select str_to_date(detectResult.`rcvDetectTime`,'%Y-%m-%d %H:%i:%s') from detectResult where str_to_date(detectResult.`rcvDetectTime`,'%Y-%m-%d %H:%i:%s')>='2010-11-22 14:49:52' and str_to_date(detectResult.`rcvDetectTime`,'%Y-%m-%d %H:%i:%s')<='2010-11-22 15:27:52'
看一下結(jié)果吧
+---------------------------------------------------------------+ | str_to_date(detectResult.`rcvDetectTime`,'%Y-%m-%d %H:%i:%s') | +---------------------------------------------------------------+ | 2010-11-22 14:50:31 | | 2010-11-22 14:51:51 | | 2010-11-22 14:53:11 | | 2010-11-22 14:54:31 | | 2010-11-22 14:55:51 | | 2010-11-22 14:57:11 | | 2010-11-22 14:58:31 | | 2010-11-22 14:59:51 | | 2010-11-22 15:01:11 | | 2010-11-22 15:02:31 | | 2010-11-22 15:03:51 | | 2010-11-22 15:05:11 | | 2010-11-22 15:06:31 | | 2010-11-22 15:07:51 | | 2010-11-22 15:09:11 | | 2010-11-22 15:10:31 | | 2010-11-22 15:11:51 | | 2010-11-22 15:13:12 | | 2010-11-22 15:14:32 | | 2010-11-22 15:15:52 | | 2010-11-22 15:17:12 | | 2010-11-22 15:18:32 | | 2010-11-22 15:19:52 | | 2010-11-22 15:21:12 | | 2010-11-22 15:22:32 | | 2010-11-22 15:23:52 | | 2010-11-22 15:25:12 | | 2010-11-22 15:26:32 | | 2010-11-22 15:27:52 | +---------------------------------------------------------------+
這樣就可以按照時(shí)間進(jìn)行查詢了，對于實(shí)驗(yàn)結(jié)果的分析方便了很多。