CODEWITHSUNDEEP
sqlpostgresql
sys49152
sys49152

Reputation: 137

SQL select subquery IN from table column values

First, apologize for maybe not being able to express the title of the question properly, I am not very much into SQL queries.

I have a table "parkings" with columns like this

| days_2        | from_hr   | to_hr
"1,2,3,4,5"     "08:00:00"  "18:00:00"
"0,1,2,3,4,5,6" "08:00:00"  "22:00:00"
...
...

I made a next query which is working OK:

SELECT Extract(dow from current_timestamp::DATE) as Day, 
    current_time BETWEEN from_hr and to_hr as ParkHour, 
    days_2, 
    from_hr, 
    to_hr  
FROM public.parkings;

If today is Monday and current time is 21:26:00 results is: 

day | ParkHour  | days_2        | from_hr   | to_hr
1    f          "1,2,3,4,5"     "08:00:00"  "18:00:00"
1    t          "0,1,2,3,4,5,6" "08:00:00"  "22:00:00"
...
...

I would like to modify this query in a way that in the first column will be stored result (true/false) if current day number is in table column days_2 for that record , in a way like this

SELECT Extract(dow from current_timestamp::DATE)  in (1,2,3,4,5,6);

where if it's now sunday (0) for example the final results will be false for first row but true in the second row:

day | ParkHour  | days_2        | from_hr   | to_hr
 f   f          "1,2,3,4,5"     "08:00:00"  "18:00:00"
 t   t          "0,1,2,3,4,5,6"  "08:00:00" "22:00:00"

How can I accomplish this ?

Thanks!

Upvotes: 0

Views: 86

Answers (2)

tale852150
tale852150

Reputation: 1638

Try 

select 
  case
    when Extract(dow from current_timestamp::DATE) = '0' then 't' else 'f'
    when Extract(dow from current_timestamp::DATE) = '1' then 't' else 'f'
    when Extract(dow from current_timestamp::DATE) = '2' then 't' else 'f'
    when Extract(dow from current_timestamp::DATE) = '3' then 't' else 'f'
    when Extract(dow from current_timestamp::DATE) = '4' then 't' else 'f'
    when Extract(dow from current_timestamp::DATE) = '5' then 't' else 'f'
    when Extract(dow from current_timestamp::DATE) = '6' then 't' else 'f'
  end as day,
  current_time BETWEEN from_hr and to_hr as ParkHour, 
  days_2, 
  from_hr, 
  to_hr  
from public.parkings;

As others have mentioned, it would easier if your table structure was better and / or if you used an array.

HTH

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125524

select
    extract(dow from now())
    =
    any (regexp_split_to_array('1,2,3', ',')::integer[]);
 ?column? 
----------
 t

Turning that column type to array would avoid the string spliting step.

http://www.postgresql.org/docs/current/static/arrays.html

Upvotes: 1

Related Questions