Security

I'm looking for assistance with adding a filter to the time source auth source. I'm looking for a filter to return the date of the next Monday.

If a user connects on a Monday, the time source filter would return the date of the following Monday. If the same user next connects on a Wednesday, the time source filter would still return the date of the following Monday.

Any help in generating the SQL syntax to place in the filter will be appreciated.

If anyone was interested in the solution for their own environment, here's what I received from TAC.

select date_trunc('day', localtimestamp(0) + interval '1 day' * (select 8 - extract(isodow from date_trunc('hour', localtimestamp(0))) as temp)) as next_monday

Thanks for sharing the solution.

What would I need to change if  i would like to get "Next Friday"?

select date_trunc('day', localtimestamp(0) + interval '1 day' * (select 8 - extract(isodow from date_trunc('hour', localtimestamp(0))) as temp)) as next_monday

With some help from here, I found the following SQL query that finds the start of the first coming Friday:

postgres=# select date_trunc('day', localtimestamp(0) + interval '1 day' * ( select ( 12 - extract(isodow from date_trunc('hour', localtimestamp(0)))::int ) % 7 as temp )) as next_friday;
     next_friday
---------------------
 2024-01-05 00:00:00
(1 row)

If you want 18:00h / 6PM:

postgres=# select date_trunc('day', localtimestamp(0) + interval '1 day' * ( select ( 12 - extract(isodow from date_trunc('hour', localtimestamp(0)))::int ) % 7 as temp )) + interval '18 hour'  as next_friday;
     next_friday
---------------------
 2024-01-05 18:00:00
(1 row)

This is standard Postgres SQL time calculations. Postgres is the database system that ClearPass uses under the hood.

select date_trunc('day', localtimestamp(0) + interval '1 day' * (select 8 - extract(isodow from date_trunc('hour', localtimestamp(0))) as temp)) as next_monday