sql - 从 dt=2018-12-01T18:23:59.000Z 到 dt 的 Hive 日期转换 - 5 小时,格式为 12/1/2018 13:23(即 'MM/dd/yyyy hh:mm' )
问题描述
我写了以下查询:
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)
。
解决方案
在您的日期格式中使用HH
而不是hh
获取 24 小时时间值
'MM/dd/yyyy HH:mm'
推荐阅读
- python - python中的'BadZipFile:中央目录的错误幻数'错误
- powerbi - 比较 PowerBI 同一张表中的 2 行
- json - Jolt Conversion - 在列表中迭代列表并形成单个列表
- bash - 守护进程 lxd-client 客户端未运行
- r - R - terra::distance() 等效于 raster::gridDistance(..., origin = x, omit = y)
- reactjs - 隐藏呈现选项卡的组件
- python - 如何根据月份列值计算列的累积总和
- docker - 在 Docker 映像中使用 Azure CLI 时出现问题
- pandas - Hto根据重复的行将pandas df转换为dict(行必须是键)
- swift - Why are the days in the .inline style UIDatePicker not being localised when applying a locale to the datpicker?