oracle日期格式转换

1.字符串日期转date

	SELECT to_date(m.LASTCOM_TIME,'yyyy-MM-dd hh24:mi:ss') FROM  DR_DEVICEINFO_SMARTSOCKET m

2.date转字符串日期

	
	SELECT to_char(m.CREATE_TIME,'yyyy-MM-dd hh24:mi:ss') FROM  DR_DEVICEINFO_SMARTSOCKET m
 

3.获取当前毫秒值

SELECT
	TO_NUMBER ( sysdate - TO_DATE ( '1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS' ) ) * 24 * 60 * 60 * 1000 
FROM
DUAL

4.将字符串日期转毫秒值并进行计算

-- 最后通信时间+超时时间 >= 当前时间  在线


SELECT

sum(
	CASE 
	when (TO_NUMBER ( TO_DATE ( a.LASTCOM_TIME, 'YYYY-MM-DD HH24:MI:SS' ) - TO_DATE ( '1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS' ) ) * 24 * 60 * 60 *1000) + (a.TIMEOUT_TIME * 1000) 
	>= 
	(SELECT
		TO_NUMBER ( sysdate - TO_DATE ( '1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS' ) ) * 24 * 60 * 60 * 1000 
	FROM
	DUAL)
	then 1 
	else 0 end
) as online_count,

sum(
	CASE 
	when (TO_NUMBER ( TO_DATE ( a.LASTCOM_TIME, 'YYYY-MM-DD HH24:MI:SS' ) - TO_DATE ( '1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS' ) ) * 24 * 60 * 60 *1000) + (a.TIMEOUT_TIME * 1000) 
	< 
	(SELECT
		TO_NUMBER ( sysdate - TO_DATE ( '1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS' ) ) * 24 * 60 * 60 * 1000 
	FROM
	DUAL)
	then 1 
	else 0 end
) as offline_count

FROM
	DR_DEVICEINFO_SMARTSOCKET a
	left join DR_USER_PROFILE b on a.USER_PROFILE_ID=b.USER_PROFILE_ID
  where 1=1
	and b.PROVINCE_ID = '400000000000001'
	

a.LASTCOM_TIME在数据库存的是varchar，先转换成date再减去纪元日期得到 天数，再转换成毫秒值，再做一些逻辑计算