【数据库】关于日期的处理（以greenplum和postgresql和hive为例）

那个啥，本人菜鸟一只，如果有什么说错的地方还请大家批评指出！！

好，开始说正事，日期处理和判断是十分常见的，本文就自己使用的数据库，和hive数据仓库来说说，我使用到的一些日期的处理和判断，当然技术能力有限，我也很菜，所以如果有说错或者遗漏的还请大家多多包涵，望能批评指出，也让我的水平提高提高！

一、日期类型的选择与使用

hive：

那我先说hive的，hive很简单，直接用String（hive有这个数据类型）存日期，一般格式是这种——"20180917"，然后使用的时候，直接：

--第一种：（推荐）,加不加引号没什么区别，但是最好加下，因为字段类型是string
select 字段1,字段2 
from 数据库.表 
where date_id <= '20180901' and date_id <='20180917'

--第二种：（用between日期要最好加引号,曾经遇到过不加引号报错,然后时间别写反了,写反可能就没有日期）,其他就没什么要注意的了,hive的比较随意
select 字段1,字段2 
from 数据库.表 
where date_id between '20180901' and '20180917'

pg和gp数据库：

数据库存日期，我用过三种类型来存时间的值，int，varchar，timestamp，下面我会来说明这三种数据类型的使用区别。

1、int类型

int类型很简单（我最推荐这种），只需要：

select 字段1,字段2 
from 数据库.表 
where date_id <= 20180901 and date_id <= 20180917

但是也有缺陷，因为这样存的话，理论上我们只会存到具体的某一天，对于日更的数据无所谓，但是有些数据我们可能要记录一些准确的时间，例如，年月日时分秒都要记录，那么int好像就不太符合我们的需求，因此就需要用另外两种数据类型。

2、varchar类型

varchar类型，理论可以存任何东西啊，各种字符串只要长度够，怎么存都可以，理论上来说就可以存如下的类型：

2018-08-03 15:44:56.523791，但是我们要关注的是如何取数据！

--使用between或者>(大于号)和<(小于号)来限定where条件都可以，日期必须用引号引起，否则会报错
--ERROR:  operator does not exist: character varying >= integer
SELECT 字段1,字段2 
FROM 数据库.表名 
WHERE create_time BETWEEN '20180901' and '20180902'
--
WHERE create_time > '20180911' and create_time < '20180913' 

--如果想要查看比如某一天有多少数据量，就需要group by 日期，因此可能就需要group by日期
--完整日期字段1格式：2018-08-03 15:44:56.523791
--sql结果如下图
SELECT substr(create_time, 1, 10),count(1) 
FROM 数据库.表名 
GROUP BY substr(create_time, 1, 10) 
ORDER BY substr(create_time, 1, 10) desc

3、timestamp

其实timestamp才是数据库中正规的日期类型呀，他的范围判断就很简单了

但是这里一定要注意：字符串上的日期一定要是存在的日期，比如，'20180932'这种日期是不存在的，因此，如果timestamp来where这个日期的话，是会直接报错的（varchar类型不会报错），报错请看代码块

--时间必须用引号引起来否则会报错，然后也是大于小于号可以，between也可以
--> ERROR:  operator does not exist: timestamp without time zone >= integer
SELECT * FROM 数据库.表名
WHERE crawl_time > '20180912' and crawl_time < '20180913'
--WHERE crawl_time between '20180912' and '20180913'


--报错：
> ERROR:  date/time field value out of range: "20180932"
  LINE 1: ...nt WHERE crawl_time > '20180912' and crawl_time < '20180932'...
                                                               ^
  HINT:  Perhaps you need a different "datestyle" setting.

类型，大体上就介绍到这里，如果有遗漏欢迎提醒补充！

二、日期类型的转换

关键是很多时候，一些sql并不是手写的，举个例子，我现在传入一个日期，并且需要获取一个7天前的日期，问题来了，怎么转换？

先提一句，在gp中转换，直接通过两个冒号就可以转换，或者也可以尝试cast('20180801' as timestamp) ，通过函数来做转换

1、字段是int类型保存的

int转ts是最复杂的，我来解释下，首先int想直接转ts是不行的

SELECT 20180901::TIMESTAMP会直接报错

> ERROR:  cannot cast type integer to timestamp without time zone

 LINE 1: SELECT 20180901::TIMESTAMP

--第一步，我们需要先通过to_date这个函数，将int转换成日期类型，如下：
select to_date('20180901','yyyymmdd')

--第二步，要获取7天前的日期
SELECT to_date('20180901','yyyymmdd') - interval '7 day'
--获取一个月前的日期也行
SELECT to_date('201809','yyyymm') - interval '1 month'

--第三步，再把ts转成int，但是ts又不能直接转int，所以要先通过to_char这个函数转成字符串类型
select to_char((to_date('20180901','yyyymmdd')+ interval '2 day'),'yyyymmdd')

--第四步，再通过::int，直接将字符串转换成int类型
select to_char((to_date('20180901','yyyymmdd')+ interval '2 day'),'yyyymmdd')::int

--ok，确实是很麻烦，所以完整的sql如下

SELECT * 
FROM 数据库.表名 
WHERE date_id >= 20180901 and 
date_id <=  to_char((20180901:TIMESTAMP + interval '1 day'),'yyyymmdd')::int

2、字段是varchar类型保存的

--其实，只要掌握最麻烦的int，varchar原理其实是一样的
--用的时候，注意下等于号是否要加，int类型不加等于号大于0912，小于0913，就会没有数据，但是字符串是有数据的（我就不解释为什么了，这个其实能想明白）
SELECT * 
FROM 数据库.表名 
WHERE create_time > '20180912' and 
create_time < to_char(to_date('20180912', 'yyyymmdd') + interval '1 day','yyyymmdd')

3、字段是timestamp类型保存的

--这个就更简单了！
--比如要获取9月12号的数据，sql如下
SELECT * 
FROM 数据库.表名 
WHERE crawl_time > '20180912' and 
crawl_time < to_date('20180912', 'yyyymmdd') + interval '1 day'

4、date类型转换成int类型

--思路就是先将date类型转换成字符串类型，然后再转换成int类型
SELECT cast(to_char(pt_days,'YYYYMMDD')as INTEGER) as date_id FROM 表 limit 1;

5、timestamp转换成其他类型

--TO_TIMESTAMP传入int类型的ts时间，可以返回timestamptz类型：2017-12-06 08:17:10+08
--例如：
select TO_TIMESTAMP(1512519430)

--EXTRACT这个函数是抽取ts中的具体的部分
--extract （field from source）
--century(世纪)，decade(得到年份除10的值)，millennium(得到第几个千年，0-1000第一个，1001-2000第二个，2001-3000第三个)，year(年)，quarter(季度)，month(月份)，week(返回当前是几年的第几个周)，dow(返回当前日期是周几，周日：0，周一：1，周二：2，...)，day(本月的第几天)，doy(本年的第几天)，hour(小时)，min(得到时间中的分钟)，sec(返回时间中的秒)
--例如：
SELECT EXTRACT(min from cast('20181206' as TIMESTAMP))
SELECT EXTRACT(quarter from now())

三、举几个例子

ok，要说的概念差不多都说完了！接下来讲几个例子：

--注：gmt_modified 这个字段是 default now()
--求5天前到当前时间的数据
select * 
from 库名.表明 
WHERE gmt_modified >=  now() - interval '5 D'
 
--第二段是这样的，表A和表C中对应天的数据计算结束之后，并且确定表B中没有计算当天的数据之后，就返回要计算的日期，主要想说的是可以通过这种方式给定日期的范围to_char((SELECT now() - interval '5 D'),'yyyyMMdd') ，date_id是int类型
 
select *from (
select rc.date_id::VARCHAR as date_id,'${date_type}' as date_type  from ( 
select  date_id,status_flag from 表A where date_id>='${date_id}' and status_flag='Completed' ORDER BY  date_id  desc 
limit 9999 ) rc 
where  not  EXISTS (select date_id from 表B b where rc.date_id=b.date_id and rc.status_flag=b.status_flag) 
and  EXISTS (select date_id from  表C b where rc.date_id=b.date_id) 
 ) tb
where tb.date_id <=(to_char((SELECT now() - interval '5 D'),'yyyyMMdd'))
ORDER BY date_id;
 
--第三段是求数据库中的最新日期的前几天
select count(1),gmt_modified,next_gmt_modified 
from ( 
select to_char(freshtime,'yyyyMMdd') as gmt_modified,(to_char(((select max(freshtime) from 表A)  - interval '3 D'),'yyyyMMdd')) as next_gmt_modified  from 表A   
where freshtime is not null 
) tb 
where gmt_modified >=  next_gmt_modified
GROUP BY gmt_modified,next_gmt_modified ORDER BY 2 desc;
 
--但是第三段有个缺点，缺点是gmt_modified这个字段是更新数据的时间，一般来说不会做索引什么的，如果要求这个字段的最大值，很有可能查询速度奇慢无比，因此就需要用到其他方式来解决这个问题
--例如，如果该表有自增的主键id，那么我只要拿到id最大的那条数据的更新时间，就是最新的时间，而且实际运行，第三段sql在数据多的情况下，需要查询半小时。。。但是如下sql只需要不到1秒钟！！
select * from 表A where
freshtime >= 
(
select next_gmt_modified::TIMESTAMP from ( 
select to_char(freshtime,'yyyyMMdd'),to_char((freshtime  - interval '3 D'),'yyyyMMdd') as next_gmt_modified from 表A where 主键 in (select max(主键)from  表A )

嗯，大体上就这样吧，如果大家对于时间的处理有什么更好的方式，也可以给我留言！相互学习。

 

好了，本人菜鸡一个，如果有说的不对的地方，还望各位大神指点迷津！！