mysql 查询时间戳格式化 和thinkphp查询时间戳转换

1、mysql语句

格式化时间戳

mysql> select pid,FROM_UNIXTIME(time,'%Y-%m-%d %H:%i:%s') as addtime from test;
+-----+---------------------+
| pid | addtime             |
+-----+---------------------+
|  10 | 2020-11-06 18:06:26 |
|  16 | 2020-11-06 18:06:44 |
|  17 | 2020-11-12 12:04:23 |
|  19 | 2020-11-12 12:04:33 |
|  51 | 2020-11-13 06:57:14 |
|  52 | 2020-11-13 09:32:11 |
|  53 | 2020-11-13 10:15:37 |
|  61 | 2020-11-16 09:42:31 |
|  66 | 2020-11-16 09:46:05 |
+-----+---------------------+
9 rows in set (0.20 sec)

时间格式变成时间戳

mysql> select  id,name,UNIX_TIMESTAMP(time) as addtime from test;  //数据库存的time格式是这样的 ：2021-02-27
+-----+------------+
| pid | addtime    |
+-----+------------+
|  10 | 1604657186 |
|  16 | 1604657204 |
|  17 | 1605153863 |
|  19 | 1605153873 |
|  51 | 1605221834 |
|  52 | 1605231131 |
|  53 | 1605233737 |
|  61 | 1605490951 |
|  66 | 1605491165 |
+-----+------------+
9 rows in set (0.20 sec)

2、thinkphp框架

格式化时间戳

$data = $model->field('id,title,FROM_UNIXTIME(time,"%Y-%m-%d %H:%i:%s") as addtime')->order('id desc')->select();

时间格式变成时间戳

$data = $model->field('id,title,UNIX_TIMESTAMP(time) as addtime')->order('id desc')->select();