The MySQL date type and millisecond value are converted to each other.

The MySQL date type and millisecond value are converted to each other.

Sometimes the date type value needs to be converted to the millisecond value, and sometimes the millisecond value needs to be converted to the date, for more convenience, meet the Query Needs.

Create a new database table t_stu_info and insert data to the table.

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. convert a date to a millisecond Value

select UNIX_TIMESTAMP(t.stu_date) from t_stu_info t

2. Convert millisecond value to date

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