Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Writing a function to select data only on weekdays in PostgreSQL

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');
like image 539
user7980 Avatar asked Mar 04 '13 20:03

user7980


People also ask

How do I extract the day of the week in PostgreSQL?

In PostgreSQL you can use the extract() function to get the day from a date. You can also use date_part() to do the same thing. When extracting the day from a date, you need to specify what sense of the word “day” you mean. For example, “day of week”, “day of month”, “day of year”, etc.

How do I select just the date in PostgreSQL?

1) Get the current date However, to get the date part only (without the time part), you use the double colons (::) to cast a DATETIME value to a DATE value. The result is in the format: yyyy-mm-dd .

How do I select specific rows in PostgreSQL?

A Simple SELECT Use the following syntax to retrieve all rows and columns from a table: SELECT * FROM table_name; The asterisk ( * ) character, as mentioned in the explanation of SELECT 's syntax, is short-hand for all non-system columns.

How do I split a date and time in PostgreSQL?

This can be done in PostgreSQL using the AGE() function. This function takes in two timestamps as arguments and then returns the interval between them.


1 Answers

The following should return the day of the week.

date_part('dow', Date);

With the default settings 0 is Sunday and 6 is Saturday

like image 199
JodyT Avatar answered Nov 10 '22 12:11

JodyT