CODEWITHSUNDEEP
sqlpostgresql
user3393089
user3393089

Reputation: 169

postgres query to check for records not falling between two time periods using the ISO WEEK number and year from DB

i do have a query which works fine but I was just wondering if there are other ways or alternate method to bettter this.

I have a table where i am fetching those records exceeding or do not fall between 1 year time interval however there is only the year and ISO week number column in the table (integer values).

basically the logic is to check ISO WEEK - YEAR falls between 'current_date - interval '1 year' AND current_date.

My query is as below :

 select *  from raj_weekly_records where 
    (date_dimension_week > extract(week from current_date) and date_dimension_year = extract(year from current_date) )
    or (date_dimension_week <  extract(week from current_date) and (extract(year from current_date)-date_dimension_year=1) )
    or(extract(year from current_date)-date_dimension_year>1);

Here date_dimension_week and date_dimension_year are the only integer parameters by which I need to check is there any other alternate or better way?.This code is working fine no issues here.

Upvotes: 0

Views: 99

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select *
from raj_weekly_records
where
    date_trunc('week',
        '0001-01-01 BC'::date + date_dimension_year * interval '1 year'
    )
    + (date_dimension_week + 1) * interval '1 week'
    - interval '1 day'
    not between
    current_date - interval '1 year' and current_date

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Here is an idea. Convert the year/week to a numeric format: YYYYWW. That is, the year times 100 plus the week number. Then you can do the logic with a single comparison:

select * 
from raj_weekly_records
where date_dimension_year * 100 + date_dimension_week
          not between (extract(year from current_date) - 1) * 100 + extract(week from current_date) and
                      extract(year from current_date) * 100 + extract(week from current_date)

(There might be an off-by one error, depending on whether the weeks at the ends are included or excluded.)

Upvotes: 1

Related Questions