greenplum时间转换

Greenplum日期时间转换

时间相减

• 返回两个时间差-秒:

•   SELECT date_part('epoch', '2020-04-20 12:00:00'::TIMESTAMP -'2019-04-20 11:30:10'::TIMESTAMP)::NUMERIC;
  

• 返回对应秒相减:

•   SELECT date_part('second', '2020-04-20 12:00:00'::TIMESTAMP -'2019-04-20 11:30:10'::TIMESTAMP)::NUMERIC;
  

• 返回对应分钟相减:

•   SELECT date_part('minute', '2020-04-20 12:00:00'::TIMESTAMP -'2019-04-20 11:30:10'::TIMESTAMP)::NUMERIC;
  

• 返回对应小时相减:

•   SELECT date_part('hour', '2020-04-20 12:00:00'::TIMESTAMP -'2019-04-20 11:30:10'::TIMESTAMP)::NUMERIC;
  

• date_part时间相减

•   date_part('epoch', starttime::TIMESTAMP -starttime::TIMESTAMP)::numeric
  

获取对应的时间数据值

• 获取对应的year quarter month week day

•   select extract (year from timestamp '2020-04-19 22:18:00');
  

时间转换

• String To Date

•   select to_date('20200420' , 'yyyymmdd') ;
  

•   '2020-04-03 12:40:00' ::TIMESTAMP
  

•   to_timestamp('2020-04-20 00:56:00' , 'yyyy-mm-dd hh24:mi:ss') 
  

•   to_timestamp('20200420005600' , 'yyyymmddhh24miss') 
  

•   to_char (createtime ,'yyyymmdd')