Writing a function to select data only on weekdays in PostgreSQL

Question

I am working on a Postgres 8.3 database. The query I use is intended only to select rows included on weekdays. Right now I am doing this manually, as in the example below, but I want to pass this to some functions where I can describe the start and end dates and get the same logic as shown below. it

How can you create a function whose input is the start and end date, and the result of the function will be the selection of all rows included only on the weekdays of the data sets (I want to exclude every Sunday and Sunday periods, as in where the condition condition is lower)?

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'); 

Answer 1

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 the day of the week using EXTRACT .

Your WHERE will look like this:

 WHERE EXTRACT(dow FROM timestamp (to_timestamp(start_time, "YYYY-MM-DD")) NOT IN (0,6) 

References: Data type formatting functions and Date and time functions and operators .

Answer 2

 select * from base_tbl where extract(dow from start_time) in (1,2,3,4,5) 

Answer 3

Check datetime functions

eg.

 SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); 

returns 5 , which represents Friday .

Answer 4

The following should return the day of the week.

 date_part('dow', Date); 

With default settings 0 - Sunday and 6 - Saturday

Answer 5

 select to_char(date, 'Day') from table