0

I have a table in Postgres as follows:

| 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 and end_time are stored as varchar. Format is 'yyyy-mm-dd hh24:mi:ss.ms' (ISO format).
duration has been calculated as end_time - start_time. Format is hh:mi:ss.

I need result table output as follows:

| 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 and end need to contain only the mm/dd/yyyy portion of start_time and end_time respectively.

duration_minutes should calculate total duration in minutes (eg, if duration is 1:02:54, duration in minutes should be 62 which is 60+2)

How can I do this using SQL?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Symphony
  • 1,655
  • 4
  • 15
  • 22
  • 1
    Use the appropriate data type. `varchar` isn't appropriate here. – sticky bit May 15 '18 at 21:06
  • 1
    Why on earth are you storing timestamps as varchar? That's a really bad idea –  May 15 '18 at 21:07
  • @ a_horse_with_no_name : I will store it as datetime – Symphony May 15 '18 at 21:08
  • There is no `datetime` in Postgres. `timestamp` or `timestamptz`. https://stackoverflow.com/a/9576170/939860. And `duration` should probably be type `interval`. Shall we keep assuming `varchar` input or `timestamp` / `interval`? And do you need the `T` and `Z` decorators in your result? Those are optional in ISO format. – Erwin Brandstetter May 15 '18 at 22:05

2 Answers2

1

Based in varchar input, this query produces your desired result, exactly:

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;

Major points:

  • Use timestamp and interval instead of varchar to begin with.
    Or do not store the functionally dependent column duration at all. It can cheaply be computed on the fly.

  • For display / a particular text representation use to_char().
    Be explicit and do not rely on locale settings that may change from session to session.
    The FM pattern modifier is for (quoting the manual):

    fill mode (suppress leading zeroes and padding blanks)

  • extract (epoch FROM interval_tpe) produces the number of contained seconds. You want to truncate fractional minutes? Integer division does just that, so cast to int like demonstrated. Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @ Erwin Brandstetter : I used `extract(epoch FROM duration::interval)::int AS duration_seconds` and got seconds. But how can I capture the fraction milliseconds – Symphony May 20 '18 at 13:03
  • @Symphony: The cast to integer *rounds* fractional seconds. Drop the cast to get a `double precision` number with up to 6 fractional digits: `extract(epoch FROM duration::interval) AS seconds_with_fractions`. But your duration in the example is rounded already, so you need to recalculate duration to get fractional units. See: *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=5c645066246021738550de0866819ea0)* – Erwin Brandstetter May 21 '18 at 13:56
0

The following appears to do what you want:

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)

If you want the dates in a particular format, then use to_char().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786