PostgreSQL两个日期的时间差比较分钟/小时

凌霄玉阶非所愿

已于 2022-12-26 17:26:59 修改

阅读量5.7k

点赞数 2

分类专栏： Postgresql 数据库文章标签： sql 数据库

于 2022-12-26 17:09:46 首次发布

本文链接：https://blog.csdn.net/weixin_42064877/article/details/128446793

版权

数据库同时被 2 个专栏收录

43 篇文章 4 订阅

订阅专栏

Postgresql

13 篇文章 0 订阅

订阅专栏

-- 按分钟比较差值
select * from round(cast(date_part('epoch', to_timestamp(now()::text,'yyyy-mm-dd hh24:MI:SS') - to_timestamp('2022-10-08 10:10:11','yyyy-mm-dd hh24:MI:SS'))/60 as numeric ),1)
-- 按小时比较差值
select * from round(cast(date_part('epoch', to_timestamp(now()::text,'yyyy-mm-dd hh24:MI:SS') - to_timestamp('2022-10-08 10:10:11','yyyy-mm-dd hh24:MI:SS'))/60/60 as numeric ),1)

-- 当前日期按24小时显示
SELECT to_char(now(),'yyyy-mm-dd hh24:MI:SS')

以下为本人案例：

SELECT
  TO_CHAR(
    SUM (
    CASE
        WHEN (
          round(
            CAST ( date_part( 'epoch', to_timestamp( CM_0_2_6_1, 'yyyy-mm-dd hh24:MI:SS' ) - to_timestamp( CM_1_4_1, 'yyyy-mm-dd hh24:MI:SS' ) ) / 60 / 60 AS NUMERIC ),
            2
          ) 
        ) :: NUMERIC > 0 
        AND (
          round(
            CAST ( date_part( 'epoch', to_timestamp( CM_0_2_6_1, 'yyyy-mm-dd hh24:MI:SS' ) - to_timestamp( CM_1_4_1, 'yyyy-mm-dd hh24:MI:SS' ) ) / 60 / 60 AS NUMERIC ),
            2
          ) 
          ) :: NUMERIC < 1 THEN
          1 ELSE 0 
        END 
        ) :: NUMERIC / COUNT (*) * 100,
        'FM999990.00' 
      ) || '%' 
  FROM
    T_DSCC T,
    T_DSSUBMIT B 
  WHERE
    T.FCODE = B.FCODE 
    AND B.FSTATUS IN ( '3', '4' ) 
  AND B.FISDEL = '0' 
  AND B.ISSUBMIT = '1'