mysql的時(shí)間轉(zhuǎn)換問(wèn)題

1、UNIX時(shí)間戳轉(zhuǎn)換為日期用函數(shù)： FROM_UNIXTIME()

select FROM_UNIXTIME(1156219870);

2、日期轉(zhuǎn)換為UNIX時(shí)間戳用函數(shù)： UNIX_TIMESTAMP()

Select UNIX_TIMESTAMP('2006-11-04 12:23:00′);

3、日期時(shí)間格式函數(shù)：DATE_FORMAT() 

例：mysql查詢當(dāng)天的記錄數(shù)：

$sql="select * from message Where DATE_FORMAT(FROM_UNIXTIME(chattime),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') order by id desc";

當(dāng)然大家也可以選擇在PHP中進(jìn)行轉(zhuǎn)換UNIX時(shí)間戳轉(zhuǎn)換為日期用函數(shù)： date()

date("Y-m-d H:i:s", 1156219870);

日期轉(zhuǎn)換為UNIX時(shí)間戳用函數(shù)：strtotime()

strtotime("2010-03-24 08:15:42");

實(shí)例：

/*

周五 0點(diǎn)至12點(diǎn) 注冊(cè)醫(yī)生信息（id,姓名、醫(yī)院、科室、職稱）

*/

select unix_timestamp("2011-09-02 00:00:00"),unix_timestamp("2011-09-02 12:00:00");

select us.uid as "ID",us.username as "用戶名",usf.workunit as "所在醫(yī)院",usf.jobtitle as "職稱",usf.kee as "一級(jí)科室",usf.keee as "二級(jí)科室",usf.keeee as "三級(jí)科室" from uchome_space us,uchome_spacefield usf where us.uid=usf.uid   and dateline between unix_timestamp("2011-09-02 00:00:00") and unix_timestamp("2011-09-02 12:00:00") and workunit !="" order by dateline desc;