数据库日期处理对比

数据库日期处理对比

一、Oracle数据库

不同类型的转换

• DATE类型转TIMESTAMP类型： TO_TIMESTAMP(SYSDATE)

• TIMESTAMP类型转DATE类型：

  • TO_DATE(TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
  • SYSTIMESTAMP+ 0
  • CAST(SYSTIMESTAMP AS DATE)

• 字符串类型

  • 转DATE类型：TO_DATE('2021-11-19 19:00:00','YYYY-MM-DD HH24:MI:SS')
  • 转TIMESTAMP类型：TO_TIMESTAMP('2021-11-19 19:00:00','YYYY-MM-DD HH24:MI:SS')

• DATE、TIMESTAMP类型转字符串类型：TO_CHAR(SYSDATE/SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS')

日期的加减（加减同理）

• DATE

  • 当前时间减去1分钟的时间
    SELECT SYSDATE,SYSDATE - INTERVAL '1' MINUTE FROM DUAL;
  • 当前时间减去1小时的时间
    SELECT SYSDATE,SYSDATE - INTERVAL '1' HOUR FROM DUAL;
  • 当前时间减去1天的时间
    SELECT SYSDATE,SYSDATE - INTERVAL '1' DAY FROM DUAL;
  • 当前时间减去1月的时间
    SELECT SYSDATE,SYSDATE - INTERVAL '1' MONTH FROM DUAL;
  • 当前时间减去1年的时间
    SELECT SYSDATE,SYSDATE - INTERVAL '1' YEAR FROM DUAL;

• TIMESTAMP（DATE）

  • 对TIMESTAMP、DATE加减天、小时、分钟、秒的函数

  NUMTODSINTERVAL(x,c)
  x是一个数字，加减值，可以为小数和分数,如1，1/2, 0.05
  c是一个字符串，为加减单位 ， 常用的单位有 ('day','hour','minute','second')表明x的单位,这个函数把x转为interval day to second数据类型
  例：SELECT SYSTIMESTAMP,SYSTIMESTAMP+NUMTODSINTERVAL(3,'HOUR') AS RES FROM DUAL;
  

  • 对TIMESTAMP、DATE加减年、月的函数

   NUMTOYMINTERVAL(x,c)
   常用的单位有'year','month'
   例：SELECT SYSTIMESTAMP,SYSTIMESTAMP+NUMTOYMINTERVAL(1,'year') AS RES FROM DUAL;
  

日期相减得到时间间隔

• DATE类型日期相加减天、小时、分钟、秒

  SELECT CEIL((TO_DATE('2021-11-19 00:00:00' , 'YYYY-MM-DD HH24-MI-SS') - TO_DATE('2021-11-17 23:59:59' , 'YYYY-MM-DD HH24-MI-SS')))  相差天数 FROM DUAL;
  

• DATE类型日期相加减年、月

  SELECT (EXTRACT(YEAR FROM TO_DATE('2021-05-01','YYYY-MM-DD')) - EXTRACT(YEAR FROM TO_DATE('2020-11-30','YYYY-MM-DD')))  FROM DUAL;
  

• TIMESTAMP类型日期加减得到小时数

  SELECT TO_NUMBER(((TO_TIMESTAMP('2021-11-20 00:00:20','YYYY-MM-DD HH24:MI:SS')+0)-(TO_TIMESTAMP('2021-11-19 19:00:00','YYYY-MM-DD HH24:MI:SS')+0))*24) FROM DUAL ;
  

二、Greenplum数据库

不同类型的转换

• DATE类型转TIMESTAMP类型：

  • to_timestamp(to_char(current_date,'yyyy-MM-dd') ,'yyyy-MM-dd hh24:mi:ss')
  • current_date::timestamp

• TIMESTAMP类型转DATE类型：

  • to_date(to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-MM-dd')
  • current_timestamp::date

• 字符串类型

  • 转DATE类型：
    • TO_DATE('2021-11-19 19:00:00','YYYY-MM-DD HH24:MI:SS')
    • '2021-11-19 19:00:00'::date
    • date '2021-11-19 19:00:00'
  • 转TIMESTAMP类型：
    • TO_TIMESTAMP('2021-11-19 19:00:00','YYYY-MM-DD HH24:MI:SS')
    • '2021-11-19 19:00:00'::timestamp
    • timestamp '2021-11-19 19:00:00'

• DATE、TIMESTAMP类型转字符串类型：

  • TO_CHAR(SYSDATE/SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS')
  • SYSDATE/SYSTIMESTAMP::text

日期的加减（加减同理）

• date

  • 当前时间减去1分钟的时间
    select current_date,current_date- interval '1 min';
  • 当前时间减去1小时的时间
    select current_date,current_date- interval '1 hour';
  • 当前时间减去1天的时间
    select current_date,current_date- interval '1 day';
  • 当前时间减去1月的时间
    select current_date,current_date- interval '1 mon';
  • 当前时间减去1年的时间
    select current_date,current_date- interval '1 year';

• timestamp

  • 当前时间减去1分钟的时间
    select current_timestamp,current_timestamp - interval '1 min';
  • 当前时间减去1小时的时间
    select current_timestamp,current_timestamp - interval '1 hour';
  • 当前时间减去1天的时间
    select current_timestamp,current_timestamp - interval '1 day';
  • 当前时间减去1月的时间
    select current_timestamp,current_timestamp - interval '1 mon';
  • 当前时间减去1年的时间
    select current_timestamp,current_timestamp - interval '1 year';

日期相减得到时间间隔

• DATE类型日期相加减得到天数

  select ('2015-01-15 17:05'::timestamp)::date-('2015-01-13 15:01'::timestamp)::date;
  

• TIMESTAMP类型日期加减

  ① select ROUND(extract(epoch from(select ('2021-11-20 00:00:00'::timestamp - '2021-11-19 19:00:00'::timestamp)))/60/60) as hours
  ② select date_part('day','2015-01-15 17:05'::timestamp - '2015-01-13 15:01'::timestamp);