CODEWITHSUNDEEP
postgresql
feret12000
feret12000

Reputation: 51

postgresql query for hour minutes and seconds

Hi I am having a Postgresql query like below to calculate DateTime difference for {1} and {2} in minutes.

CAST(ROUND(EXTRACT(EPOCH from (({2}::timestamp) - ({1}::timestamp)))/60) AS INT)

I want to calculate the difference in hours, minutes and seconds displayed like:

3 hrs 31 minutes 42 secs

What manipulation do I need for displaying like above?

Upvotes: 5

Views: 13632

Answers (4)

Gugun Gumilar
Gugun Gumilar

Reputation: 29

Simple

SELECT
  EXTRACT(year FROM LOCALTIMESTAMP(0) - yourFieldTime)||' year '||
  EXTRACT(month FROM LOCALTIMESTAMP(0) - yourFieldTime)||' month '||
  EXTRACT(day FROM LOCALTIMESTAMP(0) - yourFieldTime)||' day '||
  EXTRACT(hour FROM LOCALTIMESTAMP(0) - yourFieldTime)||' hour '||
  EXTRACT(minute FROM LOCALTIMESTAMP(0) - yourFieldTime)||' minute '||
  EXTRACT(second FROM LOCALTIMESTAMP(0) - yourFieldTime)||' second '
AS full_time_as_you_wish FROM your_table;

Result

full_time_as_you_wish
---------------------------------
0 year 0 month 0 day 0 hour 0 minute 0 second

Upvotes: 0

Viettel Solutions
Viettel Solutions

Reputation: 1489

Try use this sql:

SELECT to_char(column2 - column1, 'DD" days "HH24" hours "MI" minutes "SS" seconds"');

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658927

The subtraction of two timestamp or timestamptz values produces an interval. (While subtracting two date values produces an integer!)
Details about date/time types in the manual.

The default text representation of an interval may be sufficient:

SELECT timestamp '2017-1-6 12:34:56' - timestamp '2017-1-1 0:0';

Result is an interval, displayed as:

5 days 12:34:56

If you need the format in the question, precisely, you need to specify how to deal with intervals >= 24 hours. Add 'days'? Or just increase hours accordingly?

@Nobody provided how to use to_char(). But add days one way or the other:

SELECT to_char(ts_col2 - ts_col1, 'DD" days "HH24" hours "MI" minutes "SS" seconds"');

Result:

05 days 12 hours 34 minutes 56 seconds

'days' covers the rest. There are no greater time units in the result by default.

Upvotes: 1

Samuel Yvon
Samuel Yvon

Reputation: 478

SELECT to_char((col1 - col0), 'HH24 hrs MI "minutes" SS "seconds"') FROM T1;

Here is a sqlfiddle : link

The to_char function takes an interval (an interval is the time span between two timestamps, and subtracting timestamps gives you an interval). It then takes a formatting, and you can apply pretty much what you want.

Formatting functions in PostgreSQL

Upvotes: 4

Related Questions