If those columns are timestamp
(or date
) columns, just use
age(MySecondDateField, MyFirstDateField)
The timestamp
or date
prefix is only needed for date/timestamp constants.
If those columns are varchar then you should change them to a proper timestamp/date datatype.
Until you do that, you can use the to_date()
or to_timestamp()
function to convert a string to a date, e.g:
to_timestamp(MySecondDateField, 'yyyy-mm-dd hh24:mi:ss')
For more details on those functions, please see the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html
You should never store dates (or numbers) as varchar/text columns. To fix your table, you can use a conversion function when you run the alter table:
alter table wrong_design
alter MyFirstDateField type timestamp
using to_timestamp(some_col, 'yyyy-mm-dd hh24:mi:ss.us')
You can do much with window functions. Presenting two solutions: one with and one without materialized view.
Test case
Building on this table:
CREATE TABLE hotel_data (
hotel_id int
, day date -- using "day", not "date"
, spaces int
, price int
, PRIMARY KEY (hotel_id, day) -- provides essential index automatically
);
Days per hotel_id
must be unique (enforced by PK here), or the rest is invalid.
Multicolumn index for base table:
CREATE INDEX mv_hotel_mult_idx ON mv_hotel (day, hotel_id);
Note the reversed order as compared to the PK. You will probably need both indexes, for the following query, the 2nd index is essential. Detailed explanation:
Direct query without MATERIALIZED VIEW
SELECT hotel_id, day, sum_price
FROM (
SELECT hotel_id, day, price, spaces
, sum(price) OVER w * 2 AS sum_price
, min(spaces) OVER w AS min_spaces
, last_value(day) OVER w - day AS day_diff
, count(*) OVER w AS day_ct
FROM hotel_data
WHERE day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
AND spaces >= 2
WINDOW w AS (PARTITION BY hotel_id ORDER BY day
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to nights - 1
) sub
WHERE day_ct = 4
AND day_diff = 3 -- make sure there is not gap
AND min_spaces >= 2
ORDER BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;
Also see @ypercube's variant with lag()
, which can replace day_ct
and day_diff
with a single check.
How?
In the subquery, only consider days within your time frame ("in January" means, the last day is included in the time frame).
The frame for the window functions spans the current row plus the next num_nights - 1
(4 - 1 = 3
) rows (days). Calculate the difference in days , the count of rows and the minimum of spaces to make sure the range is long enough, gapless and always has enough spaces.
- Unfortunately, the frame clause of window functions does not accept dynamic values, so
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING`
cannot be parameterized for a prepared statement.
I carefully drafted all window functions in the subquery to reuse the same window, using a single sort step.
The resulting price sum_price
is already multiplied by the number of spaces requested.
To avoid inspecting many rows without chance of success, save only the columns you need plus three redundant, calculated values from the base table. Be sure the MV is up to date. If you are not familiar with the concept, read the manual first.
CREATE MATERIALIZED VIEW mv_hotel AS
SELECT hotel_id, day
, first_value(day) OVER (w ORDER BY day) AS range_start
, price, spaces
,(count(*) OVER w)::int2 AS range_len
,(max(spaces) OVER w)::int2 AS max_spaces
FROM (
SELECT *
, day - row_number() OVER (PARTITION BY hotel_id ORDER BY day)::int AS grp
FROM hotel_data
) sub1
WINDOW w AS (PARTITION BY hotel_id, grp);
range_start
stores the first day of each continuous range for two purposes:
- to mark a set of rows as members of a common range
- to show the start of the range for possible other purposes.
range_len
is the number of days in the gapless range.
max_spaces
is the maximum of open spaces in the range.
- Both columns are used to exclude impossible rows from the query immediately.
I cast both to smallint
( max. 32768 should be plenty for both) to optimize storage: only 52 bytes per row (incl. heap tuple header and item identifier). Details:
Multicolumn index for MV:
CREATE INDEX mv_hotel_mult_idx ON mv_hotel (range_len, max_spaces, day);
Query based on MV
SELECT hotel_id, day, sum_price
FROM (
SELECT hotel_id, day, price, spaces
, sum(price) OVER w * 2 AS sum_price
, min(spaces) OVER w AS min_spaces
, count(*) OVER w AS day_ct
FROM mv_hotel
WHERE day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
AND range_len >= 4 -- exclude impossible rows
AND max_spaces >= 2 -- exclude impossible rows
WINDOW w AS (PARTITION BY hotel_id, range_start ORDER BY day
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to $nights - 1
) sub
WHERE day_ct = 4
AND min_spaces >= 2
ORDER BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;
This is faster than the query on the table because more rows can be eliminated immediately. Again, the index is essential. Since partitions are gapless here, checking day_ct
is enough.
SQL Fiddle demonstrating both.
Repeated use
If you use it a lot, I would create an SQL function and only pass parameters. Or a PL/pgSQL function with dynamic SQL and EXECUTE
to allow adapting the frame clause.
Alternative
Range types with date_range
to store continuous ranges in a single row might be an alternative - complicated in your case with potential variations on prices or spaces per day.
Related:
Best Answer
Disjoint ranges.
What essentially you're asking to do is exclude a subset of a range. That creates a "discontinuous ranges" or "disjoint ranges" which can not be contained . It's better to see this with an
int
,([1,10] - [3,7]) = ([1,2] + [8,10])
These two forms are the same, they're just different ways of writing it. This is simply not supported by the
daterange
type from the docs,That's not to say you can't accomplish the same thing just explode the ranges and subtract out the values you don't want.
Note always do date-math including date-range generate with
timestamp without time zone
or you may encounter DST bugs