Forum OpenACS Q&A: how to find out the exact date of i.e. "1st monday of this month"

I want to find out what exact date i.e. the 1st monday of the current month is. Any hints?


in Oracle:

select next_day(last_day(add_months(sysdate,-1)), 'MONDAY') from dual

I'm too lazy to try the commands myself but it's something like...

set datenow [clock scan [date]]
set first_day_of_month "[clock scan [clock format $datenow -format %Y]-[clock format $datenow -format %m]-01]"
set day_of_year [clock format $first_day_of_month -format %j]
set day_of_week_of_first_day [clock format $first_day_of_month -format %w]

So you'll now have the day of year of the first day of the current month as well as it's day of week. Sunday is 0.

With those two values, a couple or so [expr]'s should get you the first monday.

Also possible using a similar approach in sql.

Oh.  Now that Samir posted an oracle function, I do recall ports of those functions in PG in the acs-event packages.  So Samir's should work in PG too as long as the oracle-compat-create.sql in acs-event has been loaded.
Wow Samer... this query even works with Postgresql 7.2.3 :-)


What if I wanted to use a 1 istead of Monday?

It only works because of the ports of the oracle functions.  See my previous post on where those functions were defined.

The next_day function port for PG converts the weekday to int. So if you'd want to use 1, then I guess rewrite the function and support both a weekday and an int.

Eh, I hope we remove date functions that hit the database step by step. AOLserver and/or TCL have date functions as well and using them means:

* hitting the database less frequently
* using less db handles
* less portability woes

If you are in a clustered environment you need to think about syncing times between the different machines. Oh, and whenever you stick data into the database then get the date+time from the database machine.

Mista Gomez you are right... one shouldn't mess with the db for time issues :-)

I found out about a convenient way to calculate i.e. the 4 Monday of this or next month:

set datenow                            [clock scan now]
set first_day_of_month                  [clock scan [clock format $datenow -format %Y]-[clock format $datenow -format %m]-01]
set first_day_of_month_fmt              [clock format $first_day_of_month -format "%Y-%m-%d"]
set first_day_of_next_month            [clock scan "1 month" -base [clock scan $first_day_of_month_fmt]]
set first_day_of_next_month_fmt        [clock format $first_day_of_next_month -format "%Y-%m-%d"]

set n_day_of_n_week_of_month            [clock scan "Monday 3 Week" -base [clock scan "$first_day_of_month_fmt"]]
set n_day_of_n_week_of_month_fmt        [clock format $n_day_of_n_week_of_month -format "%Y-%m-%d"]
set n_day_of_n_week_of_next_month      [clock scan "Monday 3 Week" -base [clock scan "$first_day_of_next_month_fmt"]]
set n_day_of_n_week_of_next_month_fmt  [clock format $n_day_of_n_week_of_next_month -format "%Y-%m-%d"]

Thanks for posting this. I'll see where in acs-events, calendar, datetime "select from sysdate" is being used and will deprecate and/or supersede the respective procs.
One site that I found particularly valuable was:

You can also calculate how many weeks, days, hours, minutes, seconds an event is away...