Writing a function to select data only on weekdays in PostgreSQL

Question

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

You should really consider upgrading. 8.3 is no longer supported. — user330315
– user330315, Commented Mar 4, 2013 at 21:00

souslov · Accepted Answer · 2015-01-25 00:19:25Z

20

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.

edited Jan 25, 2015 at 0:19

souslov

44.7k11 gold badges92 silver badges113 bronze badges

answered Mar 4, 2013 at 21:13

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

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

Comments

user330315 · Accepted Answer · 2013-03-04 21:01:32Z

9

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

answered Mar 4, 2013 at 21:01

user330315

Comments

JodyT · Accepted Answer · 2013-03-04 21:01:32Z

2

The following should return the day of the week.

date_part('dow', Date);

With the default settings 0 is Sunday and 6 is Saturday

answered Mar 4, 2013 at 21:01

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Comments

souslov · Accepted Answer · 2015-01-25 00:20:51Z

1

Check the datetime functions

e.g.

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

returns 5, which represents Friday.

edited Jan 25, 2015 at 0:20

souslov

44.7k11 gold badges92 silver badges113 bronze badges

answered Mar 4, 2013 at 21:01

Seb

3234 silver badges14 bronze badges

Comments

josliber · Accepted Answer · 2015-07-17 13:45:40Z

1

select to_char(date, 'Day') from table

edited Jul 17, 2015 at 13:45

josliber

44.4k12 gold badges103 silver badges136 bronze badges

answered Jul 17, 2015 at 11:00

Nandkishor Gokhe

5526 silver badges9 bronze badges

1 Comment

Karthik_SD Over a year ago

This is for Oracle SQL

Collectives™ on Stack Overflow

Writing a function to select data only on weekdays in PostgreSQL

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related