oracle group by cast(timestamp field) issue



PUBLISHTIMESTAMP  and LASTUPDDTTM  are both timestamp type.

 (cast(A.LASTUPDDTTM AS DATE) - cast(A.PUBLISHTIMESTAMP AS DATE))*24*60*60 can tell the seconds between the two fields.

The interesting thing is when I use the below SQL statement, which can not get the expected results that I need.

select count(*),mydate from( SELECT cast(A.LASTUPDDTTM AS DATE) MyDate
  from TableName a
  where  (cast(A.LASTUPDDTTM AS DATE) - cast(A.PUBLISHTIMESTAMP AS DATE))*24*60*60>5 order by cast(A.LASTUPDDTTM AS DATE))
  group by mydate
  UNION ALL
 select count(*),mydate from( SELECT cast(A.LASTUPDDTTM AS DATE) MyDate
  from TableName  a
  where  (cast(A.LASTUPDDTTM AS DATE) - cast(A.PUBLISHTIMESTAMP AS DATE))*24*60*60<=5 order by cast(A.LASTUPDDTTM AS DATE))
  group by mydate

Must use to_char to convert the cast(A.LASTUPDDTTM AS DATE) to char, then we can group by MyDate field. The reason is that using group by cast(A.LASTUPDDTTM AS DATE) , which is not using the casted value as group by clause.

select count(*),mydate from( SELECT to_char(cast(A.LASTUPDDTTM AS DATE)) MyDate
  from TableName a
  where  (cast(A.LASTUPDDTTM AS DATE) - cast(A.PUBLISHTIMESTAMP AS DATE))*24*60*60>5 order by cast(A.LASTUPDDTTM AS DATE))
  group by mydate
  UNION ALL
 select count(*),mydate from( SELECT to_char(cast(A.LASTUPDDTTM AS DATE)) MyDate
  from TableName  a
  where  (cast(A.LASTUPDDTTM AS DATE) - cast(A.PUBLISHTIMESTAMP AS DATE))*24*60*60<=5 order by cast(A.LASTUPDDTTM AS DATE))
  group by mydate

conclusion:

oracle 两个时间相减默认的是天数

oracle 两个时间相减默认的是天数*24 为相差的小时数

oracle 两个时间相减默认的是天数*24*60 为相差的分钟数

oracle 两个时间相减默认的是天数*24*60*60 为相差的秒数

1, if the field is timestamp type, then use cast(timestamp_file as date),then use the below SQL statement to get the days,hour, minutes, seconds etc.

获取两时间的相差秒数 :
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;

select (cast(timestamp_fiedl AS DATE) - cast(timestamp_field AS DATE))*24*60*60 from table

获取两时间的相差分钟数 :
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;

select (cast(timestamp_fiedl AS DATE) - cast(timestamp_field AS DATE))*24*60 from table

获取两时间的相差小时数 :
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;

select (cast(timestamp_fiedl AS DATE) - cast(timestamp_field AS DATE))*24 from table