I am working on a Postgres 8.3 database. A query I use is meant to only select rows that are included in weekdays. Right now I have do do this by hand like in the example below but I want to transfer this to some funciotn where I can specefic the start and end dates and get the same logic to apply as below. That is
How can you create a function whose input is a start and end date and the result of the function will be to select all rows only included in the weekdays of the data sets (I want to exlude every staurday and sunday as in the where clause condition below)?
create table filter_tbl as
select *
from base_tbl where
(start_Time >= '2012-11-5' and start_Time < '2012-11-10')
or (start_time >= '2012-11-12' and start_time < '2012-11-17')
or (start_time >= '2012-11-19' and start_time < '2012-11-24')
or (start_time >= '2012-11-26' and start_time < '2012-12-01')
or (start_time >= '2012-12-03' and start_time < '2012-12-07')
or (start_time >= '2012-12-10' and start_time < '2012-12-14')
or (start_time >= '2012-12-17' and start_time < '2012-12-21')
or (start_time >= '2012-12-24' and start_time < '2012-12-28')
or (start_time >= '2012-12-31' and start_time < '2013-01-04')
or (start_time >= '2013-01-07' and start_time < '2013-01-11')
or (start_time >= '2013-01-14' and start_time < '2013-01-18')
or (start_time >= '2013-01-21' and start_time < '2013-01-25')
or (start_time >= '2013-01-28' and start_time < '2013-02-02')
or (start_time >= '2013-02-04' and start_time < '2013-02-09')
or (start_time >= '2013-02-11' and start_time < '2013-02-16')
or (start_time >= '2013-02-18' and start_time < '2013-02-23')
or (start_time >= '2013-02-25' and start_time < '2013-03-02')
or (start_time >= '2013-03-04' and start_time < '2013-03-09')
or (start_time >= '2013-03-11' and start_time < '2013-03-16');
Based on your example it seems that start_time is text. Then you need to convert it to timestamp using to_timestamp and then extract day of the week using EXTRACT.
Your WHERE clause will be like this:
WHERE EXTRACT(dow FROM timestamp (to_timestamp(start_time, "YYYY-MM-DD"))
NOT IN (0,6)
Links: Data Type Formatting Functions and Date/Time Functions and Operators.