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_date
FROM
    t_stu_info t

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