PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I compare just the year and month of datetime in my SQL query (and ignore the day comparison)?

I have a list of ids:

select id from id where sid = 2403

The above simply returns a list of ids. e.g. id = 401434786, 401434787, 401434788, etc.

When I run one of these ids in my tval table, I get:

select * from tval where id = 401434786;

I get:

   id        fid                ts                  val
401434786   3765    2019-05-14 00:00:00.000 2019-11-18 00:00:00.000
401434786   3771    2019-11-18 00:00:00.000 2019-11-18 00:00:00.000
401434786   3782    2019-05-14 00:00:00.000 2019-11-18 00:00:00.000

My aim is to filter the list of ids to only the ones where the val of fid = 3771 and fid = 3782 are NOT the same.

I have this query which does it:

select distinct t.id from tval t 
where (select min(t1.val) from tval t1 
         where t1.id = t.id and t1.fid = 3771)
    !=(select min(t1.val) from tval t1 
         where t1.id = t.id and t1.fid = 3782)
  and id in (select id from id where sid = 2403);

However, I want to amend the query, or write a completely new one, to filter the list of ids to those where only the Month or the Year are different, but if the Month and Year and the same but the Day is different, then leave this out.

In the query above, it filters for ids where if the Month and Year are the same but the Day is different it still includes it in the output, but I do not want this. I only want ids where the Month or the Year are different.

I hope the above makes sense, and I really appreciate any help!

Tags:

2

Answers


  1. 0

    On a date value, use extract(month from sometimestamp) to extract the month, extract(year from sometimestamp) to extract the year, and extract(day from sometimestamp) for day of month.

    Almost equivalently,you can use date_part(‘month’, sometimestamp), etc.

    Login or Signup to reply.
  2. 0

    Use date_trunc(). Once you truncate the two dates/timestamps down to a month, they become equivalent as long as their year and month match:

    select distinct t.id from tval t 
where (select min(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3771)
    !=(select min(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3782)
  and id in (select id from id where sid = 2403);

    That comparison effectively ignores not only the day, but anything below the precision of a month, so days, hours, minutes, seconds and fractions as well.

    If that min() was some sort of compromise while you actually want to avoid ids having any pair of fid 3771 and 3782 on the same month (not just on their earliest occurence for a given id), you could instead use array overlap &&:

    select distinct t.id from tval t 
where not 
      (select array_agg(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3771)
    &&(select array_agg(date_trunc('month',t1.val)) from tval t1 
         where t1.id = t.id and t1.fid = 3782)
  and id in (select id from id where sid = 2403);

    Which could probably be faster as a not exists:

    select distinct t.id from tval t 
where not exists
      (select from tval t1 where t1.id = t.id and t1.fid = 3771
       where date_trunc('month',t1.val) in 
          ( select date_trunc('month',t1.val) from tval t1 
            where t1.id = t.id and t1.fid = 3782) )
  and id in (select id from id where sid = 2403);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search