add time interval with value from selected column in postgres

Question

I am new in Postgres and want to know if there is a better way to solve time interval problem in Postgres.

In MySQL i have:

Select STR_TO_DATE(start_time - interval (tkc + tct) second + interval 3 hour, '%Y-%m-%d %H:%i:%s') as start_time 
from table

For Postgres I found and wrote query as:

select start_time - (interval  '1 second' * (tkc + tct)) + interval  '3 hour' 
from table

The Postgres syntax you suggest above is not what I would use, but I believe it is SQL standard. Given the two example queries you have posted are not the same, it is difficult to suggest improvements or alternatives. You may want to include query output and/or specific information on what you would like to see differently. — xzilla
– xzilla, Commented Jul 13, 2018 at 21:02
I'm assuming you're aware that the two examples above don't do exactly the same thing (they would give a different result value). There's several different different ways to express intervals, In postgresql, none of them are significantly better than what you have used. — Jasen
– Jasen, Commented Jul 14, 2018 at 10:07
There is no other way in Postgres to achieve this. If you want a more compact expression in the select list, you could write a SQL function to do this — user330315
– user330315, Commented Jul 16, 2018 at 7:41
@a_horse_with_no_name i will look into that. Thanks :) again. — mysticBliss
– mysticBliss, Commented Jul 16, 2018 at 7:58

mysticBliss · Accepted Answer · 2018-08-16 11:29:48Z

4

Thanks for all the answers. Wonderful to be part of community.

I was able to solve this as below.

select start_time - ((tkc + tct)|| ' seconds')::interval + interval '3 hour' as start_time from table

answered Aug 16, 2018 at 11:29

mysticBliss

58413 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1