select to_date("2019-09-19 00:00:00");
输出:2019-09-19
- from_unixtime : 转化unix时间戳到当前时区的时间格式,格式可指定
select from_unixtime(1568778184, 'yyyy-MM-dd HH:mm:ss');
输出:2019-09-18 11:43:04
- unix_timestamp : 获取当前unix时间戳
select unix_timestamp();
输出:1568778184
select unix_timestamp('2019-09-18 12:43:04');
输出:1568781784
- year/month/day/hour/minute/second/weekofyear : 返回日期中指定的部分
select year('2019-09-18 12:43:04');
输出:2019
select month('2019-09-18 12:43:04');
输出:9
select day('2019-09-18 12:43:04');
输出:18
select hour('2019-09-18 12:43:04');
输出:12
select minute('2019-09-18 12:43:04');
输出:43
select second('2019-09-18 12:43:04');
输出:4
select weekofyear('2019-09-18 12:43:04');
输出:38
- datediff : 返回开始日期减去结束日期的天数
select datediff('2019-09-20','2019-09-18');
输出:2
select datediff('2019-09-20 01:00:00','2019-09-18 00:00:00');
输出:2
select datediff('2019-09-20','2019-09-23');
输出:-3
select date_sub('2019-09-20 12:00:00',4);
输出:2019-09-16
select date_sub('2019-09-20',4);
输出:2019-09-16
select date_add('2019-09-20 12:00:00',4);
输出:2019-09-24
select date_add('2019-09-20',4);
输出:2019-09-24
- from_unixtime + unix_timestamp Hive中yyyymmdd和yyyy-mm-dd日期之间的切换
select from_unixtime(unix_timestamp('20190920','yyyymmdd'),'yyyy-mm-dd') from dual;
select from_unixtime(unix_timestamp('2019-09-20','yyyy-mm-dd'),'yyyymmdd') from dual;
select IF(pmod(datediff('2019-09-20', '1920-01-01') - 3, 7)='0', 7, pmod(datediff('2019-09-20', '1920-01-01') - 3, 7));
输出:5
select trunc('2019-08-05','MM');
输出:2019-08-01
select date_sub(trunc('2019-08-01 00:00:00','MM'),1);
或者 select date_sub(trunc('2019-08-01','MM'),1);
输出:2019-07-31
select date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);
输出:2019-08-31