-- date类型时间
select sysdate from dual;--03-AUG-2404.33.40
-- timestamp类型时间
select systimestamp from dual;--03-AUG-2404.33.43.539173PM+00:00
date类型时间只能精确到秒,yyyy-mm-dd hh24:mi:ss
timestamp类型时间加入了小数秒 :yyyy-mm-dd hh24:mi:ss.ff
二、date 类型转换为 timestamp 类型
-- 使用to_timestamp将 date 或 字符型的date 转换为 timestamp
select to_timestamp(sysdate) from dual;--03-AUG-2412.00.00.000000AM
select to_timestamp('03-AUG-2404.33.43.539173','yyyy-mm-dd hh24:mi:ss.ff') from dual;--24-AUG-0304.33.43.539173AM-- 使用cast将date 转换为 timestamp
select cast(sysdate as timestamp) date_to_timestamp from dual;--03-AUG-2404.59.41.000000PM
三、timestamp 类型转换为date 类型
--1. 使用cast将 timestamp 转换为 date
select cast(systimestamp as date) from dual;--03-AUG-24
select cast(to_timestamp('03-AUG-2404.33.43.539173','yyyy-mm-dd hh24:mi:ss.ff') as date) timestamp_to_date from dual;--24-AUG-03--2. 使用 +0 将 timestamp 转换为 date
select systimestamp +0 from dual;--03-AUG-24
select to_timestamp('03-AUG-2404.33.43.539173','yyyy-mm-dd hh24:mi:ss.ff')+0 from dual;--24-AUG-03--3. 使用 to_char + to_date 将 timestamp 转换为 date
select to_char(1722582000000/(1000*60*60*24)+to_date('1970-01-0108:00:00','yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss' ) as datetime from dual;