13518219792
强转下吧 转成日期
创新互联公司是一家专业提供华龙企业网站建设,专注与做网站、网站设计、H5网站设计、小程序制作等业务。10年已为华龙众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。
cast(字段名 as date)
或者可以用substr截取
PostgreSQL中直接用两个date(或者timestamp)值相减，其返回的是一个interval值，再有该interval值取出天数转换成分钟或秒数，再加上interval中分钟(和秒数)部分的值就可以了。
示例SQL:
select interval_value, date_part('day', interval_value) as day_value, date_part('day', interval_value) * 24 * 60 + date_part('minute', interval_value) as minutes
from (
select (current_timestamp - to_timestamp('2013-08-21 13:23', 'yyyy-mm-dd hh24:mi')) as interval_value
) s;
## 在PostgreSQL 和 Hive中生成日期序列
### Postgresql实现日期序列
在postgresql中，有 generate_series(start_date, end_date, interval)函数来生成日期序列
```
select date(day) as day
from generate_series('2020-05-22'::timestamp, current_date, '1 day'::interval) as day
```
### Hive实现指定指定开始结束日期的日期序列
在Hive中，可以借助 posexplode(list)、datediff(end_date, start_date)来实现。
首先创建一个表名为calender，字段为day，类型为date，存入一个日期数值作为开始日期，比如2014-01-01。
```
CREATE TABLE default.calender (day DATE);
INSERT INTO TABLE default.calender VALUES(to_date('2014-01-01T00:00'));
```
借助 `datediff(end_date, start_date)`、`space(int_count)`、`split(list,seperator)`、`posexplode(list) `生成n个空格，然后split成list，posexplode将行转多列，同时返回index和value。
```
select date_add(day,idx) as new_day from default.calender
lateral view posexplode( split( space( datediff( current_date, to_date('2014-01-01T00:00:00') ) ), ' ') ) tt as idx, v;
```
中间过程解释：
比如：
```
select datediff('2020-06-30','2020-05-1'); -- 60
select split(space(datediff('2020-06-30','2020-05-1')),' ') -- 生成60个空格，然后split成list
```
index | value
--- | ---
0 | ' '
1 | ' '
2 | ' '
... | ...
59| ' '
#### Hive在日期序列表添加星期几
新增一列存放星期几
```
ALTER TABLE default.calender ADD COLUMNS(weekday STRING);
```
借助函数`datediff`，`pmod`就可以实现
datediff 是两个日期相减的函数
语法：`datediff(string enddate, string startdate)`
返回值: int
说明: 返回两个时间参数的相差天数。
pmod 是正取余函数
语法: `pmod(int a, int b),pmod(double a, double b)`
返回值: int double
说明: 返回正的a除以b的余数
选取一个日期为星期日的日期作为参照日期，这里我选取了2013-12-29
`pmod(datediff( date, '2012-01-01'), 7) `
返回值：int 0-6
0-6分别表示星期日-星期六
```
INSERT OVERWRITE TABLE default.calender
select date_add(day,idx) as `date`,
-- 0-6 分别代表星期日-星期六
case pmod(datediff(date_add(day,idx), to_date('2013-12-29T00:00:00')), 7)
when 0 then '星期日'
when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
when 4 then '星期四'
when 5 then '星期五'
when 6 then '星期六'
END as weekday
from default.calender
lateral view posexplode( split( space( datediff( to_date('2030-01-01T00:00:00') , to_date('2014-01-01T00:00:00') ) ), ' ') ) tt as idx, v;
```
### 补充：Hive实现缺失日期的补全
在统计一些daily的metrics的时候，通常使用group by，往往会存在某些日期没有数据从而导致最后的结果表的日期其实不是连续的齐全序列。
比如：
store_id | date | count
---- | ---- | -----
1 | 2020-04-02 | 45
2 | 2020-04-02 | 10
2 | 2020-04-03 | 10
1 | 2020-04-05 | 50
2 | 2020-04-06 | 10
1 | 2020-04-08 | 50
... | ...... | ....
针对这种情况，需要进行以下步骤拆解：
1. 按照store_id进行聚合，找出最小、最大日期
2. 此时，基于步骤1的CTE表进行基于每个store_id的最小、最大日期的日期序列补全
```
select t.store_id, date_add(t.min_date, idx) as `date`
from store_with_min_max_usage_date t
lateral view posexplode(split(space(datediff(t.max_date, t.min_date)),' ')) pe as idx, v
```
3. 将步骤2的结果与之前的agg聚合结果表进行`left join`，对`NULL`用`COALESCE(v, 0)`进行缺失值替换。
1、首先需要找到一个带日期字段的数据表。
2、接下来向表中插入日期值。
3、然后通过month函数获取月份即可，注意month里添加的是列名。
4、运行SQL语句以后发现日期中的月份已经被提取出来了。
5、另外还可以在Month中直接加GETDATE函数获取当前时间的月份。
