MySQL日期類型和毫秒值相互轉換，mysql相互轉換

MySQL日期類型和毫秒值相互轉換，mysql相互轉換

     有時需要將日期類型值轉換成毫秒值，有時也要將毫秒值轉換成日期，為了更方便，滿足查詢的需要。

     現在，建立一張資料庫表t_stu_info，並向表裡插入資料

use test;show tables;create table t_stu_info(     id int not null primary key,     stu_name varchar(20),     stu_date timestamp,     stu_age int(3),     stu_sex varchar(5));    insert into t_stu_info(id,stu_name,stu_date,stu_age,stu_sex) values ('1','zhangsan','2000-12-12 12:30:00','15','man');commit;select * from t_stu_info;

1、日期轉換成毫秒值

select UNIX_TIMESTAMP(t.stu_date) from t_stu_info t

2、毫秒值轉換成日期

SELECT     FROM_UNIXTIME(UNIX_TIMESTAMP(t.stu_date),            '%Y-%m-%d %h:%i:%s') AS stu_dateFROM    t_stu_info t

SELECT     FROM_UNIXTIME(UNIX_TIMESTAMP(t.stu_date),            '%Y-%m-%d') AS stu_dateFROM    t_stu_info t