SQL select subquery IN from table column values

Question

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!

If you're unfamiliar with relational databases, the days_2 column should be normalised to contain a single value - so your first row would become five rows. Once you know what you're doing, you can use an SQL99 ARRAY data type for it and use the UNNEST function in your query. — cliffordheath
– cliffordheath, Commented Dec 14, 2015 at 20:58

Clodoaldo Neto · Accepted Answer · 2015-12-14 21:56:16Z

1

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

answered Dec 14, 2015 at 21:56

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tale852150 · Accepted Answer · 2015-12-16 15:07:57Z

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

Collectives™ on Stack Overflow

SQL select subquery IN from table column values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related