从日期时间中提取日期并从持续时间计算总分钟数

Question

我在Postgres中有一个表格如下：

| id | start_time               | end_time                 | duration |
|----|--------------------------|--------------------------|----------|
| 1  | 2018-05-11T00:00:20.631Z | 2018-05-11T01:03:14.496Z | 1:02:54  |
| 2  | 2018-05-11T00:00:04.877Z | 2018-05-11T00:00:14.641Z | 0:00:10  |
| 3  | 2018-05-11T01:03:28.063Z | 2018-05-11T01:04:36.410Z | 0:01:08  |
| 4  | 2018-05-11T00:00:20.631Z | 2018-05-11T02:03:14.496Z | 2:02:54  |

start_time和end_time存储为varchar。格式为'yyyy-mm-dd hh24:mi:ss.ms'(ISO 格式)。
duration已计算为end_time - start_time。格式为hh:mi:ss.

我需要结果表输出如下：

| id | start_time               | end_time                 | duration | start     | end       | duration_minutes |
|----|--------------------------|--------------------------|----------|-----------|-----------|------------------|
| 1  | 2018-05-11T00:00:20.631Z | 2018-05-11T01:03:14.496Z | 1:02:54  | 5/11/2018 | 5/11/2018 | 62               | -- (60+2)
| 2  | 2018-05-11T00:00:04.877Z | 2018-05-11T00:00:14.641Z | 0:00:10  | 5/11/2018 | 5/11/2018 | 0                |
| 3  | 2018-05-11T01:03:28.063Z | 2018-05-11T01:04:36.410Z | 0:01:08  | 5/11/2018 | 5/11/2018 | 1                |
| 4  | 2018-05-11T00:00:20.631Z | 2018-05-11T02:03:14.496Z | 2:02:54  | 5/11/2018 | 5/11/2018 | 122              | -- (2X60 +2)

start和end分别只需要包含start_time和end_time的mm/dd/yyyy部分。

duration_minutes应该以分钟为单位计算总持续时间(例如，如果持续时间为 1：02：54，则以分钟为单位的持续时间应为 62，即 60+2)

如何使用 SQL 执行此操作？

Answer 1

根据varchar输入，此查询将生成所需的结果，恰好：

SELECT *
, to_char(start_time::timestamp, 'FMMM/DD/YYYY') AS start
, to_char(end_time::timestamp  , 'FMMM/DD/YYYY') AS end
, extract(epoch FROM duration::interval)::int / 60 AS duration_minutes
FROM   tbl;

要点：

• 使用timestamp和interval而不是varchar开头 .
  或者根本不存储功能相关的列duration。它可以廉价地即时计算。

• 对于显示/特定文本表示，请使用to_char().
  明确，不要依赖于可能因会话而异的区域设置。
  FM模式修饰符用于(引用手册)：

  填充

  模式(取消前导零和填充空白)

• extract (epoch FROM interval_tpe)生成包含的秒数。您想截断小数分钟数吗？整数除法就是这样做的，所以像演示的那样投射到int。相关：

  • 使用时区获取时间之间的分钟差异

Answer 2

以下内容似乎可以执行您想要的操作：

select v.starttime::timestamp::date, v.endtime::date,
extract(epoch from v.endtime::timestamp - v.starttime::timestamp)/60
from (values ('2018-05-11T00:00:20.631Z', '2018-05-11T01:03:14.496Z')) v(starttime, endtime)

如果希望日期采用特定格式，请使用to_char()。