sql – How do I sample in the last second?

Question:

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 h2 and postgres ?

Answer:

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 h2 and 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.

Scroll to Top