Thread: Calculate next event date based on instance of the day of week
I'm sure the subject sounds extremely confusing :)
I have a table which stores an event date. The event is stored as a standard mm/dd/yy entry.
I then need to calculate based on the dd value, WHICH day of the week that is (e.g. Wednesday) and which occurrence of that day of the week, within the month, it is (e.g. the THIRD Wednesday).
From there, I need to calculate, based on the previously obtained information, the mm/dd/yy DATE of next month's third Wednesday.
My business analyst has attempted a few various solutions but we seem to keep missing the mark, we've found tons of information for how to do it in a MySQL or MSSQL instance, but none on Postgres, or at least, none that have worked.
Any help would be appreciated, thanks!
Hello, > I have a table which stores an event date. The event is stored as a > standard mm/dd/yy entry. > > I then need to calculate based on the dd value, WHICH day of the > week that is (e.g. Wednesday) and which occurrence of that day of > the week, within the month, it is (e.g. the THIRD Wednesday). > Here is an example to reach this far .... postgres=# create table mt(myd date); postgres=# insert into mt select current_date+se from (select generate_series(1,10000) as se ) as x; postgres=# select * from mt order by postgres-# myd limit 10; myd ------------2010-12-042010-12-052010-12-062010-12-072010-12-082010-12-092010-12-102010-12-112010-12-122010-12-13 (10 rows) This is the query to get the data in the format you want... select myd, d ,w from ( select myd, to_char(myd,'Day') as d , to_char(myd,'W') as w ,rank() over (partition by to_char(myd,'W') order by myd ) as x from mt order by myd ) as t order by myd ; myd | d | w ------------+-----------+---2010-12-04 | Saturday | 12010-12-05 | Sunday | 12010-12-06 | Monday | 12010-12-07 | Tuesday | 12010-12-08 | Wednesday | 22010-12-09 | Thursday | 22010-12-10 | Friday | 22010-12-11 | Saturday | 22010-12-12| Sunday | 22010-12-13 | Monday | 22010-12-14 | Tuesday | 22010-12-15 | Wednesday | 32010-12-16 | Thursday | 32010-12-17 | Friday | 32010-12-18 | Saturday | 32010-12-19 | Sunday | 32010-12-20 | Monday | 32010-12-21| Tuesday | 32010-12-22 | Wednesday | 42010-12-23 | Thursday | 42010-12-24 | Friday | 42010-12-25 | Saturday | 42010-12-26 | Sunday | 42010-12-27 | Monday | 42010-12-28 | Tuesday | 42010-12-29 | Wednesday | 52010-12-30| Thursday | 52010-12-31 | Friday | 5 Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."