0

Trying to determine the time difference between two timestamp fields in postgres in HH24:MI:SS format, i.e.

date_started and date_completed

that have the following data:

date_started = 12/11/2021 09:11:00
date_completed = 12/11/2021 09:19:00

Using the following query:

select to_char(AGE(date_completed, date_started),'hh24:mi:ss') as "time_diff"
from my_table

returns the following value: 00:07:59

Notes: both these fields have a data type of: timestamp without timezone

My question is, why is this not actually returning 00:08:00 seeing that it is exactly, 8 minutes difference?

ArthurJ
  • 777
  • 1
  • 14
  • 39
  • 2
    What is the datatype of the date columns? Particularly, do they have fractions of seconds? Try `select to_char(date_started, 'hh24:mi:ss.us'), to_char(date_completed, 'hh24:mi:ss.us')` to see their microseconds part. – Bohemian Nov 11 '21 at 22:48
  • This works for me on v14, FYI. `select to_char(age('2021-12-11 09:19:00', '2021-12-11 09:11:00'),'hh24:mi:ss')` – ps2goat Nov 11 '21 at 22:49
  • Updated question to include data types - `timestamp without timezone` – ArthurJ Nov 11 '21 at 22:52
  • 1) Why not just do: `select '12/11/2021 09:19:00'::timestamp - '12/11/2021 09:11:00'::timestamp ; 00:08:00`? 2) Where are you seeing the values `12/11/2021 09:11:00` and `12/11/2021 09:19:00`? Are you sure they are not being rounded by the client? In `psql` do a `SELECT` for the fields and see what is actually being stored? – Adrian Klaver Nov 11 '21 at 22:57

1 Answers1

1

Solved my issue using the following:

select to_char(AGE(DATE_TRUNC('second', date_completed::timestamp), DATE_TRUNC('second', date_started::timestamp)),'hh24:mi:ss') as "time_diff"
from my_table

Reference SO: Discard milliseconds part from timestamp

Just want to also acknowledge @Bohemian for their input in assisting me to solve this issue wrt microseconds.

ArthurJ
  • 777
  • 1
  • 14
  • 39
  • So your data where not `12/11/2021 09:11:00` and `12/11/2021 09:19:00`. That would have been a useful piece of information to convey in your question. – Adrian Klaver Nov 12 '21 at 15:45
  • @AdrianKlaver - apparently not but at the time of posting my question, I wasn't aware of the microseconds component which `Bohemian` pointed me in the right direction. – ArthurJ Nov 15 '21 at 08:43