CODEWITHSUNDEEP
sqlpostgresqlconcatenation
Moehre
Moehre

Reputation: 151

How to change the format of a concat statement

I use a concat statement to select the start and endtime inside my postgresql database.

Code:

SELECT
     CASE
        WHEN d.id = 4 THEN (SELECT CONCAT (i.start_time,  i.end_time) FROM rdf_date_time i, rdf_condition_dt h WHERE ...) 
        ELSE ''
    END as timedomain
FROM ..
JOIN ...;

start_time: 0500(5 a.m.) end_time: 1300 (13p.m.)

The column timedomain looks like: 5002200

I will this format as the result: [(h5){8}] = [(start_time) {duration till end_time}] How can I convert it this way?

Upvotes: 0

Views: 93

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

to calculate interval you should just extract one time from the other, eg:

t=# select '0500'::time-'1300'::time diff;
 diff
-----------
 -08:00:00
(1 row)

If you column is of type integer, you have to prepare it first, like here:

t=# select lpad(500::text,4,'0')::time;
   lpad
----------
 05:00:00
(1 row)

So for you smth like below should work:

(lpad(i.end_time::text,4,'0')::time - lpad(i.start_time::text,4,'0')::time)

Upvotes: 1

Related Questions