Interval (Days) in Postgresql with Two Parameters

Interval (days) in PostgreSQL with two parameters

SELECT  *
FROM    mytable
WHERE   future_date BETWEEN CURRENT_DATE + '10 days'::INTERVAL AND CURRENT_DATE + '30 days'::INTERVAL

Creating N days interval function in postgresql

You are overcomplicating things. LATERAL is not required and date_trunc('day', ..) is the same as casting to date.

To create an interval based on a variable, the easiest way is to use make_interval()

create function get_n_day_group_dates(most_recent_day date, minimum_day date, n_day_group_interval varchar)
returns table (n_day_group_dates date)
as $body$
  SELECT g.dt::date
  FROM generate_series(most_recent_day,
                       minimum_day,
                       make_interval(days => n_day_group_interval) as g(dt);
$body$
language sql;

PostgreSQL how to concat interval value '2 days'

Part of the problem is that the standard SQL expression for intervals quotes the number, but not the keywords. So you have to be careful.

select current_date, current_date + interval '2' day;
--
2012-02-21   2012-02-23 00:00:00

In PostgreSQL, quoting like '2 day' and '2 days' also works. So you might think that '2' || ' days' would be equivalent, but it's not.

select current_date, current_date + interval '2' || ' days';
--
2012-02-21   2012-02-21 00:00:02 days

The solution, as A.H. said, is to cast the result string as an interval.

You can also use a variable in place of 2. This generates a calendar for 2012.

-- 0 to 365 is 366 days; 2012 is a leap year.
select ('2012-01-01'::date + (n || ' days')::interval)::date calendar_date
from generate_series(0, 365) n;

I use that final cast to date, because date + interval returns a timestamp.

How to Find the Interval Between Two Dates in PostgreSQL

If you use a timestamp then you'll get a `interval' back:

select justify_interval(date_trunc('day', current_timestamp) - test_date)

The date_trunc() is there to set the time part of the timestamp to 00:00:00. By default that would return an interval with only days in it. The justify_interval() will then "normalize" this to months, weeks and days.

E.g. 0 years 7 mons 28 days 0 hours 0 mins 0.0 secs

Using a variable period in an interval in Postgres

Use this line:

startDate TIMESTAMP := endDate - ($3 || ' MONTH')::INTERVAL;

and note the space before MONTH.
Basically: You construct a string with like 4 MONTH and cast it with ::type into a proper interval.

Edit: I' have found another solution: You can calculate with interval like this:

startDate TIMESTAMP := endDate - $3 * INTERVAL '1 MONTH';

This looks a little bit nicer to me.

Go postgres prepared statement with interval parameter not working

You can also cast the parameter to interval and pass the complete interval string as the query argument:

expires := "7 days"

stmt := `INSERT INTO snippets (title, content, created, expires)
    VALUES($1, $2, current_timestamp, current_timestamp + $3::interval)`

_, err := m.DB.Exec(stmt, title, content, expires)

This also gives you more control about how you determine the value of expire, for example with fmt.Sprintf, or string concatenation, in case you want to have also the unit of time as an externally supplied argument.

PostgreSQL query group by two parameters

I would simply do:

select date_trunc('day', t.date) as dte, 
       sum( (value < 0)::int ) as negatives,
       sum( (value > 0)::int ) as positives
from t
where t.date >= current_date - interval '20 days' 
group by date_trunc('day', t.date),
order by dte desc;

Notes:

• I prefer using date_trunc() to casting to a string for removing the time component.
• You don't need to use now() and convert to a date. You can just use current_date.
• Converting a string to an interval seems awkward, when you can specify an interval using the interval keyword.

---

Related Topics