CODEWITHSUNDEEP
sqlite
alexandernst
alexandernst

Reputation: 15089

Filter rows based on dynamically selecting columns by their name

I have a GTFS database. I want to query the calendar table.

That table has the following columns:

service_id | monday | tuesday | wednesday | thursday | friday | saturday | sunday | start_date | end_date

1          | 0      | 0       | 1         | 0        | 1      | 0        | 0      | 20190317   | 20200101
...
...
...

I know how to get the service_ids between the start and the end date, but I'm not sure how to filter the rows based on if they are enabled (1) or disabled (0) in the current day when the query happens.

The expected result would be all rows who's start_date <= now >= end_date and that are enabled for the current day.

Example: If I ran the query today (thursday), it won't return the row that I provided in my example. But if I run the query tomorrow (friday), it will return the row in my example.

Is this possible? (I'm using SQLite)

Upvotes: 0

Views: 29

Answers (2)

forpas
forpas

Reputation: 164099

With strftime('%w', 'now') you get a number for today's week day (0 for Sunday, 1 for Monday, ...).
So you can use a CASE statement like this:

select * from tablename
where 1 = case strftime('%w', 'now')
  when 0 then sunday 
  when 1 then monday 
  when 2 then tuesday 
  when 3 then wednesday 
  when 4 then thursday 
  when 5 then friday 
  when 6 then saturday
end

You can add additional conditions for start_date and end_date.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You need to simply add condition for days:

SELECT *
FROM t
WHERE service_id = ?
  AND ? BETWEEN start_date AND end_date
  AND (CASE 
      WHEN  strftime('%w', ?) = 1 AND monday = 1  THEN 1
      WHEN  strftime('%w', ?) = 2 AND tuesday = 1 THEN 1
      ...
      END) = 1

Upvotes: 1

Related Questions