大数据学习之路

SQL: CAST 类型转换

Cast(字段名 as 转换的类型 )，其中类型可以为：

CHAR[(N)] 字符型 DATE 日期型 DATETIME 日期和时间型 DECIMAL float型 SIGNED int TIME 时间型

示例

mysql> select * from capacity;
+----+----------+------------+---------------------+
| id | capacity | date       | time                |
+----+----------+------------+---------------------+
|  1 |      180 | 2019-10-26 | 2019-11-18 05:44:10 |
|  2 |      190 | 2019-10-27 | 2019-11-18 05:44:10 |
|  3 |      200 | 2019-10-28 | 2019-11-18 05:44:10 |
|  4 |      210 | 2019-10-29 | 2019-11-18 05:44:10 |
|  5 |      224 | 2019-10-30 | 2019-11-18 05:44:10 |
+----+----------+------------+---------------------+
1
2
3
4
5
6
7
8
9
10
mysql> select cast(time as signed) as date from capacity;
+----------------+
| date           |
+----------------+
| 20191118054410 |
| 20191118054410 |
| 20191118054410 |
| 20191118054410 |
| 20191118054410 |
+----------------+
1
2
3
4
5
6
7
8
9
10
mysql> select cast(time as char) as date from capacity;
+---------------------+
| date                |
+---------------------+
| 2019-11-18 05:44:10 |
| 2019-11-18 05:44:10 |
| 2019-11-18 05:44:10 |
| 2019-11-18 05:44:10 |
| 2019-11-18 05:44:10 |
+---------------------+
1
2
3
4
5
6
7
8
9
10
mysql> select cast(time as time) as date from capacity;
+----------+
| date     |
+----------+
| 05:44:10 |
| 05:44:10 |
| 05:44:10 |
| 05:44:10 |
| 05:44:10 |
+----------+
1
2
3
4
5
6
7
8
9
10
mysql> select cast(time as date) as date from capacity;
+------------+
| date       |
+------------+
| 2019-11-18 |
| 2019-11-18 |
| 2019-11-18 |
| 2019-11-18 |
| 2019-11-18 |
+------------+
1
2
3
4
5
6
7
8
9
10

reference