从dt=2018-12-01T18:23:59.000Z转换为12/1/2018 13:23格式的DT-5小时(即'MM/dd/yyyy :mm‘)

Question

我写了以下查询：

select call_begin_date_time,
       from_unixtime(unix_timestamp(replace(call_begin_date_time,'T',' '),
       'yyyy-MM-dd hh:mm')-5*3600, 'MM/dd/yyyy hh:mm') as call_begin_dt_time_conv
FROM datawarehouse.charged_usage_fact
where partition_date = '201812' and universalid = '6002163910'
  and billing_start_date = '2018-12-01' and record_validity_flag = 'Valid'
limit 1000;

其中，call_begin_date_time的值为: 2018-12-03T18:05:36.000Z

上述查询的输出如下：

call_begin_date_time    ,   call_begin_dt_time_conv   
2018-12-01T23:03:46.000Z ,  12/01/2018 06:03   (wrong)
2018-12-01T22:32:33.000Z ,  12/01/2018 05:32  (wrong)
2018-12-01T16:32:35.000Z ,  12/01/2018 11:32 (right)
2018-12-02T17:01:27.000Z ,  12/02/2018 12:01  (right)
2018-12-02T17:01:35.000Z ,  12/02/2018 12:01  (right)
2018-12-02T17:07:42.000Z ,      12/02/2018 12:07 (right)
2018-12-02T17:12:56.000Z ,  12/02/2018 12:12 (right)
2018-12-02T17:02:11.000Z ,  12/02/2018 12:02 (right)
2018-12-02T17:06:20.000Z ,  12/02/2018 12:06 (right)

对于2018-12-01T23:03:46.000Z，我希望call_begin_dt_time_conv显示为'12/01/2018 18:03' (i.e 24 hour format)或'12/01/2018 06:03 PM' (i.e 12 hour format)。

Answer 1

在日期格式中使用HH而不是hh来获取24小时时间值

'MM/dd/yyyy HH:mm'