温馨提示×

mysql如何时间戳排序

九三
589
2020-12-17 12:15:07
栏目: 云计算
亿速云mysql数据库,读写分离,安全稳定,弹性扩容,低至0.3元/天!! 点击查看>>

mysql如何时间戳排序

mysql中实现按时间戳排序的方法

1.首先,首先创建一个数据表;

create table DemoTable

(

`timestamp` varchar(100)

);

2.数据表创建好后,向表中插入时间戳数据;

insert into DemoTable values('06/22/2019 01:10:20 PM');

insert into DemoTable values('06/22/2019 12:00:27 PM');

insert into DemoTable values('06/22/2019 06:56:20 AM');

insert into DemoTable values('06/22/2019 07:10:11 AM');

3.时间戳数据插入后,查看表结构;

select *from DemoTable;

+------------------------+

| timestamp |

+------------------------+

| 06/22/2019 01:10:20 PM |

| 06/22/2019 12:00:27 PM |

| 06/22/2019 06:56:20 AM |

| 06/22/2019 07:10:11 AM |

+------------------------+

4 rows in set (0.00 sec)

4.通过执行以下命令对表中的时间戳进行排序;

select *from DemoTable ORDER BY STR_TO_DATE(`timestamp`,'%m/%d/%Y%h:%i:%s %p');

5.最后,时间戳排序好后,继续查看表结构,即可看到时间戳排序成功;

select *from DemoTable;

+------------------------+

| timestamp |

+------------------------+

| 06/22/2019 06:56:20 AM |

| 06/22/2019 07:10:11 AM |

| 06/22/2019 12:00:27 PM |

| 06/22/2019 01:10:20 PM |

+------------------------+

4 rows in set (0.00 sec)

亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>

推荐阅读:MySQL的Unix时间戳如何排序

0