How can I compare just the year and month of datetime in my SQL

ghz 8months ago ⋅ 76 views

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!

Answers

To filter the list of IDs based on whether the Month or the Year are different, while excluding cases where only the Day is different, you can modify your query to compare the month and year parts of the val column. You can achieve this by using date functions to extract the month and year components of the dates and then compare them. Here's how you can do it:

SELECT DISTINCT t.id
FROM tval t 
JOIN id i ON t.id = i.id
WHERE i.sid = 2403
AND (
    -- Check if the month or year of fid = 3771 is different from fid = 3782
    EXTRACT(YEAR FROM t1.val) != EXTRACT(YEAR FROM t2.val)
    OR EXTRACT(MONTH FROM t1.val) != EXTRACT(MONTH FROM t2.val)
)
AND t.fid = 3771 -- Filter for fid = 3771
AND EXISTS (
    -- Subquery to check if fid = 3782 exists for the same id
    SELECT 1
    FROM tval t2
    WHERE t2.id = t.id
    AND t2.fid = 3782
);

In this query:

  • We use EXTRACT(YEAR FROM t1.val) and EXTRACT(MONTH FROM t1.val) to extract the year and month parts of the val column for fid = 3771.
  • We compare the year and month parts of fid = 3771 with fid = 3782 to check if they are different.
  • We use EXISTS to check if fid = 3782 exists for the same id as fid = 3771.
  • We join with the id table to filter for ids with sid = 2403.

This query will only return IDs where the month or year of fid = 3771 is different from fid = 3782, excluding cases where only the day is different. Adjust the column names and table aliases as per your actual schema.

#sql