mysql datetime hive_时间函数对比：Oracle|Mysql|Hive

实际业务工作中，时间函数类型转换，以及次日留存，次三十日留存，一周的活跃用户等等，类似的需求应接不暇！对于新手，格式转换是特别费精力的事情！

今天特来复盘一下，那些用过的时间函数。学会了它，你就可以高效的开发代码了。

MYSQL篇

NOW()：返回当前日期和时间

SELECT NOW()

-> 2018-09-19 20:57:43

DATE(date|datetime)：提取date或datetime 的日期部分。

mysql>select date('2018-6-28')

+-------------------+

2018-06-28

mysql>select NOW()

2019-07-31 19:04:24

mysql>select CURDATE()

2019-07-31

ADDDATE(d,n)：计算起始日期 d 加上 n 天的日期

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);

->2017-06-25

MySQL 为日期减去一个时间间隔：date_sub()

select date_sub(now(), INTERVAL 1 day)

2019-07-30 19:30:44

CURDATE()：返回当前日期--current+date

SELECT CURDATE();

-> 2018-09-19

CURRENT_TIMESTAMP()：返回当前时间

SELECT CURRENT_TIMESTAMP()

-> 2018-09-19 20:57:43

DATEDIFF(d1,d2)：计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF('2001-01-01','2001-02-02')

-> -32

DAY(d)：返回日期值 d 的日期部分

SELECT DAY("2017-06-15");

-> 15

YEAR(d)：返回年份

SELECT YEAR("2017-06-15");

-> 2017

month(d):返回月份

CAST(x AS type)：转换数据类型

SELECT CAST("2017-08-29" AS DATE);

-> 2017-08-29

SELECT CAST(3.14 AS INT)

->3

select cast('1' as DOUBLE)

->1.0

CAST函数语法规则是：Cast(字段名 as 转换的类型 )，其中类型可以为：CHAR[(N)] 字符型

DATE 日期型

DATETIME 日期和时间型

DECIMAL float型

SIGNED int

TIME 时间型

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。date 参数是合法的日期。format 规定日期/时间的输出格式。

DATE_FORMAT(NOW(),'%m-%d-%Y')

12-29-2008

UNIX_TIMESTAMP(date)函数:返回日期date的UNIX时间戳

Hive篇

与传统数据库不同，Hive是为OLAP设计的数据仓库，它的数据文件存放在HDFS上，而元数据(MetaData)则存放在关系型数据库中(MySQL或Derby)，这使得它对数据的控制非常薄弱。

from_unixtime():转化 UNIX 时间戳(从 1970-01-01 00:00:00 UTC 到指定时间的秒数)到当前时区的时间格式；

select from_unixtime(1323308943,'yyyyMMdd') from lxw1234;

20111208

获取当前 UNIX 时间戳函数: unix_timestamp

hive> select unix_timestamp() from lxw1234;

1323309615

unix_timestamp(string date)：转换格式为"yyyy-MM-dd HH:mm:ss"的日期到 UNIX 时间戳。如果转化失败，则返回 0。

hive> select unix_timestamp('2011-12-07 13:01:03') from lxw1234;

1323234063

to_date()：返回日期时间字段中的日期部分。

hive> select to_date('2011-12-08 10:03:01') from lxw1234;

2011-12-08

year(string date)：返回日期中的年。

hive> select year('2011-12-08 10:03:01') from lxw1234;

2011

hive> select year('2012-12-08') from lxw1234;

2012

month (string date)：返回日期中的月份。

hive> select month('2011-12-08 10:03:01') from lxw1234;

12

day (string date)：返回日期中的天

hive> select day('2011-12-08 10:03:01') from lxw1234;

8

hive> select day('2011-12-24') from lxw1234;

24

weekofyear (string date)：返回日期在当前的周数。

hive> select weekofyear('2011-12-08 10:03:01') from lxw1234;

49

datediff()： 返回结束日期减去开始日期的天数。

hive> select datediff('2012-12-08','2012-05-09') from lxw1234;

213

date_add()：返回开始日期 startdate 增加 days 天后的日期。

hive> select date_add('2012-12-08',10) from lxw1234;

2012-12-18

date_sub()：返回开始日期 startdate 减少 days 天后的日期。

hive> select date_sub('2012-12-08',10) from lxw1234;

2012-11-28

STRING(yyyyMMdd)转TIMESTAMP：from_unixtime(unix_timestamp(date_id,'yyyyMMdd'))

TIMESTAMP转STRING(yyyyMMdd)：

from_unixtime(unix_timestamp(regist_time),'yyyyMMdd')还有一点特别值得注意：mm代表分钟，MM才是月份，所以年月日不要写成yyyymmdd，应该写成yyyyMMdd；

Oracle篇

Months_bewteen 两个日期相差的月数，多余天数以小数点位置显示，单位月

select months_between(sysdate+300,sysdate+200) from dual;

Last_day 本月的最后一天

select last_day(sysdate) from dual;

Add_months向指定日期中加上若干月数

select add_months(sysdate,1) from dual;

日期函数转字符串(敲代码时间2018-08-17 17：44：40)

select to_char(sysdate,'yyyy') from dual;--2018

select to_char(sysdate,'mm') from dual;--to_date is wrong，08

select to_char(sysdate,’dd') from dual;--17

select to_char(sysdate,’hh24‘) from dual;---只显示当前小时，17

select to_char(sysdate,’mi‘) from dual;--只显示当前分数，44

select to_char(sysdate,’ss‘) from dual;--只显示当前秒数，40

select to_char(sysdate,'yyyy—mm-dd hh24:mi:ss') from dual;--2018-08-17 17：44：40

select to_char(sysdate,‘ddd’) from dual;(1-365)中第229天

select to_char(sysdate,’dd‘) from dual;(1-31)中第17天

select to_char(sysdate,‘d’) from dual;(1-7)中第6天

select to_char(sysdate,‘day’) from dual;--星期五

select to_char(sysdate,‘ww’) from dual;当前日期在一年第33周

select to_char(sysdate,‘w’) from dual;当前日期在一月第3周

哈，终于整理完了，很早之前就想做了，拖到现在才花时间整理完。

参考文献