仅从Date Time获取比较时间

Question

SELECT
    TO_CHAR(s.start_time at time zone 'Asia/Singapore','HH24:MI:SS PM')  AS roster_starttime,
    TO_CHAR(c.time_server at time zone 'Asia/Singapore','HH24:MI:SS PM') AS Clock_TIme,
    TO_CHAR(((c.time_server at time zone 'Asia/Singapore')::time - (s.start_time at time zone 'Asia/Singapore')::Time ),'HH "Hours" MI "minutes"') AS lateness_minutes
FROM
    employee_info e
INNER JOIN
    shifts s ON s.employee_id = e.employee
INNER JOIN
    clock c ON c.shift = s.id

有人弄乱了班次的日期，但是时间是正确的，所以我只想从c.time_server和s.start_time获取时间。如果那天有人迟到了，减去它们就可以得到时间(lateness_minutes)。

以下是我在自己的终端上执行此操作的示例结果

​

​

这不是应该只有11分钟吗？如何解决此问题？

Answer 1

您可以简单地将timestamp转换为date或time，例如

SELECT 
  '2021-09-17 10:30:47'::time,
  '2021-09-17 10:30:47'::date,
  to_char('2021-09-17 10:30:47'::time,'HH "Hours" MI "minutes"');

   time   |    date    |       to_char           
----------+------------+---------------------
 10:30:47 | 2021-09-17 | 10 Hours 30 minutes
(1 Zeile)