oracle/mysql/hive日期时间格式转换及常用计算方法对比总结

因为笔者最近写sql徘徊在mysql、oracle、hive之中，有些函数容易搞混。因此在这里作个简单的总结，加深一下自己的印象，避免每次都要百度一下。

 

一、获取系统时间

 

	sql语句	结果
oracle	select sysdate from dual;
mysql	select now(), sysdate(), current_date(), current_time(), current_timestamp();
hive	select current_date(), current_timestamp();

总结：

oracle里有1种方法：

1）sysdate关键字，注意这不是一个函数，因此不能加括号。返回的是完整日期时间格式。

mysql有5种方法：

1) now(),返回完整日期时间格式

2）也可以用sysdate(),但是mysql里的sysdate是函数，因此使用时需要加括号，返回完整日期时间格式。

3）current_date()，只返回日期

4）current_time(), 只返回时间

5）current_timestamp()，返回完整日期时间格式

hive有2种方法，且与mysql 相同：

1）current_date()，只返回日期

2）current_timestamp()，返回完整日期时间格式

 

二、时间格式转换

oracle时间格式转换函数主要是to_char(), to_date

mysql时间格式转换函数是date_formate()，str_to_date()

hive时间格式转换函数是from_unixtime(), unix_timestamp.

下表列出了oracle/mysql/hive的时间格式转化的相关函数。

	函数
oracle	to_char(日期，想要的格式)，返回字符串，若要获取英文月份或星期名加上'nls_date_language=American' to_date(字符串，字符串的格式），返回日期
mysql	date_format(日期/日期格式的字符串，想要的格式），返回字符串 str_to_date(字符串，字符串的格式），返回日期
hive	from_unixtime(unix时间戳，[想要的格式])，返回日期字符串，格式参数默认值yyyy-MM-dd HH:mm:ss unix_timestamp(日期字符串, [字符串的格式])，返回unix时间戳。格式参数默认值yyyy-MM-dd HH:mm:ss

注意：hive日期都是字符串的类型（'yyyy-MM-dd HH:mm:ss'），因此转换时要借助unix时间戳。如果要将一种日期字符串转换成另一种格式的日期字符串，需要用:

from_unixtime(unix_timestamp(日期字符串，该日期的格式),  想要的格式）。

 oracle，mysql，hive的格式支持类型见下表：

	oracle		mysql		hive
年	yyyy	四位年, 2019	%Y	四位年, 2019	yyyy
	yyy	三位年, 019
	yy	二位年, 19	%y	二位年, 19
月	mm	两位月, 11	%m (%c)	两位月，11	MM
	mon	简写月份, 11月（中）nov(英）	%b	简写月份，Nov
	month	全写月份，11月(中) november(英)	%M	月名，November
日	dd	当月第几天，28	%d %D	当月第几天，28 有英语后缀的日期(1st, 2nd, 3rd...)，28th	dd
	ddd	当年第几天，332	%j	年的天 (001-366)，332
星期	dy	星期缩写，星期四(中) thu（英）	%a	缩写的星期名字(Sun……Sat) ，Thu
	day	星期全写，星期四(中) thursday(英）	%W %w	星期全写，Thursday 周的第几天（0=星期日, 6=星期六），4
时	hh24	两位24小时制，17	%H	两位24小时制00-23，17	HH
	hh	两位12小时制，05	%h %	两位12小时制01-12，05	hh
			%k	非2位24小时制0～23，17
			%	非2位12小时制1～12，5
分	mi	两位60进制，23	%i	分钟，数值(00-59)，23	mm
秒	ss	两位60进制，19	%S %s	秒(00-59)，19	ss
			%p	AM 或 PM
			%T %r	时间,24 小时(hh:mm:ss)  时间,12 小时(hh:mm:ss [AP]M)
季度	q	一位，当年第几季度，4
年的周	ww	两位，当年第几周，48	%U %u	周 (00-53) 星期日是一周的第一天 周 (00-53) 星期一是一周的第一天
月的周	w	一位，当月第几周，4

 

三、获取年/月/日/时/分/秒/星期/周/季度

获取年/月/日/时/分/秒/周/季度，有两种方法：

1）通过上面所讲的格式转换方法，指定相应的格式。

2）用sql自带函数提取：extract(要提取的单位 from 日期)。不过只支持提取年/月/日。

	oracle			mysql	hive
oracle	to_char(日期，要提取的单位格式) extract(要提取的单位 from 日期)，只支持day/month/year			day(日期)
mysql	date_format(日期, 要提取的单位格式) day(日期) month(日期) year(日期) time(日期) week(日期) weekofyear(日期) weekofmonth(日期) quarter(日期)			month(日期)
hive	from_unixtime(unix_timestamp(日期字符串), 要提取的单位格式） day(日期字符串) month(日期字符串） year(日期字符串） hour(日期字符串) minute(日期字符串) second(日期字符串) weekofyea(日期字符串)			month(日期)

	oracle	mysql	hive
日	to_char(日期，'dd') extract (day from 日期)	date_format(日期, %d) day(日期)	from_unixtime(unix_timestamp(日期字符串), 'dd') date_format(日期字符串, 'dd') day(日期字符串)
月	to_char(日期，'mm') extract(month from 日期)	date_format(日期, %m) month(日期)	from_unixtime(unix_timestamp(日期字符串), 'MM') date_format(日期字符串, 'MM') month(日期字符串)
年	to_char(日期，'yyyy') extract(year from 日期)	date_format(日期, %Y) year(日期)	from_unixtime(unix_timestamp(日期字符串), 'yyyy') date_format(日期字符串, 'yyyy') year(日期字符串)
时	to_char(日期，'hh24')	date_format(日期, %H)	from_unixtime(unix_timestamp(日期字符串), 'HH') date_format(日期字符串, 'HH') hour(日期字符串)
分	to_char(日期，'mi')	date_format(日期, %i)	from_unixtime(unix_timestamp(日期字符串), 'mm') date_format(日期字符串, 'mm') minute(日期字符串)
秒	to_char(日期，'ss')	date_format(日期, %s)	from_unixtime(unix_timestamp(日期字符串), 'ss') date_format(日期字符串, 'ss') second(日期字符串)
星期	to_char(日期，'day')	date_format(日期, %W)
年的周	to_char(日期，'ww')	date_format(日期, %U) weekofyear(日期)，week(日期), yearweek(日期)	weekofyear(日期)
月的周	to_char(日期，'w')	weekofmonth(日期)
季度	to_char(日期，'q')	quarter(日期)

	oracle sql	结果
日	select to_char(to_date('2019-11-22 14:02:36'), 'dd') from dual; -- 方法1 select extract(day from to_date('2019-11-22 14:02:36')) from dual; -- 方法2	22
月	select to_char(to_date('2019-11-22 14:02:36'), 'mm') from dual; -- 方法1 select extract(month from to_date('2019-11-22 14:02:36')) from dual; -- 方法2	11
年	select to_char(to_date('2019-11-22 14:02:36'), 'dddd') from dual;-- 方法1 select extract(year from to_date('2019-11-22 14:02:36')) from dual;-- 方法2	2019
时	select to_char(to_date('2019-11-22 14:02:36'), 'hh24') from dual;-- 方法1	14
分	select to_char(to_date('2019-11-22 14:02:36'), 'mi') from dual;-- 方法1	02
秒	select to_char(to_date('2019-11-22 14:02:36'), 'ss') from dual;-- 方法1	36
星期	select to_char(to_date('2019-11-22 14:02:36'), 'day') from dual;-- 方法1	星期五
年的周	select to_char(to_date('2019-11-22 14:02:36'), 'ww') from dual;-- 方法1	47
月的周	select to_char(to_date('2019-11-22 14:02:36'), 'w') from dual;-- 方法1	4
季度	select to_char(to_date('2019-11-22 14:02:36'), 'q') from dual;-- 方法1	4

 

 

 

 

 

 

四、求时间间隔

求时间间隔可分为两种：

1）精确时间间隔：例如计算年龄时的周岁，不满一年的需要舍去。

2）虚时间间隔：我暂且按照虚岁的概念来叫这种入位时间隔叫虚时间间隔。

3.1 精确时间间隔

oracle: 直接日期相减，得到两个日期的间隔天数（非整型）。实际是两个日期的间隔秒数/86400。

	oracle
天数	floor((date2-date1))
月份数
年数
周数
季度数
小时数
分钟数
秒数

 

	oracle	mysql
天数	to_number(date1-date2)	datediff(date1,date2)
月份数		timestampdiff(freq, date1, date2) freq= {FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR}
年数
周数
季度数
小时数
分钟数
秒数

 

	oracle	mysql
天数	to_number(date2-date1)	datediff(date1,date2)
月份数		timestampdiff(freq, date1, date2) freq= {FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR}
年数
周数
季度数
小时数
分钟数
秒数