在Amazon Athena创建表时转换时间戳并设置datetime类型

刚好处理过类似的Athena日期格式问题，给你两个可行的方案，既能把converteddate转为日期时间类型，又能输出你想要的yyyy-MM-dd HH:mm:ss格式：

方案一：修改表结构，直接存储为timestamp类型 #

你的源数据是标准的ISO 8601格式（带T的日期字符串），Athena的timestamp类型可以直接识别这种格式，所以只需要调整建表语句的字段类型即可：

CREATE EXTERNAL TABLE IF NOT EXISTS test.test_table ( 
  `converteddate` timestamp, 
  `userid` string 
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'serialization.format' = ',', 
  'field.delim' = ',' 
) LOCATION 's3:XXXX' 
TBLPROPERTIES ('has_encrypted_data'='false',"skip.header.line.count"="1");

建表完成后，查询时用date_format函数格式化输出，替换掉T：

SELECT 
  date_format(converteddate, 'yyyy-MM-dd HH:mm:ss') AS converteddate,
  userid
FROM test.test_table;

方案二：不修改原表，查询时动态转换 #

如果不想重新建表，也可以在查询时先把字符串转成timestamp，再格式化：

SELECT 
  date_format(cast(converteddate AS timestamp), 'yyyy-MM-dd HH:mm:ss') AS converteddate,
  userid
FROM test.test_table;

要是担心格式识别出问题，还可以用parse_datetime明确指定解析规则：

SELECT 
  date_format(parse_datetime(converteddate, 'yyyy-MM-dd\'T\'HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss') AS converteddate,
  userid
FROM test.test_table;

两种方案最终都能返回你想要的结果：

converteddate       | userid 
---------------------|--------
2017-11-29 05:00:00 | 00001 
2017-11-27 04:00:00 | 00002 
2017-11-26 03:00:00 | 00003 
2017-11-25 02:00:00 | 00004 
2017-11-24 01:00:00 | 00005

内容的提问来源于stack exchange，提问作者Data_101