与时间相关的函数

时间函数在日常工作中用得很多，总结一波。以下在日常工作平台anyloader任务中常用

一、mysql

涉及到函数格式化的参数及含义

参数	含义
%Y	年，4位格式
%y	年，2位格式
%m	月，数字格式
%d	天，数据格式
%H	小时，24小时格式
%h	小时，12小时格式
%i	分
%s	秒
%T	时间，24小时（hh:mm:ss）

• str_to_date函数
  将字符型转换为指定格式的日期型数据，输出的格式为带横杠日期。

select str_to_date('2020/03/01','%Y/%m/%d');     //输出结果：2020-03-01
select str_to_date('2020/03/01','%Y/%m/%d %T');  //输出结果：2020-03-01 00:00:00

• DATE_FORMAT函数
  把日期格式化成字符串，这个函数解决了我实际需求去掉日期格式不要横杠问题。

select date_format('2020-03-01','%Y%m%d');     //输出结果：20200301
select date_format('2020-03-01','%y%m%d');     //输出结果：200301
select date_format('2020-03-01','%Y%m%d %T');  //输出结果：20200301 00:00:00

• from_unixtime
  把unix时间戳转换为日期格式。

select from_unixtime(1548648852,'%Y-%m-%d');    //输出结果：2019-01-28

• unix_timestamp
  把日期转换成unix时间戳，很多时候业务系统那边的数据都是时间戳格式的数据，我们无法直接使用日期来筛选数据，可以通过这个函数来转换成对应时间戳后筛选数据。

select unix_timestamp('2019-01-28');    //输出结果：1548604800

• date_sub
  把日期减去指定时间间隔，用来取前几天的数据，返回带横杠的日期格式数据。

select date_sub('20200304',interval 2 day);      //输出结果：2020-03-02
select date_sub('20200304',interval -2 day);     //输出结果：2020-03-06
select date_sub('20200304',interval 1 month);    //输出结果：2020-02-04
select date_sub('20200304',interval 1 week);     //输出结果：2020-02-26

• date_add
  把日期加去指定时间间隔，与date_sub是相反的函数，可通用，注意正负号，返回带横杠的日期格式数据。

select date_add('20200304',interval 2 day);      //输出结果：2020-03-06

• CURRENT_TIMESTAMP的用法
  mysql数据库中，当数据类型是timestamp，设置default默认值为CURRENT_TIMESTAMP时，则插入一条记录时，该字段会自动填充系统当前时间，如果还设置了ON UPDATE CURRENT_TIMESTAMP的话，则该字段还会根据这条记录的更新update命令重新设置为系统最新当前时间。

CREATE TABLE `dim_del_active_event` (
  `app_name` varchar(100) DEFAULT NULL COMMENT 'PKG包名例如:com.meizu.notepaper',
  `del_event_name` varchar(100) DEFAULT NULL COMMENT '计算活跃剔除埋点事件名',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建更新时间',
  `crash_valid` bigint(20) DEFAULT NULL COMMENT '是否包含进崩溃的启动次数计算(0:不包含/1或空:包含)',
  UNIQUE KEY `app_name` (`app_name`,`del_event_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

二、hive

hiveQL虽然与SQL是两种不同的语言，但语法基本很多都是共通的，在mysql能用的函数很多在hive都可以用。

• 取前三天的数据，并转换好不同的格式，注意嵌套使用是经常的

//先取日期3天前，再转成时间戳，最后转成指定的日期格式
from_unixtime(unix_timestamp(date_add('2020-03-04',-3),'yyyy-MM-dd'),'yyyyMMdd')

时间戳一般11位的话就是精确到秒为单位，13位是精确到毫秒为单位

• datediff日期相减函数

select datediff('2020-07-05','2020-06-15');  //返回20，注意日期格式认准- ，如果是/则无效，得使用格式转换

关于hive分区的语句

show partitions 表名   //查询表所有分区
alter table 表名 drop if exists partition(stat_date='20200229');   //删除指定的时间分区
alter table 表名 if not exists add partition(stat_date='20200229');  //添加指定的时间分区

一般工作中常用的是直接在创建表结构的同时建立好表分区
create table if not exists uxip.ads_direct_adv_game_summary_cp(
    fday                          bigint comment '数据日期'
    ,date_type                    bigint comment '日期类型'
    ,fpackage_name                string comment '快游戏包名'
    ...
) partitioned by (stat_date bigint)   --这里就建立好了分区字段
stored as orcfile;