expressions including functions for values in interval type for postgres

Question

Is there a way in postgres db. to have expression inside interval type ? , e.g. I can execute a query -

select (timestamp '1970-01-01 00:00:00' + interval '1 second' * J0.C3) from T637 J0

Now , I need to be able to manipulate value of '1 second' dynamically , meaning '(1+2) seconds' fails for postgres , along with that the "value portion" value if it is getting derived via a function , and output of that function is some integer , I can't use that function in interval , e.g. interval func(args) seconds, how to achieve such dynamic "value portion" for "interval" in postgres db ?

The query that I build is generated dynamically by java code , ultimately the query that I want to build is - interval '((1-EXTRACT(day from (timestamp '1970-01-01 00:00:00' + interval '1 second' * J0.C3 - interval '28800 seconds'))))Day' , I understood that it could be broken in parts , like - interval '1 day' - interval(rest of part in days) , now "rest of part in days" again broken into interval '1 day' * EXTRACT(day from (...)) and so on , but I cannot traverse the already generated query back , to break into format of , interval '1 second' * (some integer value) — KanyaM
– KanyaM, Commented Apr 29, 2020 at 13:44

Haleemur Ali · Accepted Answer · 2020-04-28 13:35:07Z

1

interval '(1+2) seconds' is not a valid expression. However, interval '1 second' * 2 is valid.

So, to get the equivalent of '(m+n) seconds', one would would typically do either:

(interval '1 second' * m) + (interval '1 second' * n)

or

(m+n) * interval '1 second'

likewise, if there is a function that returns a numeric value and we want an interval of that magnitude (with units of seconds), the following could be used:

func(args) * (interval '1 second')

answered Apr 28, 2020 at 13:35

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

Comments

user330315 · Accepted Answer · 2020-04-28 13:36:40Z

1

For this kind of "interval building", make_interval() is quite handy:

make_interval(secs => (1 + 2) * some_column);

answered Apr 28, 2020 at 13:36

user330315

Collectives™ on Stack Overflow

expressions including functions for values in interval type for postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related