CODEWITHSUNDEEP
postgresql
Baljot Singh
Baljot Singh

Reputation: 143

Concatenate date(timestamp) and hour(numeric) column in one column in psql

I have a table in a server where tx_date is in timestamp and hour is in numeric are different columns, therefore i need to combine both those columns to get one result in sql query.

these are the columns of my table.

tx_date            |hour|customer_obj_num|subscriber_obj_num|
-------------------|----|----------------|------------------|
2019-06-06 00:00:0 |9   |1260340723780   |1260341599747     |
2019-07-12 00:00:0 |9   |1260340917755   |1260341740838     |
2019-07-10 00:00:0 |18  |1261891161687   |1261891162660     |
2019-06-07 00:00:0 |14  |1260341590405   |1260488404515     |
2019-06-27 00:00:0 |10  |1263742570630   |1263742571046     |

Need results like this:

tx_date              |customer_obj_num|subscriber_obj_num|
---------------------|----------------|------------------|
2019-06-06 09:00:0   |1260340723780   |1260341599747     |
2019-07-12 09:00:0   |1260340917755   |1260341740838     |
2019-07-10 18:00:0   |1261891161687   |1261891162660     |
2019-06-07 14:00:0   |1260341590405   |1260488404515     |
2019-06-27 10:00:0   |1263742570630   |1263742571046     |

Upvotes: 1

Views: 851

Answers (2)

user330315
user330315

Reputation:

you can convert the hours to an interval using make_interval() and add that to the timestamp:

If hour is not an integer, you need to cast it. 

select t.tx_date + make_interval(hours => t.hour::int)
from the_table t;

If hour is a numeric value with decimals (and those decimals) should be honored as fractional hours, you can convert to seconds.

select t.tx_date + make_interval(secs => t.hour * 60 * 60)
from the_table t;

(and if hour doesn't contain fractional hours, then why is it a numeric, and not an integer)

Online example: https://rextester.com/RHYA54311

Upvotes: 1

karlis ssl
karlis ssl

Reputation: 143

You could cast tx_date as date and just add the hours to it

Select tx_date::date + interval '1 hour'*hour from table

Upvotes: 1

Related Questions