#daterange

用 PostgreSQL 的 int4range 與 GiST

發現自己根本還不熟悉 PostgreSQL 的特性，寫一下記錄起來。 產品上常常會有 coupon 與 voucher 之類的設計，這時候通常都會設定 coupon 或 voucher 的有效期間，在 MySQL 的環境下可能會這樣設計： CREATE TABLE coupon ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, code VARCHAR(255) NOT NULL, started_at INT UNSIGNED NOT NULL, ended_at INT UNSIGNED NOT NULL ); 另外是設計 index 的部份，在產品推出夠久後，通常是過期的 coupon 或 voucher 會比目前還有效的多，而還沒生效的 coupon 與 voucher 通常都不多，所以會設計成對 ended_at 放一組…

This query finds the approximate primary key Min and Max values that corresponds to the date range specified. It's handy for dashboard statistics generataion and support queries where you need to see the a specific range of rows, but the 'timestamp' column does not have an index.

-- Binomial Search For Min and Max PK -- For specifc data range: -- ## Controlling Variables DECLARE @Today DATETIME = Cast(Getdate() AS DATE) DECLARE @NameOfPrimaryKeyField NVARCHAR(200) = 'pkElementDataID' DECLARE @NameOfDateTimeField NVARCHAR(200) = 'Created' DECLARE @NameOfTable NVARCHAR(200) = 'SafeTriageData.dbo.ElementData' DECLARE @DateTimeFrom DATETIME = Dateadd(day, -7, @Today) DECLARE @DateTimeBefore DATETIME = @Today -- ## Main Variable Setup DECLARE @MaxId             INT = 0,         @MinId             INT = 0,         @CandidateId       INT = 0,         @CandidateDateTime DATETIME,         @LowerId           INT = -1,         @UpperId           INT = -1 DECLARE @Parameters NVARCHAR(100) = N'@CandidateId INT'   + ', @CandidateDateTime DATETIME OUTPUT' DECLARE @MaxSql AS NVARCHAR(2000) = 'SELECT @MaxId = MAX('   + @NameOfPrimaryKeyField + ') FROM '   + @NameOfTable DECLARE @MinSql AS NVARCHAR(2000) = 'SELECT @MinId = MIN('   + @NameOfPrimaryKeyField + ') FROM '   + @NameOfTable DECLARE @DateTimeSql AS NVARCHAR(2000) = 'SELECT @CandidateDateTime = '   + @NameOfDateTimeField + ' FROM ' + @NameOfTable   + ' WHERE ' + @NameOfPrimaryKeyField   + ' = @CandidateId' -- set the min and max values EXEC Sp_executesql   @MinSql,   N'@MinId INT OUTPUT',   @MinId output EXEC Sp_executesql   @MaxSql,   N'@MaxId INT OUTPUT',   @MaxId output SET @CandidateId = @MinId EXEC Sp_executesql   @DateTimeSql,   @Parameters,   @CandidateId = @CandidateId,   @CandidateDateTime = @CandidateDateTime output -- now perform the algorithms DECLARE @L INT,         @R INT,         @m INT,         @T DATETIME -- ## FIND THE LOWER BOUND SELECT @L = @MinId,        @R = @MaxId,        @T = @DateTimeFrom WHILE @L < @R   BEGIN       SET @m = Floor(( @L + @R ) / 2)       EXEC Sp_executesql         @DateTimeSql,         @Parameters,         @CandidateId = @m,         @CandidateDateTime = @CandidateDateTime output       IF( @CandidateDateTime < @T )         SET @L = @m + 1       ELSE         SET @R = @m   END SET @LowerId = @m -- ## FIND THE UPPER BOUND SELECT @L = @MinId,        @R = @MaxId,        @T = @DateTimeBefore WHILE @L < @R   BEGIN       SET @m = Floor(( @L + @R ) / 2)       EXEC Sp_executesql         @DateTimeSql,         @Parameters,         @CandidateId = @m,         @CandidateDateTime = @CandidateDateTime output       IF( @CandidateDateTime <= @T )         SET @L = @m + 1       ELSE         SET @R = @m   END SET @UpperId = @m -- THE VALUES ARE IN @LowerId and @UpperId SELECT @LowerId AS '@LowerId',        @UpperId AS '@UpperId' 

This produces a continuous range of valid dates with no gaps. It's handy for reporting writing

Thanks to Guillaume Gautreau for nice implementation which I was looking for :) 

https://github.com/ghusse/jQRangeSlider

Take a look to the Demo Page : 

http://ghusse.github.com/jQRangeSlider/stable/demo/