date - 如何在配置单元中将字符串格式转换为日期格式

Question

我的表中有时间戳和日期列。我的日期和时间戳列是字符串类型。我需要将其转换为 DATE 和 TIMESTAMP 格式。但我数据中的格式是 2/4/2017 3:03。因此，当我将其转换为 TIMESTAMP 数据类型时，它会给出 NULL。我们可以做些什么来解决这个问题？

这是我的示例日期和时间列数据。

2/3/2017 23:37
2/3/2017 23:37
2/3/2017 23:40
2/3/2017 23:50
2/3/2017 23:51
2/3/2017 23:53
2/3/2017 23:55
2/4/2017 0:08
2/4/2017 0:57

提前致谢

Answer 1

create table mytable (dt_tm string);

insert into mytable values
    ('2/3/2017 23:37')
   ,('2/3/2017 23:37')
   ,('2/3/2017 23:40')
   ,('2/3/2017 23:50')
   ,('2/3/2017 23:51')
   ,('2/3/2017 23:53')
   ,('2/3/2017 23:55')
   ,('2/4/2017 0:08' )
   ,('2/4/2017 0:57' )
;

---

select  dt_tm
       ,cast(from_unixtime(unix_timestamp(dt_tm,'dd/MM/yyyy HH:mm'),'yyyy-MM-dd 00:00:00') as timestamp)

from    mytable
;

---

+----------------+---------------------+
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:40 | 2017-03-02 00:00:00 |
| 2/3/2017 23:50 | 2017-03-02 00:00:00 |
| 2/3/2017 23:51 | 2017-03-02 00:00:00 |
| 2/3/2017 23:53 | 2017-03-02 00:00:00 |
| 2/3/2017 23:55 | 2017-03-02 00:00:00 |
| 2/4/2017 0:08  | 2017-04-02 00:00:00 |
| 2/4/2017 0:57  | 2017-04-02 00:00:00 |
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:40 | 2017-03-02 00:00:00 |
| 2/3/2017 23:50 | 2017-03-02 00:00:00 |
| 2/3/2017 23:51 | 2017-03-02 00:00:00 |
| 2/3/2017 23:53 | 2017-03-02 00:00:00 |
| 2/3/2017 23:55 | 2017-03-02 00:00:00 |
| 2/4/2017 0:08  | 2017-04-02 00:00:00 |
| 2/4/2017 0:57  | 2017-04-02 00:00:00 |
+----------------+---------------------+