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');
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.
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 .
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.
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.
The following should return the day of the week.
date_part('dow', Date);
With the default settings 0 is Sunday and 6 is Saturday
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With