Fetching records that having their monthly period of creation date

Question

I want to grab all the records that have been created ℕ month ago, exactly the same hour. (age of the records should be only ℕ months, 0 days, 0 hour)

ℕ = {1, 2, …}

for example if now() is 2016-11-15 13:15:00, any record having create_date as below is expected to be returned.

2010-11-15 13:15:00  -- different year
2016-02-15 13:15:00  -- different month
2016-11-15 13:46:23  -- different minute/seconds
2010-11-15 13:46:23  -- different year, month, minute and sec

The best simple query I could think of was this:

SELECT  *
FROM    mytable
WHERE extract(day from age(now(), created_date)) = 0 -- same day any month ago
  AND extract(hour from age(now(), created_date)) = 0; -- same hour

But the problem is there are some specific days that will be left out of the query. Such as when

created_date=2016-01-31 13:00:00
now() is 2016-02-28 13:00:00

in this case, the age of the record is 28 days instead of 1 month; according to the age function in Postgresql. So it will never be fetched neither at the end of Feb, or 1st of March.

Any idea about how this problem can be addressed in the most simple way is appreciated.

---

Here's some test data you can use to verify:

create table mytable (created_date timestamp, expected_records int);
truncate mytable;
insert into mytable (created_date, expected_records)  values 
('2013-01-11 13:22:33', 2),
('2014-02-11 13:58:22', 2),
('2015-03-21 13:22:00', 1),
('2013-02-28 13:11:00', 5),
('2017-02-28 13:22:00', 5),
('2016-03-31 13:22:00', 5),
('2015-04-30 13:22:00', 5),
('2014-05-31 13:22:00', 5),
('2014-05-31 12:22:00', 1);

the final SELECT query can be executed on any of these timstamps but should returns same amount of rows as expected_records states. Also you can use 'some date'::timestamp instead of now() to fake time.

Answer 1

First, let's try to understand your requirements better. You'll need all the records, for which the timestamp column (created_date) & the current time (now()) is in the following relation:

• has the exact same hour
  AND
  • has the exact same day
    OR
  • created_date has a day, which is greater than now()'s day
    BUT only if now() is in the last day of the current month.

---

SELECT *
FROM   mytable
WHERE  EXTRACT(hour FROM created_date) = EXTRACT(hour FROM now())
AND    (EXTRACT(day FROM created_date) = EXTRACT(day  FROM now())
OR      CASE
          WHEN EXTRACT(month FROM CURRENT_DATE + 1) <> EXTRACT(month FROM CURRENT_DATE)
          THEN EXTRACT(day FROM created_date) > EXTRACT(day FROM now())
          ELSE FALSE
        END)

http://rextester.com/TXGCSI34243

Note: if you want to bind timestamps for your solution (not actually using the now() & current_date functions) then use WHEN EXTRACT(month FROM ? + INTERVAL '1 day') > EXTRACT(month FROM ?) to test whether the bound timestamp is the last day of its month, or not.

Answer 2

Please try the following code segment, which has been tested and confirmed as effective...

SELECT  *
FROM mytable
WHERE EXTRACT( HOUR FROM NOW() ) = EXTRACT( HOUR FROM created_date )
  AND ( ( EXTRACT( DAY FROM NOW() ) = EXTRACT( DAY FROM created_date ) ) OR

        ( EXTRACT( DAY FROM DATE_TRUNC( 'MONTH', NOW() ) + INTERVAL '1 MONTH - 1 DAY' ) = EXTRACT( DAY FROM NOW() ) AND
          EXTRACT( DAY FROM created_date ) >= EXTRACT( DAY FROM NOW() ) ) );

The following code tests to see both dates have the same days figure, irrespective of if one of them is the end of its month. In that situation no further testing of the dates is required...

EXTRACT( DAY FROM NOW() ) = EXTRACT( DAY FROM created_date )

The following code tests if NOW() is at the end of the month...

EXTRACT( DAY FROM DATE_TRUNC( 'MONTH', NOW() ) + INTERVAL '1 MONTH - 1 DAY' ) = EXTRACT( DAY FROM NOW() )

The following code tests to see if created_date has a days value that is greater than NOW()'s...

EXTRACT( DAY FROM created_date ) >= EXTRACT( DAY FROM NOW() )

Thus if the end of NOW() has no greater a value than the day value of created_date then they can be considered equivalent (hence the following code) e.g. NOW()'s date = 2017-02-28 versus created_date's 2017-01-31, or NOW()'s date = 2017-04-30 versus created_date's 2017-12-31, etc.

EXTRACT( DAY FROM DATE_TRUNC( 'MONTH', NOW() ) + INTERVAL '1 MONTH - 1 DAY' ) = EXTRACT( DAY FROM NOW() ) AND
EXTRACT( DAY FROM created_date ) >= EXTRACT( DAY FROM NOW() )

If you have any questions or comments, then please post a Comment accordingly.

Answer 3

You can workaround by getting the first month day - (1 day):

sample:

t=# create table so42(t timestamp);
CREATE TABLE
t=# insert into so42 select '2016-05-31 13:00:00';
INSERT 0 1
t=# insert into so42 select '2016-10-31 13:00:00';
INSERT 0 1
t=# insert into so42 select '2016-02-29 13:00:00';
INSERT 0 1
t=# insert into so42 select '2016-01-21 13:00:00';
INSERT 0 1

join example:

t=# with ts as (
        select
                generate_series('2016-01-01 13:00:00'::timestamp,'2016-12-01'::date,'1 month'::interval)
                - '1 day'::interval
        lastday
)
select t
from so42
join ts on ts.lastday = t
;
          t
---------------------
 2016-02-29 13:00:00
 2016-05-31 13:00:00
 2016-10-31 13:00:00
(3 rows)

also you can wrap it up into function with argument, smth like:

t=# create or replace function so45(_ts timestamp) returns table (lastday timestamp) as
$$
declare
 _start timestamp;
 _stop timestamp;
begin
 select date_trunc('year', _ts) + (_ts)::time +concat(extract(day from _ts),' days')::interval into _start;
 select date_trunc('year', _ts) + (_ts)::time +concat(extract(day from _ts),' days')::interval + '12 month'::interval into _stop;
return query with ts as (
        select
                generate_series(_start,_stop,'1 month'::interval)
                - '1 day'::interval
        lastday
)
select * from ts order by 1;
end;
$$ language plpgsql
;
CREATE FUNCTION

join example:

t=# with ts as (select so45('2016-03-31 13:00:00'))
select t
from so42
join ts on ts.so45 = t
;
          t
---------------------
 2016-02-29 13:00:00
 2016-05-31 13:00:00
 2016-10-31 13:00:00
(3 rows)

t=# with ts as (select so45('2016-03-21 13:00:00'))
select t
from so42
join ts on ts.so45 = t
;
          t
---------------------
 2016-01-21 13:00:00
(1 row)