I want to make a request like this:
SELECT count(*) FROM loan WHERE country = 'lv' AND created_at > current_timestamp - second(1)
How can I replace
second(1) with a valid expression so that it works in
In different SQL implementations, unfortunately, there is no order with the handling of dates and times. Try if your
h2 good at time intervals:
select now() - interval '1 second'
If it doesn't know how, then you can teach
postgresql simulate the behavior of
h2 creating a function like this:
create function second(int) returns interval immutable strict language sql as $$ select interval '1 second' * $1 $$;
And the query given in the question will work correctly.
A little misunderstanding, we need a request for
h2 too. Judging by the
h2 documentation , the
dateadd function is
dateadd , i.e. it is possible to construct a query like this:
SELECT count(*) FROM loan WHERE country = 'lv' AND created_at > DATEADD('SECOND', -1, current_timestamp)
PostgreSQL does not know such a function, but you can create a custom one and execute the same logic.
create function dateadd(text, int, timestamp) returns timestamp immutable strict language sql as $$ select $3 + concat($2, ' ', $1)::interval $$;
For the dimension,
second works for sure, and will work for other time dimensions for which
postgresql use the same names – for example,
month . If the name of the
postgresql time period does not know then there will be an error.